postgres sql , how to increment when null exists

Question

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?

Answer 1

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.