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

原创
2018/11/08 14:14
阅读数 933

如下示例,在数据库不同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修饰就直接可以获取的关系。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部