CODEWITHSUNDEEP
postgresqlconstraintsupsert
Reed G. Law
Reed G. Law

Reputation: 3945

PostgreSQL ON CONFLICT DO UPDATE with not null column and COALESCE EXCLUDED column

I want to perform UPSERTs with data that might contain a not null constraint violation but without raising an error. Instead of an error, I want the proposed row to be skipped.

Schema:

               Table "public.appointments"
    Column   |            Type             |  Modifiers
-------------+-----------------------------+--------------
 id          | bigint                      | not null
 location_id | bigint                      | not null
 time        | timestamp without time zone | not null
 status      | text                        | not null
Indexes:
    "appointments_pkey" PRIMARY KEY, btree (id)
    "for_upsert" UNIQUE CONSTRAINT, btree (id, location_id)

Existing records:

 id | location_id | status |       time         
----+-------------+--------+----------------------
  1 |           2 | sched  | 2017-12-15 01:10:00
  2 |           2 | sched  | 2017-12-19 01:30:00

Query:

INSERT INTO "objects" ("id", "location_id", "time")
  VALUES (1, 2, 'sched', '2017-10-31 19:25:00'),
         (2, 2, 'canc', NULL),
         (3, 2, 'canc', NULL)
  ON CONFLICT ON CONSTRAINT for_upsert DO UPDATE
    SET "time" = COALESCE(EXCLUDED."time", "objects"."time"),
        "status" = EXCLUDED."status"

When a record exists with time not null, I want status to be updated regardless of the EXCLUDED."time" value (that's what the COALESCE attempts to do). But if there is no existing record and a new proposed row has a null time I don't want the row inserted or an error to be raised.

Upvotes: 3

Views: 6566

Answers (1)

Reed G. Law
Reed G. Law

Reputation: 3945

Adding this clause:

    WHERE COALESCE(EXCLUDED."time", "appointments"."time") IS NOT NULL;

after the DO UPDATE SET... seems to work.

Upvotes: 2

Related Questions