Thread: pg_dump serial UNIQUE NOT NULL PRIMARY KEY
Hello, I'm a fairly new user of PostgreSQL 8.0.2 and I'm creating a table like this: CREATE TABLE cases ( id serial PRIMARY KEY, Works fine, but when I pg_dump the database, what shows up in the dump is: CREATE TABLE cases ( id serial NOT NULL, The documentation seems to say that 'serial NOT NULL' is not at all the same as 'serial PRIMARY KEY': ( http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL ) "Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type." Any help appreciated, Michael Hipp
On Fri, Apr 22, 2005 at 04:44:05PM -0500, Michael Hipp wrote: > > Hello, I'm a fairly new user of PostgreSQL 8.0.2 and I'm creating a table > like this: > > CREATE TABLE cases ( > id serial PRIMARY KEY, > > Works fine, but when I pg_dump the database, what shows up in the dump is: > > CREATE TABLE cases ( > id serial NOT NULL, > > The documentation seems to say that 'serial NOT NULL' is not at all the > same as 'serial PRIMARY KEY': Later in the dump file you should see something like this: ALTER TABLE ONLY cases ADD CONSTRAINT cases_pkey PRIMARY KEY (id); Presumably that's because adding the primary key constraint after populating the table is more efficient than populating the table with the constraint (and the associated index) in place. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Hipp <Michael@Hipp.com> writes: > Hello, I'm a fairly new user of PostgreSQL 8.0.2 and I'm creating a table like > this: > CREATE TABLE cases ( > id serial PRIMARY KEY, > Works fine, but when I pg_dump the database, what shows up in the dump is: > CREATE TABLE cases ( > id serial NOT NULL, pg_dump prefers to add the PRIMARY KEY via an ALTER TABLE, after it's got done loading data into the table. This is basically a speed hack (see the manual's advice about bulk data loading). If you don't see an appropriate ALTER down near the end of the dump, then you've got grounds for complaint ... regards, tom lane