Dynamic Update Postgres Queries

Question

Is it possible for a single API PUT endpoint to use dynamic UPDATE/INSERT queries?

Example: We have a patient table patient that has a first_name and last_name column. Sometimes we want to update the first name for a patient using a PUT http request but other times we want to update both first_name and last_name using that same PUT http request.

The first update query would like this:

UPDATE patient
SET first_name = "new first name"
WHERE id = 1

The second query would like this:

UPDATE patient
SET 
first_name = "new first name",
last_name = "new last name"
WHERE id = 1

We would like to combine this query to support both use cases without having to do a bunch of js gymnastics to inject sql into the query. We use pg-promise to support our psql/express integration.

http://vitaly-t.github.io/pg-promise/helpers.html#.update – Bergi Mar 24 '23 at 00:39 — Bergi, Mar 24 '23 at 00:39

Rohit · Answer 1 · 2023-03-21T17:03:47.000

1

You can use the COALESCE function.

UPDATE patient
SET 
first_name = COALESCE(<new first name>,first_name),
last_name = COALESCE(<new last name>,last_name)
WHERE id = 1

COALESCE returns the first non-null value it encounters. So if your new last name is null, it will preserve the original value of the column and likewise.

edited Mar 21 '23 at 17:03

answered Mar 21 '23 at 16:35

Rohit

44
3

1

Hello thank you for answering my question. This is a step in the right direction, however what if I want to set a column to null? – insivika Mar 22 '23 at 15:48

Belayer · Answer 2 · 2023-03-23T23:34:41.183

Your last comment indicates you may want to use NULL to two diametrically opposed purposes, you cannot do that. It would seem you have two options:

Write separate queries, invoking the one(s) necessary.
Designate a dummy string value that essentially says set the corresponding column to NULL. Assuming you want to avoid separate queries try something like: (see demo)

update patient 
   set first_name = nullif(coalesce(<new first name>,first_name), '<string meaning set to null>') 
     , last_name  = nullif(coalesce(<new last name> ,last_name),'<string meaning set to null>')
 where id = id_in;

The above uses the nullif() function to test the resulting value from coalesce() for a specific value. If those values match the result of the expression is NULL.

Dynamic Update Postgres Queries

2 Answers2