Ah. I didn't realize Postgrest was something, rather than just a typo. An fkey to a foreign table is not supported.
Related- https://dba.stackexchange.com/questions/138591/foreign-key-references-constraint-on-postgresql-foreign-data-wrapper *Michael Lewis | Software Engineer* *Entrata* *c: **619.370.8697 <619-370-8697>* On Wed, Feb 13, 2019 at 2:43 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 2/13/19 1:36 PM, Michael Lewis wrote: > > You don't need an fkey to write a select statement with a join. I think > > I must be missing something. Are you wanting it to enforce integrity > > Me to until I looked at this: > > http://postgrest.org/en/v5.2/api.html#resource-embedding > > "PostgREST can also detect relations going through join tables. Thus you > can request the Actors for Films (which in this case finds the > information through Roles). You can also reverse the direction of > inclusion, asking for all Directors with each including the list of > their Films: > > GET /directors?select=films(title,year) HTTP/1.1 > > Important > > Whenever foreign key relations change in the database schema you must > refresh PostgREST’s schema cache to allow resource embedding to work > properly. See the section Schema Reloading. > " > > > across the dblink? Or are you adding an fkey with the assumption that > > you will get an index? > > * > > * > > * > > * > > *Michael Lewis | Software Engineer* > > *Entrata** > > * > > *c:**619.370.8697 <tel:619-370-8697>* > > > > > > On Wed, Feb 13, 2019 at 2:04 PM adrien ruffie <adriennolar...@hotmail.fr > > <mailto:adriennolar...@hotmail.fr>> wrote: > > > > we have a tricky problem with my colleague. > > We have to database db1 and db2 linked by a foreign data wrapper > > connection. > > 1 table "contractline" in db1 and "contract" in db2. > > > > We use postgrest in order to request db2 via CURL. > > But we want to add a link between previous tables. > > > > In db2 we have a foreign table ft_contractline > > example: > > > > Column | Type | Collation | Nullable > > | Default | FDW options | Storage | Stats target | Description > > > > --------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+------------- > > id | character varying(1024) | | not null > > | | | extended | | > > ccode | text | | | > > | | extended | | > > status | text | | > > | | | extended | | > > > > We want to add for example, the following constraint: > > ALTER TABLE contract ADD CONSTRAINT > > contractline_ft_contract_fkey FOREIGN KEY (contractid) > > REFERENCES ft_contractline(ccode); > > > > in order to use the following query (via CURL on db2): > > SELECT c.name <http://c.name>, c.id <http://c.id> FROM contract > > c JOIN ft_contractline ft_c ON c.id <http://c.id> = ft_c.ccode > > WHERE c.type = 'business' > > > > but we saw, isn't possible to add a foreign key on 'contract' > > table of db2 to 'ft_contractline' foreign table ... > > > > Do you know way to do it ? > > > > Thank all and best regards. > > > > Adrien > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
