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.
--
Victor Sudakov, VAS4-RIPE, VAS47-RIPN
