problem with foreign keys + data-only backup - Mailing list pgsql-general

Hi all,

I have a problem with foreign keys and data-only (no schema) backup. I
have a simple table node (pseudo-SQL):

node
(
    integer node_id            NOT NULL    PRIMARY KEY;
    integer parent_node_id     NULL;
)

It contains the following two entries:

node(1, NULL)      the rood
node(2, 1)         a child of the root

When I do a data-only backup, the backup file contains following two lines:

INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1);
INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL);

Restoring the backup file into another database ofcourse fails, because
the parent_node_id (1) in the first INSERT statement refers to an
unknown (to be added) node (in the second statement).

How do I make sure my backup orders the insert statements in a logical
order?

This is how I make the backup:

pg_dump.exe -h ... -p 5432 -U ... --column-inserts --ignore-version
--file=dump --format=t --data-only --verbose db

This is how I import the backup:

pg_restore.exe -h .... -p 5432 -U ... --dbname db --format=t --verbose
--table=channel dump

Thanks all. Kind regards,

Peter