Thread: Does RENAME TABLE rename associated identity sequence?
Hi hackers,
I was wondering if there's any built-in functionality in PostgreSQL where renaming a table with an identity column would also rename the auto-generated sequence associated with that identity column.
In my case, I renamed a table that used `GENERATED BY DEFAULT AS IDENTITY`, and later when I ran `pg_dump`, I noticed that the sequence name was unchanged (e.g., still `old_table_id_seq`). As a result, any `setval()` or sequence-related operations referenced the old sequence name, even though the table name had changed.
I realize this can be worked around — for example, by using `--exclude-table-data` to skip the `setval()` or manually renaming the sequence after the table rename. But I'm curious if there are any plans (or technical reasons against) supporting something like `ALTER TABLE ... RENAME ... WITH SEQUENCE`, or having the sequence name automatically follow the table rename when it was originally auto-generated by an identity column.
Thanks for your time!
I was wondering if there's any built-in functionality in PostgreSQL where renaming a table with an identity column would also rename the auto-generated sequence associated with that identity column.
In my case, I renamed a table that used `GENERATED BY DEFAULT AS IDENTITY`, and later when I ran `pg_dump`, I noticed that the sequence name was unchanged (e.g., still `old_table_id_seq`). As a result, any `setval()` or sequence-related operations referenced the old sequence name, even though the table name had changed.
I realize this can be worked around — for example, by using `--exclude-table-data` to skip the `setval()` or manually renaming the sequence after the table rename. But I'm curious if there are any plans (or technical reasons against) supporting something like `ALTER TABLE ... RENAME ... WITH SEQUENCE`, or having the sequence name automatically follow the table rename when it was originally auto-generated by an identity column.
Thanks for your time!
Hi Jason,
On Wed, Apr 23, 2025 at 6:06 PM Jason Song <pidaoh@g.skku.edu> wrote:
Hi hackers,
I was wondering if there's any built-in functionality in PostgreSQL where renaming a table with an identity column would also rename the auto-generated sequence associated with that identity column.
In my case, I renamed a table that used `GENERATED BY DEFAULT AS IDENTITY`, and later when I ran `pg_dump`, I noticed that the sequence name was unchanged (e.g., still `old_table_id_seq`). As a result, any `setval()` or sequence-related operations referenced the old sequence name, even though the table name had changed.
Is it causing a problem in your application or environment?
As long as the system uses the given name consistently and does not cause any error due to trying to derive sequence name from new table name and ending up in non-existent sequence error, it should be fine. Internally we use OID and not name. Identity sequences are not expected to be manipulated by users anyway, so the name shouldn't matter.
I agree that finding an identity sequence associated with a table is not straightforward - that association is stored in pg_depends, which is not intuitive. Take a look at getIdentitySequence().
I realize this can be worked around — for example, by using `--exclude-table-data` to skip the `setval()` or manually renaming the sequence after the table rename. But I'm curious if there are any plans (or technical reasons against) supporting something like `ALTER TABLE ... RENAME ... WITH SEQUENCE`, or having the sequence name automatically follow the table rename when it was originally auto-generated by an identity column.
If there's any problem, IMO, ALTER TABLE ... RENAME ... should rename the sequence too since the identity sequences are created implicitly when the table is created, so they should be renamed implicitly. We should not require WITH SEQUENCE clause.
Best Wishes,
Ashutosh Bapat
On Thu, 24 Apr 2025 at 05:53, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
If there's any problem, IMO, ALTER TABLE ... RENAME ... should rename the sequence too since the identity sequences are created implicitly when the table is created, so they should be renamed implicitly. We should not require WITH SEQUENCE clause.
My concern would be what happens if the new sequence name is not available. I suppose the simplest behaviour might be to skip renaming the sequence in that case, perhaps raising a warning.
Isaac Morland <isaac.morland@gmail.com> writes:
> On Thu, 24 Apr 2025 at 05:53, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
> wrote:
>> If there's any problem, IMO, ALTER TABLE ... RENAME ... should rename the
>> sequence too since the identity sequences are created implicitly when the
>> table is created, so they should be renamed implicitly. We should not
>> require WITH SEQUENCE clause.
> My concern would be what happens if the new sequence name is not available.
> I suppose the simplest behaviour might be to skip renaming the sequence in
> that case, perhaps raising a warning.
We do not rename any other subsidiary objects such as indexes.
Why would we rename a sequence (which has a lot more reason
to be considered an independent object than an index does)?
regression=# create table foo (i int primary key);
CREATE TABLE
regression=# \d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
i | integer | | not null | | plain | | |
Indexes:
"foo_pkey" PRIMARY KEY, btree (i)
Not-null constraints:
"foo_i_not_null" NOT NULL "i"
Access method: heap
regression=# alter table foo rename to bar;
ALTER TABLE
regression=# \d+ bar
Table "public.bar"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
i | integer | | not null | | plain | | |
Indexes:
"foo_pkey" PRIMARY KEY, btree (i)
Not-null constraints:
"foo_i_not_null" NOT NULL "i"
Access method: heap
I think it's up to the user to rename subsidiary objects if
they wish to do so.
regards, tom lane
On Thu, Apr 24, 2025 at 11:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think it's up to the user to rename subsidiary objects if
they wish to do so.
Best Wishes,
Ashutosh Bapat