Question 89387 in PostgreSQL Question posted in
The official documentation can be found here.

I want to update specific fields in PostgreSQL only if they are null

I was looking to update fields on a row in PostgreSQL only if the fields were null. Otherwise I would overwrite what’s already in the row.
My update looks as simple as this:

UPDATE public.people
    SET flag=$flag,
    name=$name,
    surname=$surname
    WHERE id_dt=$id_dt;

Can you help?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here you go:

    UPDATE public.people
    SET flag=$flag,
    name=coalesce(name,$name),
    surname=coalesce(surname,$surname)
    WHERE id_dt=$id_dt

    Coalesce() returns the first non-null value, so name if it's already there, or $name (that is your new value)


  2. 0

    Looks like you could start a transaction with 2 statemnts and add a where clause that ignores correct rows:

    BEGIN;
UPDATE public.people
    SET flag = $flag,
    name = $name
    WHERE id_dt=$id_dt
    AND name IS NULL;

UPDATE public.people
    SET flag = $flag,
    surname = $surname
    WHERE id_dt=$id_dt
    AND surname IS NULL;
COMMIT;

    If you go with the coalesce option like stated in a simpling answer, you can still add a where name is null or surname is null clause to reduce the working set.

    Side note: I see you use $ like it was used in string interpolation. Always prefer to use prepared statements to avoid attacks via sql injection.

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

Back To Top
Search