Horton

Thread

  1. Does RENAME TABLE rename associated identity sequence?

    Jason Song <pidaoh@g.skku.edu> — 2025-04-23T12:36:21Z

    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!

  2. Re: Does RENAME TABLE rename associated identity sequence?

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2025-04-24T09:52:44Z

    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

  3. Re: Does RENAME TABLE rename associated identity sequence?

    Isaac Morland <isaac.morland@gmail.com> — 2025-04-24T17:30:41Z

    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.

  4. Re: Does RENAME TABLE rename associated identity sequence?

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-24T17:40:05Z

    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

  5. Re: Does RENAME TABLE rename associated identity sequence?

    Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2025-04-25T10:28:28Z

    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.
>

I absolutely agree.

-- 
Best Wishes,
Ashutosh Bapat

  6. Re: Does RENAME TABLE rename associated identity sequence?

    Jason Song <pidaoh@g.skku.edu> — 2025-04-26T10:41:22Z

    Hi all,

Thank you for the detailed discussion and clarifications.

I sincerely appreciate everyone's time and valuable insights shared in this
discussion.