主页 / dba / 问题 / 75015
Accepted
vfclists
Asked: 2014-08-27 14:46:36 +0800 CST

获取查询、表或视图的列名和数据类型

  • 772

是否有返回查询、表或视图的字段名称和字段类型的 PostgreSQL 查询或命令？

例如，如果应用于简单的 SELECT 查询的解决方案SELECT * from person应该返回一个列表，如：

Column Name   | Column Type
===========================
First Name    | character
Last Name     | character
Age           | integer
Date of Birth | date

我查看了information_schema下面答案中描述的视图，它似乎很好地涵盖了表格，我怀疑它也涵盖了视图，但我还没有检查过。

最后一个是任意但有效的 SELECT 查询，例如在数据库中涉及、JOINS等 UNIONS。是否有内置过程或其他存储过程或脚本可以为任何有效的 QUERY 返回相同的内容？

我正在开发一个创建数据和查询表单的程序，数据验证和对返回的数据执行函数需要这些信息。

postgresql datatypes

5 个回答

  1. Best Answer

    information_schema与系统目录

    我们已经讨论过很多次了。信息模式服务于某些目的。系统目录是所有信息的实际来源。

    信息模式提供有助于可移植性的标准化视图 - 主要是跨主要 Postgres 版本，因为一旦您的查询足够复杂以查找系统目录，跨不同 RDBMS 平台的可移植性通常是一种错觉。值得注意的是，Oracle 仍然不支持信息模式。

    信息模式中的视图必须经过许多环节才能达到符合标准的格式。这使它们变得缓慢，有时甚至非常缓慢。比较这些基本对象的计划和性能：

    EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

    差异是显着的。

    你的例子

    对于您的示例SELECT * from tbl，比较下面这个简单表的两个查询：

    CREATE TEMP TABLE foo(
   a numeric(12,3)
 , b timestamp(0)
);

    使用pg_attribute：

    SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

    format_type()返回带有所有修饰符的完整类型：

    attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

    另请注意，转换regclass为根据当前解析表名search_path。如果名称无效，它会引发异常。看：

    使用information_schema.columns：

    SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

    信息是标准化的，但不完整：

    column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

    要获取数据类型的完整信息，您需要另外考虑所有这些列：

    character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

    相关答案：

    利弊清单

    最大的专业人士（IMO）以粗体显示。

    信息模式视图

    • 通常更简单（取决于）
    • 减缓
    • 预处理，可能适合也可能不适合您的需求
    • 选择性（用户只能看到他们有权限的对象）
    • 符合 SQL 标准（由一些主要的 RDBMS 实现）
    • 主要可跨主要 Postgres 版本移植
    • 不需要太多关于 Postgres 的具体知识
    • 标识符是描述性的，冗长的，有时很尴尬

    系统目录

    • 通常更复杂（取决于），更接近源
    • 快速地
    • 完整（包括系统列oid）
    • 不符合 SQL 标准
    • 跨主要 Postgres 版本的可移植性较差（但基础不会改变）
    • 需要更具体的 Postgres 知识
    • 标识符简洁，描述性较差，但方便简短

    任意查询

    要从查询中获取相同的列名和类型列表，您可以使用一个简单的技巧：查询输出中CREATE的临时表，然后使用与上述相同的技术。

    您可以附加LIMIT 0，因为您不需要实际数据：

    CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

    要获取各个列的数据类型，您还可以使用函数pg_typeof()：

    SELECT pg_typeof(1);
    • 33

  2. 您可以使用psql命令行客户端。

    \dt将显示表格列表

    \dv将显示视图列表

    \d [object_name]将描述表或视图的架构

    不确定您将如何描述查询。

    更多信息：https ://manikandanmv.wordpress.com/tag/basic-psql-commands/

    • 3

  3. 如果您有权访问 pg_catalog 并使用 PgAdmin3，我强烈推荐我在 Valentine's Tech 博客 ( http://tech.valgog.com/2011/02/pgadmin-iii-macros-get-table-fields) 上找到的解决方案。 html ). 它是一个 PgAdmin3 宏，可以通过快捷方式访问以显示所选表名的定义。

    select quote_ident(nspname) || '.' || quote_ident(relname) as table_name, 
       quote_ident(attname) as field_name, 
       format_type(atttypid,atttypmod) as field_type, 
       case when attnotnull then ' NOT NULL' else '' end as null_constraint,
       case when atthasdef then 'DEFAULT ' || 
                                ( select pg_get_expr(adbin, attrelid) 
                                    from pg_attrdef 
                                   where adrelid = attrelid and adnum = attnum )::text else ''
       end as dafault_value,
       case when nullif(confrelid, 0) is not null
            then confrelid::regclass::text || '( ' || 
                 array_to_string( ARRAY( select quote_ident( fa.attname ) 
                                           from pg_attribute as fa 
                                          where fa.attnum = ANY ( confkey ) 
                                            and fa.attrelid = confrelid
                                          order by fa.attnum 
                                        ), ','
                                 ) || ' )'
            else '' end as references_to
  from pg_attribute 
       left outer join pg_constraint on conrelid = attrelid 
                                    and attnum = conkey[1] 
                                    and array_upper( conkey, 1 ) = 1,
       pg_class, 
       pg_namespace
 where pg_class.oid = attrelid
   and pg_namespace.oid = relnamespace
   and pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
   and attnum > 0
   and not attisdropped
 order by attrelid, attnum;

    像魅力一样工作，非常有用。

    • 3

  4. 使用视图，它们是 SQL 标准并包含您想要的信息。information_schema

    您也可以直接访问pg_class,pg_attribute等，但这不便携且通常更繁琐；对于某些事情，您可能需要,等辅助函数。oidvectortypespg_get_function_arguments

    如果您想查看如何psql执行类似的内容\dt，请运行psql -E- 它会打印查询。但是，information_schema如果它可以满足您的需求，通常最好使用它。

    • 1

  5. 这可能过于简单，但 pgAdmin4 会在输出结果中显示字段类型。上面的其他解决方案可能更优雅，但是当我只需要快速回答时，我发现 pgAdmin4 的查询 GUI 工作得很好。试图找出视图或函数返回的计算字段的字段类型可能很棘手。

    在此处输入图像描述

    • 1

相关问题