Lookup Primary Key of Foreign Server's Table

From: Chris Morris <chris(at)mysteryscience(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Lookup Primary Key of Foreign Server's Table
Date: 2019-10-22 16:16:05
Message-ID: CALrUc2Whpei56aLFj2T=d9Y6+jgZwPF0j8C5BDFx7TV61pzBcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm looking for a system query that will lookup the primary key column on a
fdw table. It's possible we need to declare that part of the foreign
table's schema in the local (is that the right term?) database?

Here's the foreign table - I don't see anything showing a primary key, so
my hunch is we need to declare it in the local schema?

*=> \d sidecar_link.actions
Foreign table "sidecar_link.actions" Column | Type
| Collation | Nullable | Default
| FDW options
-----------------+-----------------------------+-----------+----------+--------------------------------------------------+-------------
id
| bigint | | not null |
nextval('sidecar_link.actions_id_seq'::regclass) | user_session_id |
bigint | | not null |
| user_id | bigint
| | not null |
| created_at | timestamp without time zone | | not null |
now() | occurred_at |
timestamp without time zone | | not null | now()
| thing_id | integer
| | | |
parent_thing_id | integer | | |
| viewing_id | integer
| | |
| origin | origin |
| | 'mysteryscience'::origin | scope
| text | | not null |
| name | text
| | not null |
| details | text | |
| | request_path
| text | | |
| Server: pg_mysterysci_sidecarFDW options:
(schema_name 'public', table_name 'actions')*
Not really related question, but a curiosity: why does this table not show
in the list of foreign tables?

*=> \det List of foreign tables Schema | Table | Server
--------+-------+--------(0 rows)*

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2019-10-22 16:35:17 Re: Lookup Primary Key of Foreign Server's Table
Previous Message Adrian Klaver 2019-10-22 15:27:13 Re: existing dblinks