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(dot)parragi(at)percona(dot)com>
wrote:

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