Re: Renaming table doesn't rename primary key index or serial

Yes, this is a known problem, and our TODO list mentions them.  We
haven't had time to fix them yet.

---------------------------------------------------------------------------

Jonathan Gardner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> PostgreSQL 7.3.2 (Redhat 9)
>
> When creating a table, primary key indexes and serial sequences are created
> as well. Naively altering the name of the table does not modify the names
> of the dependent primary key indexes and serial sequences. It was expected
> that the indexes and serial sequences would've been renamed as if they had
> been created originally with the new table name.
>
> This isn't a critical bug, but it would make PostgreSQL that much more
> accessible to novices.
>
> # CREATE TABLE test ( id SERIAL PRIMARY KEY );
> NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL
> column 'test.id'
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey'
> for table 'test'
> CREATE TABLE
>
> # ALTER TABLE test RENAME TO old_test;
> ALTER TABLE
>
> # CREATE TABLE test ( id SERIAL PRIMARY KEY );
> NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL
> column 'test.id'
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey'
> for table 'test'
> ERROR:  Relation 'test_id_seq' already exists
>
> Also, note that:
> # CREATE TABLE test ( id INTEGER PRIMARY KEY );
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey'
> for table 'test'
> CREATE TABLE
>
> # ALTER TABLE test RENAME TO old_test;
> ALTER TABLE
>
> # CREATE TABLE test ( id INTEGER PRIMARY KEY );
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey'
> for table 'test'
> ERROR:  relation named "test_pkey" already exists

--
  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