Joel Krajden <joelk(at)cs(dot)concordia(dot)ca> writes:
> But if I create the tables as a mortal user or create them as postgres
> but in the schema of user joelk and grant all to user joelk, I can
> insert data without the foreign key constraint being respected. Now if
> I drop the foreign key constraint and recreate it with a schema prefix
> in the references section, the constarint works fine.
This is even harder to believe than the first report. Could we see a
complete, self-contained test case? A SQL script that demonstrates the
problem from a standing start in an empty database is what I have in mind.
(What I suspect is that you have multiple similarly-named tables in
different schemas and are getting confused by that...)
regards, tom lane