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?

Laurenz Albe · Accepted Answer · 2022-11-04 06:51:52Z

16

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

edited Nov 4, 2022 at 6:51

answered Feb 13, 2020 at 12:08

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

phaen Over a year ago

Haven't thought of coalesce with a unique constraint so far, thats in fact with the combination of non-nullable defaults a pretty solid way of handling this issue!

Laurenz Albe Over a year ago

If you can manage to set the column NOT NULL, forget the coalesce.

puchal Over a year ago

Thank you that's perfect solution for my case. Unfortunately I need columns with values with NULL and there are different column types, so this will work perfectly for me

Naeel Maqsudov Over a year ago

@puchal, pay attention to the new feature of PG 15 — F292 «Unique null treatment».

benshabatnoam · Accepted Answer · 2024-11-14 11:03:19Z

7

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

edited Nov 14, 2024 at 11:03

benshabatnoam

7,7403 gold badges37 silver badges55 bronze badges

answered Nov 4, 2022 at 3:06

Naeel Maqsudov

1,45620 silver badges26 bronze badges

Comments

phaen · Accepted Answer · 2020-02-13 12:01:12Z

4

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

answered Feb 13, 2020 at 12:01

phaen

3883 silver badges13 bronze badges

1 Comment

puchal Over a year ago

This is a great suggestion, but what with a case where columns can be different type then text (for example integer that can be missing). I'm sorry I didn't make it clear enough in question. I'll edit it accordingly.

Collectives™ on Stack Overflow

Postgres - Unique constraint with multiple columns and NULL values

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related