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 | 12 | 2
(2 rows)
