pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE TABLE ... LIKE INCLUDING ALL does not create new sequences for serial columns
3+ messages / 3 participants
[nested] [flat]
* CREATE TABLE ... LIKE INCLUDING ALL does not create new sequences for serial columns
@ 2025-11-03 17:10 Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>
0 siblings, 2 replies; 3+ messages in thread
From: Kumaresan Balakrishnan @ 2025-11-03 17:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Janarthanan Poornavel <jana@intics.ai>; Sivadhas arumugam <sivadhas.arumugam@intics.ai>; Intics Devops <intics.devops@intics.ai>
Dear PostgreSQL team,
I encountered an unexpected behavior when using the "CREATE TABLE ... LIKE ... INCLUDING ALL" syntax.
When a table is created this way from another table that contains serial or identity columns,
the new table’s column defaults still reference the *original* sequence objects from the source table,
rather than creating new independent sequences.
This results in both tables sharing the same sequence, which can cause data consistency issues
if inserts happen on both tables.
### Steps to reproduce
Tested on PostgreSQL 16.1 (Ubuntu 22.04, x86_64).
1. Create a base table:
CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE audit.statement_execution_audit (
id serial PRIMARY KEY,
description text
);
2. Backup and recreate the table:
DROP TABLE IF EXISTS audit.statement_execution_audit_backup;
ALTER TABLE audit.statement_execution_audit
RENAME TO statement_execution_audit_backup;
CREATE TABLE audit.statement_execution_audit
(LIKE audit.statement_execution_audit_backup INCLUDING ALL);
3. Inspect defaults:
\d audit.statement_execution_audit
### Observed behavior
The `id` column of the newly created table references the same sequence:
nextval('audit.statement_execution_audit_backup_id_seq'::regclass)
### Expected behavior
Ideally, the new table should receive its *own sequence* (for example,
`audit.statement_execution_audit_id_seq`), or there should be an option such as
`INCLUDING SEQUENCES` to explicitly request new sequence creation.
### Notes
This behavior occurs even with `INCLUDING ALL`, which implies `INCLUDING DEFAULTS`
and `INCLUDING IDENTITY`. However, it seems that when serial columns are used,
their associated sequences are copied by reference rather than duplicated.
If this is expected behavior, perhaps documentation could clarify that
`INCLUDING ALL` does not create new sequences for serial columns.
Thank you for your time and all your work maintaining PostgreSQL.
Regards,
Kumareasan
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: CREATE TABLE ... LIKE INCLUDING ALL does not create new sequences for serial columns
@ 2025-11-03 19:47 Sivadhas arumugam <sivadhas.arumugam@intics.ai>
parent: Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>
1 sibling, 0 replies; 3+ messages in thread
From: Sivadhas arumugam @ 2025-11-03 19:47 UTC (permalink / raw)
To: Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Janarthanan Poornavel <jana@intics.ai>; Intics Devops <intics.devops@intics.ai>
FYI - We tried logging in the issue through their JIRA which we weren't able to do. So, we have taken this route so far to at-least close the loop this way. We will keep you posted on next developments as we get more details.
Best regards
Sivadhas A
From: Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>
Sent: 03 November 2025 22:40
To: pgsql-bugs@lists.postgresql.org
Cc: Janarthanan Poornavel <jana@intics.ai>; Sivadhas arumugam <sivadhas.arumugam@intics.ai>; Intics Devops <intics.devops@intics.ai>
Subject: CREATE TABLE ... LIKE INCLUDING ALL does not create new sequences for serial columns
Dear PostgreSQL team,
I encountered an unexpected behavior when using the "CREATE TABLE ... LIKE ... INCLUDING ALL" syntax.
When a table is created this way from another table that contains serial or identity columns,
the new table's column defaults still reference the *original* sequence objects from the source table,
rather than creating new independent sequences.
This results in both tables sharing the same sequence, which can cause data consistency issues
if inserts happen on both tables.
### Steps to reproduce
Tested on PostgreSQL 16.1 (Ubuntu 22.04, x86_64).
1. Create a base table:
CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE audit.statement_execution_audit (
id serial PRIMARY KEY,
description text
);
2. Backup and recreate the table:
DROP TABLE IF EXISTS audit.statement_execution_audit_backup;
ALTER TABLE audit.statement_execution_audit
RENAME TO statement_execution_audit_backup;
CREATE TABLE audit.statement_execution_audit
(LIKE audit.statement_execution_audit_backup INCLUDING ALL);
3. Inspect defaults:
\d audit.statement_execution_audit
### Observed behavior
The `id` column of the newly created table references the same sequence:
nextval('audit.statement_execution_audit_backup_id_seq'::regclass)
### Expected behavior
Ideally, the new table should receive its *own sequence* (for example,
`audit.statement_execution_audit_id_seq`), or there should be an option such as
`INCLUDING SEQUENCES` to explicitly request new sequence creation.
### Notes
This behavior occurs even with `INCLUDING ALL`, which implies `INCLUDING DEFAULTS`
and `INCLUDING IDENTITY`. However, it seems that when serial columns are used,
their associated sequences are copied by reference rather than duplicated.
If this is expected behavior, perhaps documentation could clarify that
`INCLUDING ALL` does not create new sequences for serial columns.
Thank you for your time and all your work maintaining PostgreSQL.
Regards,
Kumareasan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CREATE TABLE ... LIKE INCLUDING ALL does not create new sequences for serial columns
@ 2025-11-05 13:01 Peter Eisentraut <peter@eisentraut.org>
parent: Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2025-11-05 13:01 UTC (permalink / raw)
To: Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Janarthanan Poornavel <jana@intics.ai>; Sivadhas arumugam <sivadhas.arumugam@intics.ai>; Intics Devops <intics.devops@intics.ai>
On 03.11.25 18:10, Kumaresan Balakrishnan wrote:
> This behavior occurs even with `INCLUDING ALL`, which implies `INCLUDING
> DEFAULTS`
> and `INCLUDING IDENTITY`. However, it seems that when serial columns
> are used,
> their associated sequences are copied by reference rather than duplicated.
This works correctly if you use GENERATED ALWAYS AS IDENTITY instead of
serial.
Using serial is kind of deprecated for reasons like this.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-11-05 13:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-03 17:10 CREATE TABLE ... LIKE INCLUDING ALL does not create new sequences for serial columns Kumaresan Balakrishnan <kumaresan.balakrishnan@intics.ai>
2025-11-03 19:47 ` Sivadhas arumugam <sivadhas.arumugam@intics.ai>
2025-11-05 13:01 ` Peter Eisentraut <peter@eisentraut.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox