Making nulls distinct in Postgres < 15

[thecodedrift]

From the postgres docs on unique indexes:

When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.

Unfortunately, disabling distinct null values via NULLS NOT DISTINCT is a pg 15+ feature meaning it might be a while until you can use it. As a workaround, you can instead store a generated column. For example, a nullable uuid column that must still be unique can be solved first by creating the generated column

ALTER TABLE table_name
ADD COLUMN
  generated_column uuid GENERATED ALWAYS AS (
    COALESCE(nullable_uuid_column, uuid_nil())
  ) STORED

Then the resulting generated_column can be used in a unique index. The coalesce function lets you turn null values into a non-null value ensuring the unique index works as expected. In this case uuid_nil() returns a special uuid of all 0 characters.