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
