Deferrable Unique Constraints

I noticed that implementing deferrable unique constraints is on the
TODO list. I don't think its been said yet, but currently you can
implement a deferrable unique constraint by using a deferrable
constraint trigger together with a procedural language like plpgsql.
If you need an index on a column, you can use a regular index instead
of a unique index.

Yes, I noticed that getting rid of constraint triggers is also on the
TODO list.

Below is an example.

George Essig

------------------------------------------

create table t (x integer, y integer);
create index t_x_in on t (x);

-- Create a trigger function to test for duplicate values of x.
-- Table t column x unique insert update trigger function.

create or replace function t_x_un_ins_up_tr() RETURNS "trigger"
AS '
declare
invalid integer;
begin

-- Not absolutely necessary, but avoids a query if the new and old
-- values of x are the same.

if TG_OP = ''UPDATE'' then
if new.x = old.x then
return new;
end if;
end if;

-- If 2 or more rows have the same value of x, set invalid to 1.

select 1 into invalid
from t
where x = new.x
offset 1 limit 1;

-- If found, raise exception.

if FOUND then
raise EXCEPTION
''Violation of unique constraint on column x in table t by new row:
x %, y %'', new.x, new.y;
end if;

return new;
end;'
LANGUAGE plpgsql;

-- Create a deferrable constraint trigger that executes the trigger function.
-- This runs at transaction commit time for every row that was inserted or updated.

create constraint trigger t_x_un_ins_up_tr after insert or update on t
deferrable initially deferred
for each row
execute procedure t_x_un_ins_up_tr ();

-- Begin a transaction.
-- Insert duplicate values of x successfully.
-- Violation of constraint when transaction is committed.

test=# begin;
BEGIN
test=# insert into t (x, y) values (1,1);
INSERT 30332079 1
test=# insert into t (x, y) values (1,2);
INSERT 30332080 1
test=# commit;
ERROR: Violation of unique constraint on column x in table t by new row:
x 1, y 1
test=# select * from t;
x | y
---+---
(0 rows)

-- Begin a transaction.
-- Insert duplicate values of x successfully.
-- Update one of the duplicate values to another value.
-- Commit transaction successfully.

test=# begin;
BEGIN
test=# insert into t (x, y) values (1,1);
INSERT 30332083 1
test=# insert into t (x, y) values (1,2);
INSERT 30332084 1
test=# update t set x = 2 where y = 2;
UPDATE 1
test=# commit;
COMMIT
test=# select * from t;
x | y
---+---
1 | 1
2 | 2
(2 rows)