In PostgreSQL, I would like to increment a field but it is null sometimes.
How do I increment that field in 1 if it is not null and set it to 1 if it is null?
insert into table (id, inc) values('a',1) on conflict(id) do update set inc=inc;
When id already exists and inc is null it won't work.
Any suggestions?
You can accomplish that with the help of the coalesce function:
INSERT INTO mytable (id,inc) VALUES ('a',1)
ON CONFLICT (id)
DO UPDATE SET inc = coalesce(mytable.inc,0) + 1;
The key point is the expression coalesce(mytable.inc,0), which means the following: if mytable.inc is not NULL then use that value, otherwise use the value 0.
