On Wed, 2010-06-02 at 21:06 -0400, Bruce Momjian wrote: > Simon Riggs wrote: > > > > Deferrable unique constraints seem an interesting feature, though I have > > either some questions or some issues, not sure which. > > > > I don't seem to be able to find any way to do an ALTER TABLE that adds > > this new capability to an existing table. > > I was able to do it: > > test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED); > NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_x_key" > for table "test" > CREATE TABLE > > test=> alter table test add column y int; > ALTER TABLE > > test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED; > NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index > "test_y_key" for table "test" > ALTER TABLE > > Is that what you were asking?
No. I wanted to defer an existing UNIQUE constraint. That doesn't seem to be possible. You *can* add a whole new constraint and then drop the old one, though that's not quite as cool. > > There is no way to add a constraint via a CREATE TABLE AS SELECT, so > > that means there is no way to use the feature at all in that case. > > Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not > heard any complaints about it before. > > > Also, foreign keys can't be defined that refer to a deferrable primary > > key. That isn't mentioned at all in the manual with regard to the > > DEFERRABLE clause, though it is mentioned in the FK section. You get > > this error message > > ERROR: cannot use a deferrable unique constraint for referenced table > > > > The use case for this feature looks a little narrow at present. Can we > > do something about usability? > > Not sure why that was a limitation. Regrettably it makes it an unusable limitation for many people. All large tables are referenced in a typical database that uses PKs/FKs. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
