Create parent table before child table which references parent table

[hazardland]

table_child references table_parent with foreign key but table_child is created first and then table_parent

How to repoduce:

Source database:

DROP SCHEMA IF EXISTS schema1 CASCADE;
CREATE SCHEMA schema1;
CREATE TABLE schema1.b_parent_table (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE schema1.a_child_table (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    parent_id BIGINT REFERENCES schema1.b_parent_table(id)
);

Target database:

DROP SCHEMA IF EXISTS schema1 CASCADE;
CREATE SCHEMA schema1;

Output:

--
-- pgquarrel 0.7.0
-- quarrel between 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) and 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
--

CREATE SEQUENCE schema1.a_child_table_id_seq NO MINVALUE NO MAXVALUE;

CREATE SEQUENCE schema1.b_parent_table_id_seq NO MINVALUE NO MAXVALUE;

CREATE TABLE schema1.a_child_table (
id bigint DEFAULT nextval('schema1.a_child_table_id_seq'::regclass) NOT NULL,
name text,
parent_id bigint
);

ALTER TABLE ONLY schema1.a_child_table
        ADD CONSTRAINT a_child_table_pkey PRIMARY KEY (id);

-- HERE b_parent_table TABLE DOES NOT EXIST YET
ALTER TABLE ONLY schema1.a_child_table
        ADD CONSTRAINT a_child_table_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES schema1.b_parent_table(id);

ALTER SEQUENCE schema1.a_child_table_id_seq OWNED BY schema1.a_child_table.id;

CREATE TABLE schema1.b_parent_table (
id bigint DEFAULT nextval('schema1.b_parent_table_id_seq'::regclass) NOT NULL,
name text
);

ALTER TABLE ONLY schema1.b_parent_table
        ADD CONSTRAINT b_parent_table_pkey PRIMARY KEY (id);

ALTER SEQUENCE schema1.b_parent_table_id_seq OWNED BY schema1.b_parent_table.id;

As you see at the moment of ADD CONSTRAINT a_child_table_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES schema1.b_parent_table(id); the table schema1.b_parent_table(id) does not exist yet

[eulerto]

@hazardland pgquarrel does not handle dependencies accordingly. It uses a natural order of object classes to build the diff output. There are some issues that refers to this same issue #49 #52 #68 #69. Unfortunately the workaround is to test your script and reorder the commands if necessary. This feature is already in my roadmap.

[hazardland]

@eulerto I just moved foreign key writes into the post file and that fixes it fully

[eulerto]

Commit 39b02d392262a58c579176cce399f8be91d0113a.