I got bitten by this one also. Perhaps it would be possible to change pg_dump so that it dumps the create table statement with the explicit sequence, rather than the original SQL used to create the table? (This would preserve old dumps and the syntactical sugar which I would not want to forego.)
Paul Tillotson
Hello.
I use PostgreSQL 7.4.2 on i386-portbld-freebsd5.2, compiled by GCC cc (GCC) 3.3.3 [FreeBSD] 20031106
Description: It seems that renaming tables with columns of type "serial" leaves "orphaned" implicit sequences which breaks pg_restore.
How to reproduce:
1. Create a table
CREATE DATABASE something1; CREATE DATABASE something2; \c something1 CREATE TABLE test1 (id serial, name char(12)); ALTER TABLE test1 RENAME TO test2;
2. Run dump/restore and get an error:
$ pg_dump -Fc something1 | pg_restore -d something2 pg_restore: NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for "serial" column "test2.id" pg_restore: [archiver (db)] could not execute query: ERROR: relation "test1_id_seq" does not exist
Workaround: Do not use the "serial" data type, always create sequences explicitly. pg_dump always generates a "CREATE SEQUENCE" clause for explicit sequences.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
