Re: Does RENAME TABLE rename associated identity sequence?

Hi Jason,

On Wed, Apr 23, 2025 at 6:06 PM Jason Song <pidaoh(at)g(dot)skku(dot)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