pg_dump serial UNIQUE NOT NULL PRIMARY KEY

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