Обсуждение: 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