Postgresql: ON CONFLICT UPDATE only if new value has been provided

Question

I have this table:

CREATE TABLE myTable (
  a VARCHAR(32),
  b VARCHAR(32) DEFAULT NULL,
  c VARCHAR(32) DEFAULT NULL,

  PRIMARY KEY (a)
);

and this "UPSERT" type query:

INSERT INTO 
  myTable ( a,  b,  c)
  VALUES  ($1, $2, $3)
ON CONFLICT (a)
  DO UPDATE SET
    b = $2,
    c = $3,
RETURNING
  a, b, c
;

This works as expected. If I first insert (json for notational convenience):

{a:"a", b:"b", c:"c"}

then I can update it with params like this:

{a:"a", b:"x", c:"y"}

And I get the expected result-- the {a:"a"} record has updated b and c columns.

But I would like to also be able to do this:

{a:"a", c:"Q"}

and update the c column while leaving column b intact.

Of course, I need some kind of expression on the right hand side, but I don't know what that is. My intuition is something like this (SQL pseudocode):

...
  DO UPDATE SET
    b = ($2 | b)
    c = ($3 | c)
...

What's the right syntax here? Or, is there a completely different method I should be using?

Answer 1

You can use COALESCE(). The idea is to pass a NULL value to one of the parameters, and then you can do:

INSERT INTO myTable (a,  b,  c)
VALUES  ($1, $2, $3) 
DO UPDATE SET
    b = COALESCE(EXCLUDED.b, b)
    c = COALESCE(EXCLUDED.c, c)

When a record already exists with a = $1 and $1 or $2 are given a null value and , then only the non-null value is written.

The downside is that this query will not be able to assign a null value to an already-existing record.

Side note: this uses pseudo-table EXCLUDED rather than repeating the parameters.

Answer 2

You can use COALESCE() and not forget to use allias for th table myTable

INSERT INTO myTable as t (a,  b,  c)
VALUES  ($1, $2, $3) 
DO UPDATE SET
    b = COALESCE(EXCLUDED.b, t.b)
    c = COALESCE(EXCLUDED.c, t.c)