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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
