From: Vsevolod (Simon) Ilyushchenko <simonf@cshl.edu>
To: pgsql-sql@postgresql.org
Subject: Foreign key pg_dump issue and serial column type
Date: Wed, 29 Jun 2005 20:25:55 -0400
Message-ID: <42C33C13.5010308@cshl.edu> (raw)
Hi,
As a relative newbie to postgres, I've run into to weirdisms that I
don't quite know how to handle:
1. I have a many-to-many table 'people_roles' containing fields
'person_code' and 'role_code'. It links tables 'people' and 'roles'.
There are foreign key constraints:
ALTER TABLE PEOPLE_ROLES add CONSTRAINT PRO_PEE_FK FOREIGN
KEY(PERSON_CODE) REFERENCES PEOPLE(PERSON_CODE) ON DELETE CASCADE NOT
DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE PEOPLE_ROLES add CONSTRAINT PRO_ROE_FK FOREIGN
KEY(ROLE_CODE) REFERENCES ROLES(ROLE_CODE) ON DELETE CASCADE NOT
DEFERRABLE INITIALLY IMMEDIATE;
However, when I pg_dump the database and import it on another server,
the tables are exported alphabetically, so when the 'people_roles' table
is created with its foreign keys, the table 'roles' does not exist yet.
Thus, the foreign key creation fails. Is there a way around it?
2. I've just discovered the 'serial' column type and tried to do this:
alter table people_roles alter column people_roles_code type serial;
To my surprise, it fails:
ERROR: type "serial" does not exist
However, I can create new tables with the 'serial' type without a hitch.
Is it a bug or a feature?
I'm running postgres 8.0.3 on Fedora Core 4.
Thanks,
Simon
--
Simon (Vsevolod ILyushchenko) simonf@cshl.edu
http://www.simonf.com
Terrorism is a tactic and so to declare war on terrorism
is equivalent to Roosevelt's declaring war on blitzkrieg.
Zbigniew Brzezinski, U.S. national security advisor, 1977-81
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: simonf@cshl.edu
Subject: Re: Foreign key pg_dump issue and serial column type
In-Reply-To: <42C33C13.5010308@cshl.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox