Question

如何查PostgreSQL 数据库中所有的表

Answer 1

这个也是从 oid2name 中扒出来的：

[postgres@localhost bin]$ ./oid2name -d postgres
From database "postgres":
now:
SELECT pg_catalog.pg_relation_filenode(c.oid) as "Filenode", relname as "Table Name" FROM pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),pg_catalog.pg_tablespace t WHERE relkind IN ('r') AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND t.oid = CASE WHEN reltablespace <> 0 THEN reltablespace ELSE dattablespace END ORDER BY relname
Filenode Table Name
----------------------
24608 gaotab
24604 testtab
[postgres@localhost bin]$

执行结果就是这样，这里没有用我给出的 postgres 数据库名 ，而是用了 pg_catalog.current_database()

我把格式整理一下，并且把数据库名换成我想要的 ’postgres‘，当然，如果有其他数据库，换其他的名字就可以了。

SELECT
pg_catalog.pg_relation_filenode(c.oid) as "Filenode",
relname as "Table Name"

FROM
pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_database d ON d.datname = 'postgres',
pg_catalog.pg_tablespace t
WHERE
relkind IN ('r')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname !~ '^pg_toast'
AND t.oid = CASE WHEN reltablespace <> 0 THEN reltablespace ELSE dattablespace END

ORDER BY
relname

其实 t 是没有必要的，还可以再简化：

SELECT
pg_catalog.pg_relation_filenode(c.oid) as "Filenode",
relname as "Table Name"

FROM
pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_database d ON d.datname = 'postgres'

WHERE
relkind IN ('r')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname !~ '^pg_toast'

ORDER BY
relname