[SOLVED] Postgres - Unique constraint with multiple columns and NULL values

Postgres - Unique constraint with multiple columns and NULL values

I have problem with Postgres Unique constraint with multiple columns that may contain NULL value.

Let's assume this situation:

CREATE TEMP TABLE test (
  foo TEXT,
  bar TEXT,
  UNIQUE (foo, bar)
);

INSERT INTO test
VALUES 
  ('foo', NULL),
  ('foo', NULL),
  ('foo', 'bar'),
  ('foo', 'bar')
ON CONFLICT (foo, bar) DO NOTHING;

Insert will insert ('foo', 'bar') once and ('foo', NULL) twice (even though the intuition says it should insert once).

In this scenario solution is pretty straightforward. I could just add unique index

CREATE UNIQUE INDEX indx ON test (foo) WHERE bar IS NULL;

But the problem starts when there is more columns and with different types (not only text). Let's say we have 10 columns and 9 of them can have NULL value. Maybe I could solve it with big amount of constraints, but it's not convenient at all.

Is there easier way to keep uniqueness for a row like that?

Solution

For PostgreSQL v15 or better, see Naeel's answer. For lower versions, try the following:

An alternative to the good solution of forbidding NULLs is to create a unique index.

All you need is a value that is guaranteed not to occur in your data set (in my example '@@'):

CREATE UNIQUE INDEX ON test (
   coalesce(foo, '@@'),
   coalesce(bar, '@@')
);