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