Get overview of all tables and foreign tables with options:
SELECT c.relname AS "Table",
CASE WHEN c.relkind = 'r' THEN 'ordinary table'
WHEN c.relkind = 'v' THEN 'view'
WHEN c.relkind = 'm' THEN 'materialized view'
WHEN c.relkind = 'f' THEN 'foreign table'
ELSE 'other' END AS "Type",
pg_size_pretty(pg_total_relation_size(c.oid)) AS "Size",
ft.ftoptions as "fdw options"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_foreign_table as ft ON c.oid = ft.ftrelid
WHERE c.relkind IN ('r', 'v', 'm', 'f')
AND n.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY n.nspname, c.relname;
Get overview of all tables and foreign tables with options:
SELECT c.relname AS "Table", CASE WHEN c.relkind = 'r' THEN 'ordinary table' WHEN c.relkind = 'v' THEN 'view' WHEN c.relkind = 'm' THEN 'materialized view' WHEN c.relkind = 'f' THEN 'foreign table' ELSE 'other' END AS "Type", pg_size_pretty(pg_total_relation_size(c.oid)) AS "Size", ft.ftoptions as "fdw options" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_foreign_table as ft ON c.oid = ft.ftrelid WHERE c.relkind IN ('r', 'v', 'm', 'f') AND n.nspname NOT IN ('information_schema', 'pg_catalog') ORDER BY n.nspname, c.relname;