Hi,
I found a pg_dump/restore failure involving inherited columns and identity
columns.
Consider the following schema:
CREATE SCHEMA orchestrator;
CREATE SCHEMA tasks;
CREATE SEQUENCE orchestrator.task_id_seq AS integer;
CREATE TABLE orchestrator.task (
id integer NOT NULL DEFAULT
nextval('orchestrator.task_id_seq'::regclass)
);
CREATE TABLE tasks.kis05_formation_act_by_contract (
id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
child_id integer NOT NULL
) INHERITS (orchestrator.task);
The child table definition merges the local id column with the inherited one,
producing:
NOTICE: merging column "id" with inherited definition
A schema-only dump currently emits the child table roughly as:
CREATE TABLE tasks.kis05_formation_act_by_contract (
id integer DEFAULT nextval('orchestrator.task_id_seq'::regclass) NOT
NULL,
child_id integer NOT NULL
)
INHERITS (orchestrator.task);
ALTER TABLE tasks.kis05_formation_act_by_contract
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (...);
Restoring this dump fails with:
ERROR: column "id" of relation "kis05_formation_act_by_contract"
already has a default value
The failure happens because the inherited/default expression is emitted as part
of the CREATE TABLE, and then pg_dump emits ALTER COLUMN ... ADD GENERATED ...
AS IDENTITY for the identity sequence. However, ADD GENERATED cannot be applied
while the column has a default.
The attached patch makes pg_dump emit ALTER COLUMN ... DROP DEFAULT before
ALTER COLUMN ... ADD GENERATED ... AS IDENTITY, but only when pg_dump knows
that the owning column has a default expression.
With the patch, the dump becomes restorable:
ALTER TABLE tasks.kis05_formation_act_by_contract
ALTER COLUMN id DROP DEFAULT;
ALTER TABLE tasks.kis05_formation_act_by_contract
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (...);
This is a minimal fix for the invalid dump. I considered whether pg_dump
should instead emit the identity clause inline in the CREATE TABLE, but that
would require a larger change in how identity sequences are dumped. The
attached patch keeps the current structure and only removes the conflicting
default before adding the identity property.
A regression test is included for the pg_dump output and restore path.
Comments are welcome.
--
Antuan Violin, PostgresPro
From 1543c693d709f13acb4f0322dc9181fa2c0c9e5c Mon Sep 17 00:00:00 2001
From: Antoine Violin <[email protected]>
Date: Mon, 29 Jun 2026 12:03:00 +0700
Subject: [PATCH] pg_dump: drop column default before adding identity
---
src/bin/pg_dump/pg_dump.c | 16 ++++++++++++++++
src/bin/pg_dump/t/002_pg_dump.pl | 30 ++++++++++++++++++++++++++++++
2 files changed, 46 insertions(+)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 61c572664e7..8b0be39df00 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -19128,6 +19128,22 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
{
owning_tab = findTableByOid(tbinfo->owning_tab);
+ /*
+ * The column may already have a DEFAULT inherited/printed by CREATE TABLE.
+ * ADD GENERATED AS IDENTITY fails if a default is present, so remove it
+ * before adding identity.
+ */
+ if (owning_tab->attrdefs != NULL &&
+ owning_tab->attrdefs[tbinfo->owning_col - 1] != NULL)
+ {
+ appendPQExpBuffer(query,
+ "ALTER TABLE %s ",
+ fmtQualifiedDumpable(owning_tab));
+ appendPQExpBuffer(query,
+ "ALTER COLUMN %s DROP DEFAULT;\n",
+ fmtId(owning_tab->attnames[tbinfo->owning_col - 1]));
+ }
+
appendPQExpBuffer(query,
"ALTER TABLE %s ",
fmtQualifiedDumpable(owning_tab));
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 8595e0fa93b..1761b0c9539 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1640,6 +1640,36 @@ my %tests = (
},
},
+ 'ALTER TABLE inherited identity column DROP DEFAULT' => {
+ create_sql => q{
+ CREATE SEQUENCE dump_test.inherited_identity_column_id_seq AS integer;
+
+ CREATE TABLE dump_test.inherited_identity_column_parent (
+ id integer NOT NULL DEFAULT nextval('dump_test.inherited_identity_column_id_seq'::regclass)
+ );
+
+ CREATE TABLE dump_test.inherited_identity_column_child (
+ id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
+ child_id integer NOT NULL
+ ) INHERITS (dump_test.inherited_identity_column_parent);
+ },
+
+ regexp => qr/^
+ \QALTER TABLE dump_test.inherited_identity_column_child ALTER COLUMN id DROP DEFAULT;\E
+ .*?
+ \QALTER TABLE dump_test.inherited_identity_column_child ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY\E
+ /xms,
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
'ALTER FOREIGN TABLE foreign_table OWNER TO' => {
regexp =>
qr/^\QALTER FOREIGN TABLE dump_test.foreign_table OWNER TO \E.+;/m,
--
2.43.0
