Thread: Create Primary Key?
Apropos of my last question: Is there syntax to create a primary key after the table has been defined and populated? I think I could speed things up quite a bit by not having any indexes at all when I do my mass copies. Thanks, and my apologies if that is a totally stupid question. W __________________________________________________ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
We don't currently support the SQL syntax for adding a PK to a table. However, if you have the columns as NOT NULL already, adding a unique index to the columns in question has the same general effect. Stephan Szabo sszabo@bigpanda.com On Tue, 29 Aug 2000, Webb Sprague wrote: > Apropos of my last question: > > Is there syntax to create a primary key after the > table has been defined and populated? I think I could > speed things up quite a bit by not having any indexes > at all when I do my mass copies.
Hi, It suppose to use ALTER TABLE tablename ADD constraint xxx primary key(columnname), unforturatly, Pg7.0 still haven't implemented it yet, so except foreign key ,other constarints, you have to choose: 1. rename your old table, recreate an new one with primary key, then use insert into newtable select * from oldtable. 2. use pg_dump -t tablename -f scriptfile dbname in shell , edit that scriptfile, add primary key there, drop your table, then reload it again: psql dbname<scriptfile Webb Sprague wrote: > Apropos of my last question: > > Is there syntax to create a primary key after the > table has been defined and populated? I think I could > speed things up quite a bit by not having any indexes > at all when I do my mass copies. > > Thanks, and my apologies if that is a totally stupid > question. > > W > > __________________________________________________ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com
Thus spake Stephan Szabo > We don't currently support the SQL syntax for adding > a PK to a table. However, if you have the columns > as NOT NULL already, adding a unique index to the > columns in question has the same general effect. Except for interfaces such as PyGreSQL that recognize the primary key and use it. > > Is there syntax to create a primary key after the > > table has been defined and populated? I think I could > > speed things up quite a bit by not having any indexes > > at all when I do my mass copies. Why not just use the copy function? That's what it's for. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Wed, 30 Aug 2000, D'Arcy J.M. Cain wrote: > Thus spake Stephan Szabo > > We don't currently support the SQL syntax for adding > > a PK to a table. However, if you have the columns > > as NOT NULL already, adding a unique index to the > > columns in question has the same general effect. > > Except for interfaces such as PyGreSQL that recognize the primary key > and use it. True. You'd have to see what it was doing to determine the primary key and try to do the same things.