Re: BUG #15316: pg_restore failes to load data that is constrainted by a functional check constraints

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
> I have found an edge case where pg_dump and pg_restore fails to restore
> databases with check constraints that points to data in another table that
> is restored after the table containing the check constraint.

This is an unsupported case.[1] In general, table CHECK constraints are
only allowed to concern themselves with the data in the particular row
that they're checking. While we can't actually stop you from cheating by
hiding the subquery in a function, we're not going to consider it a bug
that such a setup doesn't work reliably.

I'd suggest rethinking your data representation so that you can make the
tables' relationship into a regular foreign key constraint. Those, the
system understands well enough to maintain in a dump/reload context.

regards, tom lane

[1] Specifically, we disclaim support for SQL feature F671, "Subqueries
in CHECK constraints".