[GENERAL] problem with foreign keys + data-only backup

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general