Home
About Me

Unique and Exclusion Constraints in Postgres

In Postgres, most constraints work on a single row, like check and not-null. Where we finally start to work across multiple rows is with unique constraints.

You can read a unique constraint as “no two rows have the same value for these columns”.

1
create table georgia_bulldogs (
2
  name text primary key,
3
  number int not null unique
4
);
5


6
insert into georgia_bulldogs (name, number)
7
values ('Carson Beck', 15), ('Random Freshman', 15);
8
-- ERROR:  duplicate key value violates unique constraint "georgia_bulldogs_number_key"
9
-- DETAIL:  Key (number)=(15) already exists.

Partial Indexes

Now, you can constrain columns for a subset of rows by using a partial index.

1
create table georgia_bulldogs (
2
  name text primary key,
3
  number int not null,
4
  starting boolean not null default false
5
);
6


7
create unique index on georgia_bulldogs (number) where starting is true;
8


9
insert into georgia_bulldogs (name, number, starting)
10
values ('Carson Beck', 15, true), ('Random Freshman', 15, false);
11
-- no problem
12


13
insert into georgia_bulldogs (name, number, starting)
14
values ('Trevor Etienne', 1, true), ('Other Random Freshman', 1, true);
15
-- ERROR:  duplicate key value violates unique constraint "georgia_bulldogs_number_idx"
16
-- DETAIL:  Key (number)=(1) already exists.

Handling of Nulls

No discussion of postgres unique constraints would be complete without mentioning nulls not distinct. (Note - I recently learned I’m not qualified to use nulls).

By default, two null values are not considered equal in a unique constraint.

1
create table a (
2
  value text unique
3
);
4


5
insert into a (value) values (null), (null);
6
-- all good

This behavior can be changed with nulls not distinct.

1
create table a (
2
  value text unique nulls not distinct
3
);
4


5
insert into a (value) values (null), (null);
6
-- ERROR:  duplicate key value violates unique constraint "a_value_key"
7
-- DETAIL:  Key (value)=(null) already exists.

Exclusion Constraints

An exclusion constraint is a generalized version of a unique constraint. They are all about preventing overlap between two rows, any form of overlap.

Reimplement a Unique Constraint With an Exclusion Constraint

1
create table georgia_bulldogs (
2
  name text primary key,
3
  number int not null,
4
  exclude (number with =)
5
);
6


7
insert into georgia_bulldogs (name, number)
8
values ('Carson Beck', 15), ('Random Freshman', 15);
9
-- ERROR:  conflicting key value violates exclusion constraint "georgia_bulldogs_number_excl"
10
-- DETAIL:  Key (number)=(15) conflicts with existing key (number)=(15).

However, with an exclusion constraint, we can use any operator that is:

  • commutative (a <operator> b = b <operator> a)
  • boolean
  • searchable by the given index

(List taken from this source).

In practice, the most useful one is the overlap operator, &&.

1
-- true
2
select daterange '[2023-01-01,2024-01-01)' && daterange '[2023-08-01,2024-05-29)';
3
-- false
4
select daterange '[2023-01-01,2024-01-01)' && daterange '[2024-08-01,2025-05-29)';

Scheduling - Overlapping Date Ranges

1
-- need to use = operator for integers with gist
2
create extension if not exists btree_gist;
3


4
create table rooms (
5
  id serial primary key
6
);
7


8
create table reservations (
9
  room_id int references rooms(id),
10
  time tstzrange not null,
11
  exclude using gist (room_id with =, time with &&)
12
);
13


14
insert into rooms default values;
15


16
insert into reservations (room_id, time)
17
select id, tstzrange(now(), now() + '5 days')
18
from rooms;
19


20
insert into reservations (room_id, time)
21
select id, tstzrange(now() + '3 days', now() + '7 days')
22
from rooms;

And we get an error that looks like this:

1
ERROR:  conflicting key value violates exclusion constraint "reservations_room_id_time_excl"
2
DETAIL:  Key (room_id, "time")=(1, ["2024-10-18 05:44:11.220589-04","2024-10-22 05:44:11.220589-04")) conflicts with existing key (room_id, "time")=(1, ["2024-10-15 05:44:11.216798-04","2024-10-20 05:44:11.216798-04")).

Resources

Wow! You read the whole thing. People who make it this far sometimes want to receive emails when I post something new.

I also have an RSS feed.