I did some more testing, I noticed one more issue with self referencing foreign keys:
CREATE TABLE t (id int PRIMARY KEY, parent_id int REFERENCES t(id)); SELECT pg_get_table_ddl('t'::regclass); -- CREATE TABLE public.t (id integer NOT NULL, parent_id integer); -- ALTER TABLE public.t OWNER TO postgres; -- ALTER TABLE public.t ADD CONSTRAINT t_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES public.t(id); -- ALTER TABLE public.t ADD CONSTRAINT t_pkey PRIMARY KEY (id);
It tries to add the foreign key before the primary, and fails with `ERROR: there is no unique constraint matching given keys for referenced table "t"`
There's also another issue in schema_qualified false, with partitions in different schemas:
CREATE SCHEMA s; CREATE SCHEMA other; CREATE TABLE s.pt (id int, val int) PARTITION BY RANGE (id); CREATE TABLE other.pt_c PARTITION OF s.pt FOR VALUES FROM (0) TO (100); SELECT pg_get_table_ddl('s.pt'::regclass, schema_qualified => false); -- CREATE TABLE pt (id integer, val integer) PARTITION BY RANGE (id); -- ALTER TABLE pt OWNER TO postgres; -- CREATE TABLE pt_c PARTITION OF s.pt FOR VALUES FROM (0) TO (100); -- ALTER TABLE pt_c OWNER TO postgres;
The second create table statement references pt as s.pt, which seems incorrect. It is also missing its own schema qualification, which I'm unsure if it is wrong or not. If I interpret the documentation strictly, it isn't the target table, so it should appear with its schema qualification?
