[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:

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

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.

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:

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));

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:

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');

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.

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

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

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

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.

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:

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