Thread: 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=
=20
as well. Naively altering the name of the table does not modify the names=
=20
of the dependent primary key indexes and serial sequences. It was expected=
=20
that the indexes and serial sequences would've been renamed as if they had=
=20
been created originally with the new table name.

This isn't a critical bug, but it would make PostgreSQL that much more=20
accessible to novices.

# CREATE TABLE test ( id SERIAL PRIMARY KEY );
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIA=
L=20
column 'test.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey'=
=20
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 SERIA=
L=20
column 'test.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey'=
=20
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'=
=20
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'=
=20
for table 'test'
ERROR:  relation named "test_pkey" already exists

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

iD4DBQE/RRxVWgwF3QvpWNwRAlpnAJi27GohB3uCqdZlz4d8t6js/H8qAKDP+Rxt
Pvex0xAqvaIQAU/5DCLPww=3D=3D
=3Dhsw/
-----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@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