postgres的\d命令不显示全部的用户表

最新推荐文章于 2024-06-13 21:12:59 发布

weixin_33869377

最新推荐文章于 2024-06-13 21:12:59 发布

阅读量2.3k

点赞数

文章标签：数据库 python 操作系统

原文链接：https://my.oschina.net/lcc1990/blog/2873574

版权

2019独角兽企业重金招聘Python工程师标准>>>

如下示例，在数据库不同schema下有同名表t2 ,但是使用\d命令的时候只有一个t2表被查询出来。

postgres=# select oid,nspname from pg_namespace; 
  oid  |      nspname       
-------+--------------------
    99 | pg_toast
 11736 | pg_temp_1
 11737 | pg_toast_temp_1
    11 | pg_catalog
  2200 | public
 12920 | information_schema
 16384 | postgres
 16391 | lichuancheng
(8 rows)

postgres=# select relname,relnamespace from pg_class where relname  = 't2';
 relname | relnamespace 
---------+--------------
 t2      |         2200
 t2      |        16391
(2 rows)

postgres=# \d
             List of relations
    Schema    | Name | Type  |    Owner     
--------------+------+-------+--------------
 lichuancheng | t2   | table | lichuancheng
(1 row)

postgres=#

经过调查发现，执行\d命令后，客户端程序将\d命令转化为如下sql（pg10）

SELECT n.nspname as "Schema",
 c.relname as "Name",
 CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
 pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','p','v','m','S','f','')
 AND n.nspname <> 'pg_catalog'
 AND n.nspname <> 'information_schema'
 AND n.nspname !~ '^pg_toast'
 AND pg_catalog.pg_table_is_visible(c.oid);

其中有一个查询条件'AND pg_catalog.pg_table_is_visible(c.oid)',就是将另外一个t2表忽略的筛选条件

结论：\d命令不显示所有的关系。它只显示当前不使用schema修饰就直接可以获取的关系。

转载于:https://my.oschina.net/lcc1990/blog/2873574