How to handle possible NULL in a Trigger Updating a table without a Primary Key

Question

I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary key, but both of those columns allow NULL values.

For tables with regular NOT NULL primary keys, a trigger that calls a function like this works well:

UPDATE "foo"
SET
 "bar_id" = NEW."bar_id",
 "baz_id" = NEW."baz_id",
 "created_at" = NEW."created_at",
 "updated_at" = NEW."updated_at"
WHERE
 "foo"."bar_id" = OLD."bar_id"
AND "foo"."baz_id" = OLD."baz_id"

I update the 2nd table with all the NEW values from the first table, where the old "primary key" values match. However, If either of the keys are NULL then nothing gets updated, since NULL != NULL.

I'm considering handling this by adding several IS NULL checks, like:

UPDATE "foo"
SET
 "bar_id" = NEW."bar_id",
 "baz_id" = NEW."baz_id",
 "created_at" = NEW."created_at",
 "updated_at" = NEW."updated_at"
WHERE
 ("foo"."bar_id" = OLD."bar_id" OR ("foo"."bar_id" IS NULL AND OLD."bar_id" IS NULL))
AND ("foo"."baz_id" = OLD."baz_id" OR ("foo"."baz_id" IS NULL AND OLD."baz_id" IS NULL))

Is there a better way to handle possible NULLs in WHERE clauses like these?

Answer 1

See if IS NOT DISTINCT FROM serves your purpose.

IS (NOT) DISTINCT FROM is the equivalent of the = operator, but also works with NULLs. If one input is NULL and one input is not, the result is TRUE, or FALSE when both inputs are NULL.

For example, you would use it like this in your update statement.

UPDATE "foo"
SET
 "bar_id" = NEW."bar_id",
 "baz_id" = NEW."baz_id",
 "created_at" = NEW."created_at",
 "updated_at" = NEW."updated_at"
WHERE "foo"."bar_id" IS NOT DISTINCT FROM OLD."bar_id"
 AND "foo"."baz_id" IS NOT DISTINCT FROM OLD."baz_id"

• Brendan McCaffrey: Welcome to DBA.SE. Links are great to support answers. However, they should only be needed to validate from what you've shared. With a well answered question, someone should not need to hit the link, unless they don't believe the answer or want a more in-depth understanding of the concept.