Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following


I get,

ERROR: constraints on temporary tables may reference only temporary tables

I thought this used to work, so I went back to PostgreSQL 8.4 and I see even then it used to error,

ERROR: cannot reference permanent table from temporary table constraint

Why is this though? Why can't a temporary table reference a non-temporary table.


There are two sides to a foreign key constraint:

  • When inserting a child record (or updating its foreign key), the parent record must exist
  • When deleting a parent record (or updating its primary key), a child record must not exist

Postgres implements this by installing triggers on both tables to perform the existence checks. But the parent trigger simply won't work if the child is a temp table: the trigger needs to fire for all database users in order to guarantee referential integrity, but the temp table data is only visible within the session which created it, so the constraint cannot be enforced.

Even if it were possible, it's probably a bit too intrusive; it's rarely desirable to have a DELETE on the parent fail just because some other user is referencing it from a temporary dataset. But if you really need to maintain integrity in this case, you can get much the same effect by locking the parent records with a SELECT ... FOR KEY SHARE.

  • And is there a way to enforce the first side? When inserting a child record (or updating its foreign key), the parent record must exist That concerns only the child (temporary) table, right? – Adam Sep 26 '19 at 20:24
  • @Adam: Yes, as I mentioned, you can check for the existence of the parent, and guard against its deletion until your transaction commits, by querying it with a SELECT ... FOR KEY SHARE. You could either run this yourself before inserting to your temp table, or create a trigger on your temp table to perform the check automatically. – Nick Barnes Sep 26 '19 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.