Renaming table doesn't rename primary key index or serial sequences

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

- --
Jonathan Gardner
jgardner(at)jonathangardner(dot)net
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD4DBQE/RRxVWgwF3QvpWNwRAlpnAJi27GohB3uCqdZlz4d8t6js/H8qAKDP+Rxt
Pvex0xAqvaIQAU/5DCLPww==
=hsw/
-----END PGP SIGNATURE-----

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