主页 / dba / 问题 / 195717
Accepted
as.beaulieu
Asked: 2018-01-19 10:58:39 +0800 CST

postgres系统目录查询表中的列

  • 772

我正在针对为用户配置数据库的应用程序构建集成测试。为其创建的用户不是超级用户，并且无权访问 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 是因为基本用户无法看到比模式中的表更深的内容？

postgresql information-schema

1 个回答

  1. Best Answer

    只需从中选择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
    • 4

相关问题