[PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Started by Akshay Joshi21 days ago32 messageshackers

akshay.joshi@enterprisedb.com

21 days ago

Hackers,

Attached is a patch adding pg_get_table_ddl(regclass, VARIADIC text[]),
extending the existing pg_get_database/role/tablespace_ddl family.
relations. It returns the CREATE TABLE statement plus the follow-up ALTER
TABLE, CREATE INDEX, CREATE RULE, and CREATE STATISTICS statements needed
for a full reconstruction, one per row.

*Coverage*
--------
*Per-column:* typmod, COLLATE, STORAGE, COMPRESSION (pglz/lz4), GENERATED
STORED/VIRTUAL, IDENTITY ALWAYS/BY DEFAULT (with non-default sequence
options), DEFAULT, NOT NULL, attoptions.

*Table-level:* UNLOGGED, INHERITS, PARTITION BY (RANGE/LIST/HASH),
PARTITION OF ... FOR VALUES, USING access method, WITH (reloptions),
TABLESPACE, inline CHECK.

*Sub-objects:* Indexes, Constraints (PK/UNIQUE/FK/EXCLUDE/named NOT NULL),
Rules, extended statistics, REPLICA IDENTITY, ENABLE/FORCE RLS, and
child-local DEFAULT overrides.
*Triggers and policies are TODOs pending pg_get_trigger_ddl (Phil's
re-roll) and pg_get_policy_ddl (Waiting for review/commit)*.

Options (pretty, owner, tablespace, and a family of *includes_** toggles
for each sub-object class) let callers fine-tune the output. Every clause
is omitted when its value equals what the system would reapply on
round-trip same default-omission convention as the existing _ddl functions.

I deliberately did *not* add pg_get_index_ddl / _constraint_ddl / _rule_ddl
/ _stat_ddl wrappers. The existing C helpers in ruleutils.c
(pg_get_indexdef_string, pg_get_constraintdef_command, etc.) already emit
reproducible statements, so pg_get_table_ddl_internal calls them directly.
Happy to revisit if reviewers prefer separate SQL-level surfaces.

*Testing: *
pg_regress test at src/test/regress/sql/pg_get_table_ddl.sql covering the
matrix above plus error paths. I kept it as pg_regress rather than TAP
(where the sibling _ddl tests live) since exact-.out diffs are the more
rigorous check for a deparse function, and the pg_regress restriction that
drove the TAP choice for CREATE DATABASE/TABLESPACE doesn't apply to
tables. A manual round-trip across IDENTITY/GENERATED/
STORAGE/COMPRESSION/PK produces an empty EXCEPT diff against the original.

*Out of scope:* COMMENT ON, GRANT/REVOKE (matching the existing _ddl
family), and typed tables (CREATE TABLE name OF type).

Feedback is welcome, particularly on the option naming and the decision to
reuse the C helpers directly rather than add new SQL wrappers.

-----
Regards,
Akshay Joshi
Principal Engineer | Engineering Manager | pgAdmin Hacker
EDB (EnterpriseDB)

Akshay Joshi

akshay.joshi@enterprisedb.com

16 days ago

In reply to: Akshay Joshi (#1)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Hi Hackers,

Attached is the v2 patch, which fixes the Meson build failure, and the
rebased patch.

On Wed, Jun 3, 2026 at 6:28 PM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:

Show quoted text

Hackers,

Attached is a patch adding pg_get_table_ddl(regclass, VARIADIC text[]),
extending the existing pg_get_database/role/tablespace_ddl family.
relations. It returns the CREATE TABLE statement plus the follow-up ALTER
TABLE, CREATE INDEX, CREATE RULE, and CREATE STATISTICS statements needed
for a full reconstruction, one per row.

*Coverage*
--------
*Per-column:* typmod, COLLATE, STORAGE, COMPRESSION (pglz/lz4), GENERATED
STORED/VIRTUAL, IDENTITY ALWAYS/BY DEFAULT (with non-default sequence
options), DEFAULT, NOT NULL, attoptions.

*Table-level:* UNLOGGED, INHERITS, PARTITION BY (RANGE/LIST/HASH),
PARTITION OF ... FOR VALUES, USING access method, WITH (reloptions),
TABLESPACE, inline CHECK.

*Sub-objects:* Indexes, Constraints (PK/UNIQUE/FK/EXCLUDE/named NOT
NULL), Rules, extended statistics, REPLICA IDENTITY, ENABLE/FORCE RLS, and
child-local DEFAULT overrides.
*Triggers and policies are TODOs pending pg_get_trigger_ddl (Phil's
re-roll) and pg_get_policy_ddl (Waiting for review/commit)*.

Options (pretty, owner, tablespace, and a family of *includes_** toggles
for each sub-object class) let callers fine-tune the output. Every clause
is omitted when its value equals what the system would reapply on
round-trip same default-omission convention as the existing _ddl functions.

I deliberately did *not* add pg_get_index_ddl / _constraint_ddl /
_rule_ddl / _stat_ddl wrappers. The existing C helpers in ruleutils.c
(pg_get_indexdef_string, pg_get_constraintdef_command, etc.) already emit
reproducible statements, so pg_get_table_ddl_internal calls them directly.
Happy to revisit if reviewers prefer separate SQL-level surfaces.

*Testing: *
pg_regress test at src/test/regress/sql/pg_get_table_ddl.sql covering the
matrix above plus error paths. I kept it as pg_regress rather than TAP
(where the sibling _ddl tests live) since exact-.out diffs are the more
rigorous check for a deparse function, and the pg_regress restriction that
drove the TAP choice for CREATE DATABASE/TABLESPACE doesn't apply to
tables. A manual round-trip across IDENTITY/GENERATED/
STORAGE/COMPRESSION/PK produces an empty EXCEPT diff against the original.

*Out of scope:* COMMENT ON, GRANT/REVOKE (matching the existing _ddl
family), and typed tables (CREATE TABLE name OF type).

Feedback is welcome, particularly on the option naming and the decision to
reuse the C helpers directly rather than add new SQL wrappers.

-----
Regards,
Akshay Joshi
Principal Engineer | Engineering Manager | pgAdmin Hacker
EDB (EnterpriseDB)

Marcos Pegoraro

marcos@f10.com.br

15 days ago

In reply to: Akshay Joshi (#2)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Em seg., 8 de jun. de 2026 às 08:30, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

Hi Hackers,

Attached is the v2 patch, which fixes the Meson build failure, and the
rebased patch.

Would be good to have an option "schema-qualified" boolean, because
sometimes I don't want a schema-qualified result.
Suppose you are duplicating a schema, the way your did you cannot do
something like this to have a complete script to generate a new schema

select 'create schema new_schema;' union all
select 'set search_path to new_schema, public;' union all
select string_agg(pg_get_table_ddl(oid),',') from pg_class where
relnamespace::regnamespace::text = 'old_schema' and relkind = 'r';

regards
Marcos

Zsolt Parragi

zsolt.parragi@percona.com

15 days ago

In reply to: Akshay Joshi (#2)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Hello!

I did some basic testing with the new functions, and found a few bugs:

1. Seems like check constraints on partitions are ignored:

CREATE TABLE p (id int, val int) PARTITION BY RANGE (id);
CREATE TABLE p_child PARTITION OF p (CONSTRAINT chk_inline CHECK (val > 0))
FOR VALUES FROM (0) TO (100);
SELECT * FROM pg_get_table_ddl('p_child', 'owner','false');

2. inherited stored generated columns can't be replayed:

CREATE TABLE par_s (
id int,
g int GENERATED ALWAYS AS (id * 2) STORED
);
CREATE TABLE ch_s () INHERITS (par_s);
SELECT * FROM pg_get_table_ddl('ch_s', 'owner','false');
-- CREATE TABLE public.ch_s () INHERITS (public.par_s);
-- ALTER TABLE public.ch_s ALTER COLUMN g SET DEFAULT (id * 2);

Dropping ch_s, executing the returned statements:

ERROR: column "g" of relation "ch_s" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION instead.

3. named not null constraints can't be replayed:

CREATE TABLE t (a int CONSTRAINT my_nn NOT NULL);
SELECT * FROM pg_get_table_ddl('t'::regclass,'owner','false');
-- CREATE TABLE public.t ( a integer NOT NULL);
-- ALTER TABLE public.t ADD CONSTRAINT my_nn NOT NULL a;

Dropping t, executing the statements:

ERROR: cannot create not-null constraint "my_nn" on column "a" of table "t"
DETAIL: A not-null constraint named "t_a_not_null" already exists for this column.

Akshay Joshi

akshay.joshi@enterprisedb.com

15 days ago

In reply to: Zsolt Parragi (#4)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks to Zsolt and Marcos for the review.

Added *schema_qualified (boolean, default true)*. When false, the target
table is emitted unqualified everywhere (e.g., CREATE TABLE, ALTER TABLE,
INHERITS, PARTITION OF, identity SEQUENCE NAME, etc.); same-schema sibling
references follow suit; cross-schema references (e.g. FK targets in a
different schema) remain qualified for correctness. Output from the
always-qualified ruleutils helpers (pg_get_indexdef_string,
pg_get_constraintdef_command, pg_get_ruledef,
pg_get_statisticsobjdef_string) is post-processed to strip the base-schema
prefix.

1) CHECK constraints on partition children are now emitted as ALTER TABLE …
ADD CONSTRAINT … CHECK (…). They had been silently dropped because the
PARTITION OF form has no column list to inline them into.

2) Inherited generated columns no longer emit a spurious ALTER COLUMN … SET
DEFAULT, which would fail at replay.
3) User-named NOT NULL constraints are now emitted inline as CONSTRAINT
<name> NOT NULL. Auto-named NOT NULLs keep the existing inline-NOT NULL +
ALTER TABLE dedup behaviour so the common-case output is unchanged.
4) Added regression coverage for the three bug fixes plus the
schema_qualified=false paths (same-schema vs cross-schema,
INHERITS/PARTITION OF parents, custom identity sequence name, replay into a
different target schema)

Attached is the v3 patch, ready for review.

On Tue, Jun 9, 2026 at 2:42 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Show quoted text

Hello!

I did some basic testing with the new functions, and found a few bugs:

1. Seems like check constraints on partitions are ignored:

CREATE TABLE p (id int, val int) PARTITION BY RANGE (id);
CREATE TABLE p_child PARTITION OF p (CONSTRAINT chk_inline CHECK (val > 0))
FOR VALUES FROM (0) TO (100);
SELECT * FROM pg_get_table_ddl('p_child', 'owner','false');

2. inherited stored generated columns can't be replayed:

CREATE TABLE par_s (
id int,
g int GENERATED ALWAYS AS (id * 2) STORED
);
CREATE TABLE ch_s () INHERITS (par_s);
SELECT * FROM pg_get_table_ddl('ch_s', 'owner','false');
-- CREATE TABLE public.ch_s () INHERITS (public.par_s);
-- ALTER TABLE public.ch_s ALTER COLUMN g SET DEFAULT (id * 2);

Dropping ch_s, executing the returned statements:

ERROR: column "g" of relation "ch_s" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION instead.

3. named not null constraints can't be replayed:

CREATE TABLE t (a int CONSTRAINT my_nn NOT NULL);
SELECT * FROM pg_get_table_ddl('t'::regclass,'owner','false');
-- CREATE TABLE public.t ( a integer NOT NULL);
-- ALTER TABLE public.t ADD CONSTRAINT my_nn NOT NULL a;

Dropping t, executing the statements:

ERROR: cannot create not-null constraint "my_nn" on column "a" of table
"t"
DETAIL: A not-null constraint named "t_a_not_null" already exists for
this column.

Zsolt Parragi

zsolt.parragi@percona.com

14 days ago

In reply to: Akshay Joshi (#5)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks, I can confirm that the previous bugs were fixed, however the bugfixes also introduce a new issue, where inherited not null constraints are missing:

CREATE TABLE par (a int);
CREATE TABLE ch (b int) INHERITS (par);
ALTER TABLE ch ADD CONSTRAINT my_nn NOT NULL a;
SELECT * FROM pg_get_table_ddl('ch','owner','false');
-- CREATE TABLE public.ch ( b integer) INHERITS (public.par);

The schema_qualified => false part also doesn't work as described:

+        <command>CREATE STATISTICS</command> statement.  References to
+        objects in the same schema as the target table (inheritance
+        parents, partition parents, identity sequences, and any
+        same-schema object the deparse helpers happen to mention) are
+        also emitted unqualified, so the script can be replayed under a
+        different <varname>search_path</varname> to recreate the table
+        in another schema.

but:

CREATE SCHEMA s1;
CREATE SEQUENCE s1.myseq;
CREATE FUNCTION s1.f(int) RETURNS int LANGUAGE sql IMMUTABLE AS 'SELECT $1';
CREATE TABLE s1.t (
id int DEFAULT nextval('s1.myseq'),
val int,
CONSTRAINT chk CHECK (s1.f(val) > 0)
);
SELECT * FROM pg_get_table_ddl('s1.t', 'owner','false', 'schema_qualified','false');
-- CREATE TABLE t ( id integer DEFAULT nextval('s1.myseq'::regclass), val integer, CONSTRAINT chk CHECK ((s1.f(val) > 0)));

s1 appears twice in the output.

It also has an issue with strings containing the schema:

CREATE SCHEMA myschema;
CREATE TABLE myschema.p (id int, note text) PARTITION BY RANGE (id);
CREATE TABLE myschema.pc PARTITION OF myschema.p
(CONSTRAINT chk CHECK (note <> 'myschema.secret')) FOR VALUES FROM (0) TO (100);
SELECT * FROM pg_get_table_ddl('myschema.pc', 'owner','false', 'schema_qualified','false');
-- CREATE TABLE pc PARTITION OF p FOR VALUES FROM (0) TO (100);
-- ALTER TABLE pc ADD CONSTRAINT chk CHECK ((note <> 'secret'::text));

Akshay Joshi

akshay.joshi@enterprisedb.com

14 days ago

In reply to: Zsolt Parragi (#6)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks for the review. I have fixed all the issues you mentioned.

Because this is a major restructuring of the DDL function involving
multiple child elements and complex syntaxes, some edge cases might still
pop up, though I have tried to cover as much as possible.

The v4 patch is ready for your review.

On Wed, Jun 10, 2026 at 12:47 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Show quoted text

Thanks, I can confirm that the previous bugs were fixed, however the
bugfixes also introduce a new issue, where inherited not null
constraints are missing:

CREATE TABLE par (a int);
CREATE TABLE ch (b int) INHERITS (par);
ALTER TABLE ch ADD CONSTRAINT my_nn NOT NULL a;
SELECT * FROM pg_get_table_ddl('ch','owner','false');
-- CREATE TABLE public.ch ( b integer) INHERITS (public.par);

The schema_qualified => false part also doesn't work as described:
+        <command>CREATE STATISTICS</command> statement.  References to
+        objects in the same schema as the target table (inheritance
+        parents, partition parents, identity sequences, and any
+        same-schema object the deparse helpers happen to mention) are
+        also emitted unqualified, so the script can be replayed under a
+        different <varname>search_path</varname> to recreate the table
+        in another schema.
but:

CREATE SCHEMA s1;
CREATE SEQUENCE s1.myseq;
CREATE FUNCTION s1.f(int) RETURNS int LANGUAGE sql IMMUTABLE AS 'SELECT
$1';
CREATE TABLE s1.t (
id int DEFAULT nextval('s1.myseq'),
val int,
CONSTRAINT chk CHECK (s1.f(val) > 0)
);
SELECT * FROM pg_get_table_ddl('s1.t', 'owner','false',
'schema_qualified','false');
-- CREATE TABLE t ( id integer DEFAULT nextval('s1.myseq'::regclass),
val integer, CONSTRAINT chk CHECK ((s1.f(val) > 0)));

s1 appears twice in the output.

It also has an issue with strings containing the schema:

CREATE SCHEMA myschema;
CREATE TABLE myschema.p (id int, note text) PARTITION BY RANGE (id);
CREATE TABLE myschema.pc PARTITION OF myschema.p
(CONSTRAINT chk CHECK (note <> 'myschema.secret')) FOR VALUES FROM
(0) TO (100);
SELECT * FROM pg_get_table_ddl('myschema.pc', 'owner','false',
'schema_qualified','false');
-- CREATE TABLE pc PARTITION OF p FOR VALUES FROM (0) TO (100);
-- ALTER TABLE pc ADD CONSTRAINT chk CHECK ((note <> 'secret'::text));

Zsolt Parragi

zsolt.parragi@percona.com

13 days ago

In reply to: Akshay Joshi (#7)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks for the update! The new version looks mostly good, I only found one corner case that doesn't work, double quoted literals can still get over-stripped:

CREATE SCHEMA s;
CREATE TABLE s.p (id int, "s.weird" int) PARTITION BY RANGE (id);
CREATE TABLE s.pc PARTITION OF s.p
(CONSTRAINT chk CHECK ("s.weird" > 0)) FOR VALUES FROM (0) TO (100);
SELECT * FROM pg_get_table_ddl('s.pc', 'owner', 'false',
'schema_qualified', 'false');

Akshay Joshi

akshay.joshi@enterprisedb.com

13 days ago

In reply to: Zsolt Parragi (#8)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

On Thu, Jun 11, 2026 at 2:13 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Thanks for the update! The new version looks mostly good, I only found
one corner case that doesn't work, double quoted literals can still
get over-stripped:

CREATE SCHEMA s;
CREATE TABLE s.p (id int, "s.weird" int) PARTITION BY RANGE (id);
CREATE TABLE s.pc PARTITION OF s.p
(CONSTRAINT chk CHECK ("s.weird" > 0)) FOR VALUES FROM (0) TO (100);
SELECT * FROM pg_get_table_ddl('s.pc', 'owner', 'false',
'schema_qualified', 'false');

Fixed the issue above. The v5 patch is ready for review/testing.

#10

Marcos Pegoraro

marcos@f10.com.br

13 days ago

In reply to: Akshay Joshi (#9)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Em qui., 11 de jun. de 2026 às 04:48, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

Fixed the issue above. The v5 patch is ready for review/testing.

One thing I noticed, though I'm not sure if it's the point here, is that
it's not possible to extract only the foreign keys or only the triggers
from the table. So if we want to extract the objects independently by type,
we would need to have all the return types as optional, and we could have
more granularity in the return types.

Just like you have...
if (!ctx->include_indexes)

You could have too
+ if (!ctx->include_create_table)
+ if (!ctx->include_foreign_keys)
+ if (!ctx->include_primary_keys)

Because only in this way can we more or less execute the dump behavior
here, which is to create all the tables beforehand, then primary keys, then
foreign keys, then triggers.

I repeat, sorry if this is not the function's intended purpose.

regards
Marcos

#11

Zsolt Parragi

zsolt.parragi@percona.com

12 days ago

In reply to: Akshay Joshi (#9)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

The new version seem to work correctly to me, I didn't find any further issues. Now the main blockers seem to be the remaining TODOs related to includes_triggers/includes_policies.

I only have some minor comments about code structuring:

+ * get_inheritance_parents
+ *		Return a List of parent OIDs for relid, ordered by inhseqno.
+ *
+ * find_inheritance_children() walks the opposite direction (parent->children),

Shouldn't this follow the same naming and parameter pattern and live at the same place in pg_inherits?

+static char *
+lookup_qualified_relname(Oid relid)
+...
+static char *
+lookup_relname_for_emit(Oid relid, bool schema_qualified, Oid base_namespace)

Is lookup_qualified_relname needed? It is only called within lookup_relname_for_emit, and it results in a double syscache lookup, which could be avoided if these were a single function.

+		/* COMPRESSION clause, only if explicitly set on the column. */
+		if (CompressionMethodIsValid(att->attcompression))
+		{
+			const char *cm = NULL;
+
+			switch (att->attcompression)
+			{
+				case TOAST_PGLZ_COMPRESSION:
+					cm = "pglz";
+					break;
+				case TOAST_LZ4_COMPRESSION:
+					cm = "lz4";
+					break;
+			}
+			if (cm)
+				appendStringInfo(buf, " COMPRESSION %s", cm);
+		}

Isn't this basically GetCompressionMethodName(att->attcompression)?

+		/* STORAGE clause, only if it differs from the type's default. */
+		if (att->attstorage != get_typstorage(att->atttypid))
+		{
+			const char *storage = NULL;
+
+			switch (att->attstorage)
+			{
+				case TYPSTORAGE_PLAIN:
+...

And this seems like storage_name(att->attstorage) from tablecmds.c, the only issue is that that's currently static

#12

Kyotaro Horiguchi

horikyota.ntt@gmail.com

12 days ago

In reply to: Akshay Joshi (#9)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Hello.

At Thu, 11 Jun 2026 13:18:07 +0530, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote in

Fixed the issue above. The v5 patch is ready for review/testing.

I have not looked at the patch in detail, but I noticed that some
comments in the patch seem to contain non-ASCII characters.

* * re-emitting it as ALTER TABLE … ADD CONSTRAINT — the column-emit

I don't think that is recommended in PostgreSQL source comments, so
these should probably be replaced with plain ASCII equivalents.

/messages/by-id/E1pnhhu-003D6z-Ki@gemulon.postgresql.org

For reference, I have attached the result of a quick search below.

Regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

Quick search result:

=========
20 matches in 18 lines for "[^[:ascii:]]" in buffer: v5-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch
565:+ * re-emitting it as ALTER TABLE … ADD CONSTRAINT — the column-emit
877:+ * defaults — mirroring pg_get_database_ddl's pattern of
934:+ * SEQUENCE NAME — omit when it matches the implicit
1042:+ * Table-level CHECK constraints — emitted inline in the CREATE TABLE
1055:+ * applied per-column overrides — DEFAULT, NOT NULL, and any locally
1155:+ * pg_get_ruledef, pg_get_statisticsobjdef_string) — when
1383:+ * ALTER TABLE qualname ALTER COLUMN col SET DEFAULT expr — one per
1422:+ * ALTER TABLE qualname ALTER COLUMN col SET (...) — one per column
1468:+ * out-of-line by emit_local_constraints (the ALTER TABLE … ADD
1500:+ * ALTER TABLE … ADD CONSTRAINT for each locally-defined constraint
1617:+ * ALTER TABLE qualname REPLICA IDENTITY … — emitted only when the
1749:+ * (#if 0) — they will become a single helper call once the standalone
1830:+ * Pre-compute "<schema>." too — the always-qualified helpers
1884:+ * Triggers and row-level security policies — disabled until the
2680:+-- quoting (its prefix starts with "). Both forms of the prefix —
2681:+-- bare-lowercase and quoted — must be stripped from outer
3197:+-- quoting (its prefix starts with "). Both forms of the prefix —
3198:+-- bare-lowercase and quoted — must be stripped from outer
=========

#13

Akshay Joshi

akshay.joshi@enterprisedb.com

9 days ago

In reply to: Marcos Pegoraro (#10)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

On Thu, Jun 11, 2026 at 6:38 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

Em qui., 11 de jun. de 2026 às 04:48, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

Fixed the issue above. The v5 patch is ready for review/testing.

One thing I noticed, though I'm not sure if it's the point here, is that
it's not possible to extract only the foreign keys or only the triggers
from the table. So if we want to extract the objects independently by type,
we would need to have all the return types as optional, and we could have
more granularity in the return types.

Just like you have...
if (!ctx->include_indexes)
You could have too
+ if (!ctx->include_create_table)
+ if (!ctx->include_foreign_keys)
+ if (!ctx->include_primary_keys)
Because only in this way can we more or less execute the dump behavior
here, which is to create all the tables beforehand, then primary keys, then
foreign keys, then triggers.

I repeat, sorry if this is not the function's intended purpose.

I don't think per-contype flags are the right shape, though. The existing
toggles group by catalog (indexes, constraints, rules, ...); splitting
constraints into PK/FK/CHECK/UNIQUE/EXCLUDE/NOT NULL adds six flags on a
second axis, and the function already carries nine. Only FKs have the
cross-table dependency-ordering problem; the rest only reference the same
table, so splitting them unlocks nothing new.

On include_create_table, we are reconstructing the DDL for the table
itself, so I don't think we should skip the CREATE TABLE statement. I'd
rather always emit CREATE TABLE.

Show quoted text

regards
Marcos

#14

Akshay Joshi

akshay.joshi@enterprisedb.com

9 days ago

In reply to: Kyotaro Horiguchi (#12)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Hi Kyotaro and Zsolt,

I have incorporated the feedback provided by both of you.
The v6 patch is updated and ready for your review.

On Fri, Jun 12, 2026 at 6:40 AM Kyotaro Horiguchi <horikyota.ntt@gmail.com>
wrote:

Show quoted text

Hello.

At Thu, 11 Jun 2026 13:18:07 +0530, Akshay Joshi <
akshay.joshi@enterprisedb.com> wrote in

Fixed the issue above. The v5 patch is ready for review/testing.

I have not looked at the patch in detail, but I noticed that some
comments in the patch seem to contain non-ASCII characters.

* * re-emitting it as ALTER TABLE … ADD CONSTRAINT ― the column-emit

I don't think that is recommended in PostgreSQL source comments, so
these should probably be replaced with plain ASCII equivalents.

/messages/by-id/E1pnhhu-003D6z-Ki@gemulon.postgresql.org

For reference, I have attached the result of a quick search below.

Regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

Quick search result:

=========
20 matches in 18 lines for "[^[:ascii:]]" in buffer:
v5-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch
565:+ * re-emitting it as ALTER TABLE … ADD CONSTRAINT ― the
column-emit
877:+ * defaults ― mirroring
pg_get_database_ddl's pattern of
934:+ * SEQUENCE NAME ―
omit when it matches the implicit
1042:+ * Table-level CHECK constraints ― emitted inline in the
CREATE TABLE
1055:+ * applied per-column overrides ― DEFAULT, NOT NULL,
and any locally
1155:+ * pg_get_ruledef, pg_get_statisticsobjdef_string) ― when
1383:+ * ALTER TABLE qualname ALTER COLUMN col SET DEFAULT
expr ― one per
1422:+ * ALTER TABLE qualname ALTER COLUMN col SET (...) ―
one per column
1468:+ * out-of-line by emit_local_constraints (the ALTER
TABLE … ADD
1500:+ * ALTER TABLE … ADD CONSTRAINT for each
locally-defined constraint
1617:+ * ALTER TABLE qualname REPLICA IDENTITY … ― emitted
only when the
1749:+ * (#if 0) ― they will become a single helper call once the
standalone
1830:+ * Pre-compute "<schema>." too ― the always-qualified
helpers
1884:+ * Triggers and row-level security policies ― disabled
until the
2680:+-- quoting (its prefix starts with "). Both forms of the prefix ―
2681:+-- bare-lowercase and quoted ― must be stripped from outer
3197:+-- quoting (its prefix starts with "). Both forms of the prefix ―
3198:+-- bare-lowercase and quoted ― must be stripped from outer
=========

#15

Marcos Pegoraro

marcos@f10.com.br

8 days ago

In reply to: Akshay Joshi (#13)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Em seg., 15 de jun. de 2026 às 04:52, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

I don't think per-contype flags are the right shape, though. The existing
toggles group by catalog (indexes, constraints, rules, ...); splitting
constraints into PK/FK/CHECK/UNIQUE/EXCLUDE/NOT NULL adds six flags on a
second axis, and the function already carries nine. Only FKs have the
cross-table dependency-ordering problem; the rest only reference the same
table, so splitting them unlocks nothing new.

Ok, I understand your point. Initially, I saw the usefulness of this
function for cloning a schema, something very common in a multi-tenant
model. But creating the foreign keys along with the create table makes that
unfeasible.

Options are variadic, so you could split your emit_local_constraints into
+emit_local_foreign_keys_constraints(TableDdlContext * ctx)
+ if (!(ctx->include_constraints || ctx->include_foreign_keys)) then
+   return

+emit_local_primary_keys_constraints(TableDdlContext * ctx)
+ if (!(ctx->include_constraints || ctx->include_primary_keys)) then
+   return

pg_get_table_ddl('x','includes_constraints','true') -- would print all
constraints
pg_get_table_ddl('x','include_primary_keys','true') -- would print only
primary key constraints

regards
Marcos

#16

Akshay Joshi

akshay.joshi@enterprisedb.com

5 days ago

In reply to: Marcos Pegoraro (#15)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

On Tue, Jun 16, 2026 at 2:35 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

Em seg., 15 de jun. de 2026 às 04:52, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

I don't think per-contype flags are the right shape, though. The existing
toggles group by catalog (indexes, constraints, rules, ...); splitting
constraints into PK/FK/CHECK/UNIQUE/EXCLUDE/NOT NULL adds six flags on a
second axis, and the function already carries nine. Only FKs have the
cross-table dependency-ordering problem; the rest only reference the same
table, so splitting them unlocks nothing new.

Ok, I understand your point. Initially, I saw the usefulness of this
function for cloning a schema, something very common in a multi-tenant
model. But creating the foreign keys along with the create table makes that
unfeasible.
Options are variadic, so you could split your emit_local_constraints into
+emit_local_foreign_keys_constraints(TableDdlContext * ctx)
+ if (!(ctx->include_constraints || ctx->include_foreign_keys)) then
+   return
+emit_local_primary_keys_constraints(TableDdlContext * ctx)
+ if (!(ctx->include_constraints || ctx->include_primary_keys)) then
+   return
pg_get_table_ddl('x','includes_constraints','true') -- would print all
constraints
pg_get_table_ddl('x','include_primary_keys','true') -- would print only
primary key constraints

The schema cloning use case is valid. The v7 patch (attached) adds a single
new option, `includes_foreign_keys` (boolean, default true), which acts as
an additive gate underneath `includes_constraints`. Calling
pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits everything
except FOREIGN KEY constraints. This covers the multi-tenant clone
workflow: create tables first without cross-table references, then re-run
with the default to add the constraints once all targets exist.

I held off on the broader split into per-contype options
(includes_primary_keys, includes_unique, etc.) for two reasons. First, only
FOREIGN KEY actually breaks schema cloning. PRIMARY KEY, UNIQUE, CHECK,
EXCLUDE, and named NOT NULL constraints are all table local and don't
reference anything else, so no workflow currently needs to suppress them
independently. Second, the function already exposes thirteen boolean
options; adding five more granular ones without a concrete use case expands
the surface area unnecessarily.

The v7 patch is ready for review.

Show quoted text

regards
Marcos

#17

Zsolt Parragi

zsolt.parragi@percona.com

4 days ago

In reply to: Akshay Joshi (#16)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

The previous features all look good to me, I only have one question for the new flag.

Calling
pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits everything
except FOREIGN KEY constraints. This covers the multi-tenant clone
workflow: create tables first without cross-table references, then re-run
with the default to add the constraints once all targets exist.

I think this feature needs a bit more documentation, an "only_foreign_keys" flag, or both.

CREATE TABLE refd (id int PRIMARY KEY);
CREATE TABLE cons (a int CHECK(a>0), b int UNIQUE, c int REFERENCES refd(id));

-- pass 1: running without foreign keys
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','false');
-- execute everything

-- loading data

-- pass 2: running with everything
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','true');
-- ERROR: relation "cons" already exists (and the unique constraint also collides)

I could do a "grep FOREIGN KEY" before executing (unless it's a tricky schema where that phrase appears elsewhere), or since psql continues on error, it will simply work if I accept a significant error noise, but then the documentation should be clear about this limitation. Following the documented approach and getting a bunch of unexpected errors could be confusing for users.

#18

Akshay Joshi

akshay.joshi@enterprisedb.com

2 days ago

In reply to: Zsolt Parragi (#17)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks for the review; you're right, `includes_foreign_keys=false` on its
own is a half-measure. Re-running with the default to add FKs back collides
with the existing CREATE TABLE, UNIQUE indexes, etc.

I've added an only_foreign_keys option (boolean, default false) as the
natural complement of includes_foreign_keys=false. When set to true, the
function emits only the ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY
statements and suppresses everything else (CREATE TABLE, owner, indexes,
non-FK constraints, rules, statistics, replica identity, RLS toggles).
Partition-child recursion still runs so child FKs are reached too.
Combining `only_foreign_keys=true` with `includes_foreign_keys=false` is
rejected upfront since it would produce no output.

The documentation paragraph for `includes_foreign_keys` now directs users
to `only_foreign_keys` as the intended second pass. Regression coverage
adds three cases: the FK-only emission for your cons example, the zero-row
result for a table without FKs, and the error path.

The v8 patch is ready for review.

On Sat, Jun 20, 2026 at 1:15 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Show quoted text

The previous features all look good to me, I only have one question
for the new flag.

Calling
pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits

everything

except FOREIGN KEY constraints. This covers the multi-tenant clone
workflow: create tables first without cross-table references, then re-run
with the default to add the constraints once all targets exist.

I think this feature needs a bit more documentation, an
"only_foreign_keys" flag, or both.

CREATE TABLE refd (id int PRIMARY KEY);
CREATE TABLE cons (a int CHECK(a>0), b int UNIQUE, c int REFERENCES
refd(id));

-- pass 1: running without foreign keys
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','false');
-- execute everything

-- loading data

-- pass 2: running with everything
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','true');
-- ERROR: relation "cons" already exists (and the unique constraint
also collides)

I could do a "grep FOREIGN KEY" before executing (unless it's a tricky
schema where that phrase appears elsewhere), or since psql continues
on error, it will simply work if I accept a significant error noise,
but then the documentation should be clear about this limitation.
Following the documented approach and getting a bunch of unexpected
errors could be confusing for users.

#19

Chao Li

li.evan.chao@gmail.com

2 days ago

In reply to: Akshay Joshi (#18)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

On Jun 22, 2026, at 14:26, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Thanks for the review; you're right, `includes_foreign_keys=false` on its own is a half-measure. Re-running with the default to add FKs back collides with the existing CREATE TABLE, UNIQUE indexes, etc.

I've added an only_foreign_keys option (boolean, default false) as the natural complement of includes_foreign_keys=false. When set to true, the function emits only the ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statements and suppresses everything else (CREATE TABLE, owner, indexes, non-FK constraints, rules, statistics, replica identity, RLS toggles). Partition-child recursion still runs so child FKs are reached too. Combining `only_foreign_keys=true` with `includes_foreign_keys=false` is rejected upfront since it would produce no output.

The documentation paragraph for `includes_foreign_keys` now directs users to `only_foreign_keys` as the intended second pass. Regression coverage adds three cases: the FK-only emission for your cons example, the zero-row result for a table without FKs, and the error path.

The v8 patch is ready for review.

On Sat, Jun 20, 2026 at 1:15 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
The previous features all look good to me, I only have one question
for the new flag.

Calling
pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits everything
except FOREIGN KEY constraints. This covers the multi-tenant clone
workflow: create tables first without cross-table references, then re-run
with the default to add the constraints once all targets exist.

I think this feature needs a bit more documentation, an
"only_foreign_keys" flag, or both.

CREATE TABLE refd (id int PRIMARY KEY);
CREATE TABLE cons (a int CHECK(a>0), b int UNIQUE, c int REFERENCES refd(id));

-- pass 1: running without foreign keys
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','false');
-- execute everything

-- loading data

-- pass 2: running with everything
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','true');
-- ERROR: relation "cons" already exists (and the unique constraint
also collides)

I could do a "grep FOREIGN KEY" before executing (unless it's a tricky
schema where that phrase appears elsewhere), or since psql continues
on error, it will simply work if I accept a significant error noise,
but then the documentation should be clear about this limitation.
Following the documented approach and getting a bunch of unexpected
errors could be confusing for users.

<v8-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch>

I have a comment, or maybe a question:
```
+{ oid => '8215', descr => 'get DDL to recreate a table',
+  proname => 'pg_get_table_ddl', prorows => '50', provariadic => 'text',
+  proisstrict => 'f', proretset => 't', provolatile => 's', proparallel => 'r',
+  pronargdefaults => '1', prorettype => 'text',
+  proargtypes => 'regclass text', proallargtypes => '{regclass,text}',
+  proargmodes => '{i,v}', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_table_ddl' },
```

Since provariadic is text, I wonder if proallargtypes should be {regclass,_text}, with _text meaning an array of text.

I’m asking because I have had this suspicion for some time. I saw a few other procs using the same pattern, for example:
```
{ oid => '6501', descr => 'get DDL to recreate a role',
proname => 'pg_get_role_ddl', prorows => '10', provariadic => 'text',
proisstrict => 'f', proretset => 't', provolatile => 's',
pronargdefaults => '1', prorettype => 'text', proargtypes => 'regrole text',
proallargtypes => '{regrole,text}', proargmodes => '{i,v}',
proargdefaults => '{NULL}', prosrc => 'pg_get_role_ddl' },
```

But for jsonb_delete etc procs, _text is used:
```
{ oid => '3343',
proname => 'jsonb_delete', provariadic => 'text', prorettype => 'jsonb',
proargtypes => 'jsonb _text', proallargtypes => '{jsonb,_text}',
proargmodes => '{i,v}', proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_delete_array' },
```

So I wonder whether “text” rather than “_text" is intentionally used in proallargtypes, or if this was just never noticed.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

#20

Akshay Joshi

akshay.joshi@enterprisedb.com

1 day ago

In reply to: Chao Li (#19)

Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

You're right, and thanks for spotting this. The existing pattern in
pg_proc.dat for variadic-text functions (e.g., jsonb_delete,
json_extract_path) uses _text at the variadic position in both proargtypes
and proallargtypes, with provariadic => 'text'. That is the convention
documented by the sanity check in src/test/regress/sql/opr_sanity.sql.

The same issue applies to *pg_get_role_ddl*, *pg_get_tablespace_ddl* (both
variants), and *pg_get_database_ddl*, but that will require a separate
patch.

The v9 patch is ready for review.

On Mon, Jun 22, 2026 at 12:25 PM Chao Li <li.evan.chao@gmail.com> wrote:

Show quoted text

On Jun 22, 2026, at 14:26, Akshay Joshi <akshay.joshi@enterprisedb.com>

wrote:

Thanks for the review; you're right, `includes_foreign_keys=false` on

its own is a half-measure. Re-running with the default to add FKs back
collides with the existing CREATE TABLE, UNIQUE indexes, etc.

I've added an only_foreign_keys option (boolean, default false) as the

natural complement of includes_foreign_keys=false. When set to true, the
function emits only the ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY
statements and suppresses everything else (CREATE TABLE, owner, indexes,
non-FK constraints, rules, statistics, replica identity, RLS toggles).
Partition-child recursion still runs so child FKs are reached too.
Combining `only_foreign_keys=true` with `includes_foreign_keys=false` is
rejected upfront since it would produce no output.

The documentation paragraph for `includes_foreign_keys` now directs

users to `only_foreign_keys` as the intended second pass. Regression
coverage adds three cases: the FK-only emission for your cons example, the
zero-row result for a table without FKs, and the error path.

The v8 patch is ready for review.

On Sat, Jun 20, 2026 at 1:15 AM Zsolt Parragi <zsolt.parragi@percona.com>

wrote:

The previous features all look good to me, I only have one question
for the new flag.

Calling
pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits

everything

except FOREIGN KEY constraints. This covers the multi-tenant clone
workflow: create tables first without cross-table references, then

re-run

with the default to add the constraints once all targets exist.

I think this feature needs a bit more documentation, an
"only_foreign_keys" flag, or both.

CREATE TABLE refd (id int PRIMARY KEY);
CREATE TABLE cons (a int CHECK(a>0), b int UNIQUE, c int REFERENCES

refd(id));

-- pass 1: running without foreign keys
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','false');
-- execute everything

-- loading data

-- pass 2: running with everything
SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','true');
-- ERROR: relation "cons" already exists (and the unique constraint
also collides)

I could do a "grep FOREIGN KEY" before executing (unless it's a tricky
schema where that phrase appears elsewhere), or since psql continues
on error, it will simply work if I accept a significant error noise,
but then the documentation should be clear about this limitation.
Following the documented approach and getting a bunch of unexpected
errors could be confusing for users.

<v8-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch>
I have a comment, or maybe a question:
```
+{ oid => '8215', descr => 'get DDL to recreate a table',
+  proname => 'pg_get_table_ddl', prorows => '50', provariadic => 'text',
+  proisstrict => 'f', proretset => 't', provolatile => 's', proparallel
=> 'r',
+  pronargdefaults => '1', prorettype => 'text',
+  proargtypes => 'regclass text', proallargtypes => '{regclass,text}',
+  proargmodes => '{i,v}', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_table_ddl' },
```
Since provariadic is text, I wonder if proallargtypes should be
{regclass,_text}, with _text meaning an array of text.

I’m asking because I have had this suspicion for some time. I saw a few
other procs using the same pattern, for example:
```
{ oid => '6501', descr => 'get DDL to recreate a role',
proname => 'pg_get_role_ddl', prorows => '10', provariadic => 'text',
proisstrict => 'f', proretset => 't', provolatile => 's',
pronargdefaults => '1', prorettype => 'text', proargtypes => 'regrole
text',
proallargtypes => '{regrole,text}', proargmodes => '{i,v}',
proargdefaults => '{NULL}', prosrc => 'pg_get_role_ddl' },
```

But for jsonb_delete etc procs, _text is used:
```
{ oid => '3343',
proname => 'jsonb_delete', provariadic => 'text', prorettype => 'jsonb',
proargtypes => 'jsonb _text', proallargtypes => '{jsonb,_text}',
proargmodes => '{i,v}', proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_delete_array' },
```

So I wonder whether “text” rather than “_text" is intentionally used in
proallargtypes, or if this was just never noticed.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/