Simon Riggs wrote: > 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.
Do we allow any kind of constraint modification via ALTER TABLE? I don't see much listed in the manual. > > > 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. Yeah, no question. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
