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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073