Postgresql – Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper

foreign keypostgresqlpostgresql-fdwremote

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server.

More specific details:

I only really need it as an annotation because some tools look that up for JOINs.

I have two tables on a remote server that I've added to my local using postgresql_fdw's CREATE FOREIGN TABLE. These I'm using in PostgREST, an automatic API server uses the REFERENCES information in order to make joins.

Actually, I'm further making two additional CREATE MATERIALIZED VIEW to make the lookups fast – but sadly, they too don't have any way I know of to add REFERENCES information?

I'm open to other ways of making (read-only) lookups fast for these tables on the foreign API server and where Foreign Key constraints will work.

Best Answer

The PostgREST team gave a potential solution: faking the foreign key constraints.

They read the pg_catalog.pg_constraint table to get this information. The idea was that I could manipulate the search_path and create my own pg_constraint table where I could make the fake foreign keys:

CREATE TABLE pg_constraint (LIKE pg_catalog.pg_constraint);

ALTER ROLE postgrest IN DATABASE p2k SET search_path = public, remote, pg_catalog;

I was able to make such a table, and the query they use gave something reasonable back. However it seems to fail for some other reason.

It's also hackish and quite a bother to create the information in pg_constraint correctly. So I probably won't accept this answer, just wanted to put it here because it might work for someone else / some similar use case.

Related Question