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.
