我正在針對為使用者配置數據庫的應用程序建構集成測試。為其創建的使用者不是超級使用者，並且無權訪問 schema_information.tables 因為當我嘗試以下腳本時：

SELECT table_name
FROM information_schema.tables
WHERE table_schema='{schema}'

我得到了 0 的回報，因為這個使用者沒有權限。

我正在嘗試查詢數據庫以驗證創建的表和列。我可以使用以下腳本通過系統目錄獲取表名列表：

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = '{schema}'

這會以我想要的方式輸出表名：

業務，位置，人等…

我找不到帶有系統目錄的腳本，然後找到每個表的列名（作為獎勵，數據類型）。到目前為止，我已經嘗試了以下方法：

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE  attrelid = 'business'

這是錯誤：

ERROR:  invalid input syntax for type oid: "business"
LINE 1: ...od) AS type FROM   pg_attribute WHERE  attrelid = 'business'
                                                        ^```

也試過：

SELECT
   a.attname as "Column",
   pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
   pg_catalog.pg_attribute a
WHERE
   a.attnum > 0
   AND NOT a.attisdropped
   AND a.attrelid = (
       SELECT c.oid
       FROM pg_catalog.pg_class c
           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'
           --AND pg_catalog.pg_table_is_visible(c.oid)
   );

這將返回帶有架構或數據庫的 0。我不知道該為 c.relname 放什麼。我是否也看到 0 是因為基本使用者無法看到比模式中的表更深的內容，期間？

只需從中選擇information_schema.columns即可。

SELECT table_catalog, table_schema, table_name, data_type
FROM information_schema.tables
WHERE table_schema='{schema}';

為了榮耀

無論出於何種原因，如果您無法查詢information_schema.columns（否則表明某些東西對我來說很時髦）。psql -E我們可以使用可以的使用者有效地對目錄進行逆向工程。然後執行適當的客戶端 ( \) 命令，psql該命令會顯示您想要的內容，例如\d schema. 並複制出您需要的導出查詢的部分..

對我來說，最後一欄類似於

SELECT a.attname,
 pg_catalog.format_type(a.atttypid, a.atttypmod),
 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
  FROM pg_catalog.pg_attrdef d
  WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
 a.attnotnull, a.attnum,
 (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
  WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
 NULL AS indexdef,
 NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1024334' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

現在我只需要獲取attrelid模式的所有 ‘。跑得快\d asdofkodskf，我看到了，

SELECT c.oid,
 n.nspname,
 c.relname
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(asdofkodskf)$'
 AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

我們實際上n.nspname不想c.relname

所以鑑於我的版本psql，直接查詢目錄的官方方法是……

SELECT c.oid,
 n.nspname,
 c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
 SELECT a.attname,
   pg_catalog.format_type(a.atttypid, a.atttypmod),
   (
     SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
     FROM pg_catalog.pg_attrdef d
     WHERE d.adrelid = a.attrelid
       AND d.adnum = a.attnum
       AND a.atthasdef
   ),
   a.attnotnull, a.attnum,
   (
     SELECT c.collname
     FROM
       pg_catalog.pg_collation c,
       pg_catalog.pg_type t
     WHERE c.oid = a.attcollation
       AND t.oid = a.atttypid
       AND a.attcollation <> t.typcollation
   ) AS attcollation
 FROM pg_catalog.pg_attribute a
 WHERE a.attrelid = c.oid
   AND a.attnum > 0
   AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(public)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

摘錄（沒有所有列或所有行）（relname 是表，attname 是列）

  oid   | nspname |           relname           |       attname        |       format_type       
llation 
---------+---------+-----------------------------+----------------------+-------------------------
--------
1024242 | public  | spatial_ref_sys_pkey        | srid                 | integer                 
1045853 | public  | product_discount_qty_excl   | qty                  | int4range               
1024334 | public  | valid_detail                | valid                | boolean                 
1024334 | public  | valid_detail                | reason               | character varying       
1024334 | public  | valid_detail                | location             | geometry                
1045847 | public  | product_discount            | pid                  | integer                 
1045847 | public  | product_discount            | qty                  | int4range               
1045847 | public  | product_discount            | percent_modifier     | real                    
1024569 | public  | geography_columns           | f_table_catalog      | name                    

引用自：https://dba.stackexchange.com/questions/195717