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

#1

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

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

Jonathan Gardner wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Renaming table doesn't rename primary key index or serial - 08-27-2003 , 09:55 PM