From: Zsolt Parragi <zsolt.parragi@percona.com>
In-Reply-To: 
 <CANxoLDfL1AxL=k9SsRNReKQ-sJUSy6RvCqeog5HWckFxff=0Pg@mail.gmail.com>
References: 
 <CANxoLDfjQnhM=E6JSyYo9s9OdjqoN8s_3wE5yL=kaDu_X8j-dA@mail.gmail.com>
 <CANxoLDcxmXNHNyPt5v+LaBJVJmyt=j95D7TDE9SepoM7y15t-w@mail.gmail.com>
 <CAN4CZFNqf41TCg1ENccq6DAYQpArCbnKUSidHnRKaVMWGj5pBA@mail.gmail.com>
 <CANxoLDfL1AxL=k9SsRNReKQ-sJUSy6RvCqeog5HWckFxff=0Pg@mail.gmail.com>
MIME-Version: 1.0
Date: Tue, 9 Jun 2026 14:17:11 -0500
Message-ID: 
 <CAN4CZFP5-fAGwxMCyKGePgp3N8NCpoFXkruO=5xjTC=gPwqYxw@mail.gmail.com>
Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE
 statements
To: pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN4CZFP5-fAGwxMCyKGePgp3N8NCpoFXkruO%3D5xjTC%3DgPwqYxw%40mail.gmail.com>
Precedence: bulk

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