Question 54082 in Javascript Question posted in
A very good W3school tutorial can be found here.

Dynamic Update Postgres Queries – Javascript

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search