In PostgreSQL, running \d command will list the tables along with their table type. Currently I'm trying to list out all foreign tables that I've created using foreign data wrapper. What is the query to list out those tables?
Add a comment
|
5 Answers
Query for listing foreign tables only:
select * from information_schema.foreign_tables
Comments
According to the manual \dE[S+] should do it.
http://www.postgresql.org/docs/current/static/app-psql.html
To see the query behind this, start psql with the -e ("echo queries") option.
Or use the information_schema.tables view: http://www.postgresql.org/docs/current/static/infoschema-tables.html
The table_type column will contain FOREIGN TABLE for those tables.
4 Comments
Azuan
actually I want to avoid using \d command. trying to find a query that looks like select table_name from schema.relationship
Azuan
documentation does help me. Thanks :) I use this query: select table_name,table_type from information_schema.tables where table_type='FOREIGN TABLE' and table_schema='public';
Shayan
I don't understand why this answer and gph's answer don't work (the commands say no relations found) but gargii's answer works and shows my table.
You can also use this command:
\detr
Comments
SELECT tc.table_name , ctu.table_name
FROM information_schema.table_constraints tc
inner join information_schema.constraint_table_usage ctu on ctu.constraint_name = tc.constraint_name
where constraint_type = 'FOREIGN KEY'
The query behind the mentioned \detr command is:
SELECT n.nspname AS "Schema",
c.relname AS "Table",
s.srvname AS "Server"
FROM pg_catalog.pg_foreign_table ft
INNER JOIN pg_catalog.pg_class c ON c.oid = ft.ftrelid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_foreign_server s ON s.oid = ft.ftserver
WHERE pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
