COPY documentation with regard to references constraints
I was unable to find any documentation clarifying if using COPY to load a
table with rows referencing others rows in the same table, whether I
had to worry about ordering of the input.
What I found (in 17) is that even if the references constraint wasn't
set as deferrable, I could have rows for children before their parents
in the COPY data and things still worked.
On Thu, Oct 31, 2024 at 1:04 PM Bruno Wolff III <bruno@wolff.to> wrote:
I was unable to find any documentation clarifying if using COPY to load a
table with rows referencing others rows in the same table, whether I
had to worry about ordering of the input.
What I found (in 17) is that even if the references constraint wasn't
set as deferrable, I could have rows for children before their parents
in the COPY data and things still worked.
AFAIK, DEFERRABLE is for temporary violations crossing statement boundaries,
to postpone the enforcement of FKs at COMMIT time of the transaction.
While a single COPY is just one statement, so whether a temporary violation
for self-referential FKs would occur would be implementation-dependent,
and thus "neither logical" nor predictable.
Applies to other statement types too.
But of course, more authoritative answers will come I'm sure. --DD
On Thu, Oct 31, 2024 at 13:15:34 +0100,
Dominique Devienne <ddevienne@gmail.com> wrote:
AFAIK, DEFERRABLE is for temporary violations crossing statement boundaries,
to postpone the enforcement of FKs at COMMIT time of the transaction.While a single COPY is just one statement, so whether a temporary violation
for self-referential FKs would occur would be implementation-dependent,
and thus "neither logical" nor predictable.Applies to other statement types too.
Thanks. I needed to look more carefully under deferrable where it says
the following:
This controls whether the constraint can be deferred. A constraint that is
not deferrable will be checked immediately after every command.
That is pretty clear.
On Thu, 2024-10-31 at 06:38 -0500, Bruno Wolff III wrote:
I was unable to find any documentation clarifying if using COPY to load a
table with rows referencing others rows in the same table, whether I
had to worry about ordering of the input.
I cannot find it in the documentation either, but since COPY is a single
statement and foreign key constraints are checked at the end of the
statement, it should work fine.
Yours,
Laurenz Albe