I am working on a small invoicing solution and I Need to add a column to store the price of one unit. I already have a column for total for all the units and the quantity in the db.
My question is, how can I add this column and populate it with accurate numbers? I know that formula will be:
total_col / quantity_col = unit_col
Here is an example of populating a new column with some derived value:
create table products (
total_col int,
quantity_col int);
ALTER TABLE products ADD COLUMN unit_col numeric(10,2) default null;
update products set unit_col=total_col::float / quantity_col;
You'll want to set up a trigger to keep this column up to date. This is what is know as a persistent, computed column.
Another, perhaps better, solution is to set up a view that has the computed column you want:
create table products (
total_col int,
quantity_col int);
create view productsWithUnitCol as
select *, total_col::float / quantity_col as unit_col from products;
Assuming your database is called mydb, the table is called invoices, and the column is unit_col I would do the following:
Connect to your postgresql database via command line, typically psql mydb and then the following:
ALTER TABLE invoices
ADD COLUMN unit_col real;
UPDATE invoices SET unit_col = total_col/quantity_col;
