Search code examples
databasepostgresqltimescaledb

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?

Solution

  • 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)