1

Is it possible to write an SQL UPDATE that only changes columns if data is actually provided?

Let's say I'm updating a post. A post has an id, author, title and text. When updating a post, either or both of title and text can change.
Is there an SQL query which can dynamically change the UPDATE based on the provided data? It would maybe look something like this in Nodejs pg:

const result = await pgClient.query("UPDATE post SET title = $1, text = $2 WHERE userId = $3", 
                                    ["new title", undefined, "12345"]);

with an additional clause saying something like "if undefined, do not update."

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Andrew Pulver
  • 178
  • 12

2 Answers2

2

Use COALESCE():

UPDATE post
SET    title = COALESCE($1, title)
     , text  = COALESCE($2, text)
WHERE  userId = $3
AND   (title <> $1 OR text <> $2);

Plus, add an additional WHERE clause to skip updates that would not change anything (but cost the same).
In your case at least one of the non-null parameters must actually differ to have an impact.
If columns themselves can be NULL, use instead:

...
AND   ($1 IS NOT NULL AND title IS DISTINCT FROM $1
    OR $2 IS NOT NULL AND text IS DISTINCT FROM $2);

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

how about something like this:

UPDATE  post
SET title = CASE WHEN $1 IS NULL THEN title ELSE $1 END
....
Z .
  • 12,657
  • 1
  • 31
  • 56
  • Thanks Z, that was pretty much it. I was getting the error "could not determine data type of patameter." So I found on this other post which says that you need to cast the variable to what it is supposed to be. So in this case, replace the $1 with $1::TEXT. https://stackoverflow.com/questions/56089400/postgres-sql-could-not-determine-data-type-of-parameter-by-hibernate. Thanks again! – Andrew Pulver Sep 16 '21 at 00:49