Skip to content

Instantly share code, notes, and snippets.

@mhfs

mhfs/gist:1511293

Created December 22, 2011 18:21
Show Gist options
  • Save mhfs/1511293 to your computer and use it in GitHub Desktop.
Save mhfs/1511293 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres conditional unique index
Raw
gistfile1.sql
create table unique_test (
number integer not null,
active boolean not null
)
create unique index unique_test_index on unique_test(number, boolean) where active;
insert into unique_test values (1, true);
insert into unique_test values (1, true);
-- ERROR: duplicate key value violates unique constraint "unique_test_index"
insert into unique_test values (1, false);
insert into unique_test values (1, false);
insert into unique_test values (1, false);
select * from unique_test ;
-- number | active
----------+--------
-- 1 | t
-- 1 | f
-- 1 | f
-- 1 | f
--(4 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment