CODEWITHSUNDEEP
postgresql
puchal
puchal

Reputation: 2381

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?

Upvotes: 14

Views: 8811

Answers (3)

Naeel Maqsudov
Naeel Maqsudov

Reputation: 1434

BTW, since version 15 Postgress supports optional feature of SQL specification F292 «Unique null treatment». It allows to control behavior NULL values in unique constrains

create table ... (
  ..........,
  UNIQUE NULLS [NOT] DISTINCT (...)
);

Another useful link

Example to make live easier...

ALTER TABLE public.teams ADD CONSTRAINT teams_unique UNIQUE NULLS NOT DISTINCT (id, user_id, team_id);

Upvotes: 7

Laurenz Albe
Laurenz Albe

Reputation: 247625

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

Upvotes: 16

phaen
phaen

Reputation: 388

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)
);

Upvotes: 4

Related Questions