Foreign key pg_dump issue and serial column type

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(at)cshl(dot)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