Looks like this TODO item:
o Have ALTER TABLE rename SERIAL sequences
Sorry we haven't fixed it yet.
---------------------------------------------------------------------------
Victor Sudakov wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
