Postgres - Unique constraint with multiple columns and NULL values

Question

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?

Answer 1

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, '@@')
);

Answer 2

You might consider the column definition in general, as both are stated as texts, you can just let them be NOT NULLABLE and provide a DEFAULT value as ' ' (empty string). This way you can be sure, that foo does not get saved twice. Also NULL values are not that good in practice, because its just a sign that there is a MISSING value.

CREATE TEMP TABLE test (
  foo TEXT DEFAULT '' NOT NULL,
  bar TEXT DEFAULT '' NOT NULL,
  UNIQUE (foo, bar)
);