This table:

australia=# \d users_users
                                         Table "public.users_users"
     Column     |           Type           |
Modifiers
----------------+--------------------------+--------------------------------
---------------------------------
 userid         | integer                  | not null default
nextval('public.users_users_userid_seq'::text)
 sex            | character(1)             | not null
 suspended      | boolean                  | not null default 'f'
 recurring      | boolean                  | not null default 'f'
 referrer       | integer                  |
<snip fields>
Check constraints: "$2" (NOT (recurring AND suspended))
                   "users_users_sex" ((sex = 'M'::bpchar) OR (sex =
'F'::bpchar))
Foreign Key constraints: $1 FOREIGN KEY (referrer) REFERENCES
users_users(userid) ON UPDATE NO ACTION ON DELETE SET NULL

Is dumped like this:

CREATE TABLE users_users (
    userid SERIAL,
    sex character(1) NOT NULL,
    suspended boolean DEFAULT 'f' NOT NULL,
    recurring boolean DEFAULT 'f' NOT NULL,
    referrer integer,
    CHECK ((NOT (recurring AND suspended))),
    CONSTRAINT users_users_sex CHECK (((sex = 'M'::bpchar) OR (sex =
'F'::bpchar)))
);
ALTER TABLE ONLY users_users
    ADD CONSTRAINT "$1" FOREIGN KEY (referrer) REFERENCES
users_users(userid) ON UPDATE NO ACTION ON DELETE SET NULL;

Restoring this fails with:

ERROR:  constraint "$1" already exists for relation "users_users"

Solution:

We need to dump constraint name always for CHECK, or suppress default names
in ADD FOREIGN KEY.

Chris


Tue, 8 Jul 2003 11:00:27 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Another nasty pg_dump bug in 7.3 (and 7.4?)
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> We need to dump constraint name always for CHECK, or suppress default names
> in ADD FOREIGN KEY.

Didn't Rod Taylor fix that recently?

			regards, tom lane

Tue, 08 Jul 2003 01:48:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another nasty pg_dump bug in 7.3 (and 7.4?) 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > We need to dump constraint name always for CHECK, or suppress default
names
> > in ADD FOREIGN KEY.
>
> Didn't Rod Taylor fix that recently?

Maybe - I haven't checked 7.4's dump of that yet...I'll give it a go...

Chris


Tue, 8 Jul 2003 14:00:19 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Another nasty pg_dump bug in 7.3 (and 7.4?) 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > We need to dump constraint name always for CHECK, or suppress default
names
> > in ADD FOREIGN KEY.
>
> Didn't Rod Taylor fix that recently?

You're quite correct - it has been fixed.

Chris


Tue, 8 Jul 2003 14:05:41 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Another nasty pg_dump bug in 7.3 (and 7.4?) 