I have a PostgreSQL table of 7k records. each record has 3 a unique ID and 3 fields with it. childcares, shcools, hospitals. there are all integer fields. i want to add a new column and calculate the total amount of receptors (schools,childcares,hospitals) for each row. I thought this should be pretty straighforward with adding a column and doing an insert with a select but i am not getting the results i want
alter table site add total integer;
insert into site(total) select sum(schools+childcares+hospitals) as s from site;
i have also tried a group by id in the insert select statement
You are looking for Update not Insert
Update site
set total = COALESCE(schools,0)+COALESCE(childcares,0)+COALESCE(hospitals,0)
Added COALESCE to handle NULL values.
Ex :
1 + 2 + NULL = NULL so to replace NULL with 0 I have used COALESCE.
Now it will be 1 + 2 + 0(NULL) = 3
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments