SQL 如何在 PostgreSQL 中获取表的列表列名和数据类型？

How to get a list column names and datatype of a table in PostgreSQL?

提问by Pratik

With the following query, we can get a list of column names and datatype of a table in PostgreSQL.

通过以下查询，我们可以获取 PostgreSQL 中表的列名和数据类型列表。

采纳答案by Pratik

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 ~ '^(hello world)$'
                AND pg_catalog.pg_table_is_visible(c.oid)
        );

More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

关于它的更多信息：http: //www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

回答by Marouane Afroukh

Open psql commande line and type :

打开 psql 命令行并输入：

\d+ table_name

回答by selva

select column_name,data_type 
from information_schema.columns 
where table_name = 'table_name';

with the above query you can columns and its datatype

使用上述查询，您可以使用列及其数据类型

回答by BatCat

Don't forget to add the schema name in case you have multiple schemas with the same table names.

如果您有多个具有相同表名的模式，请不要忘记添加模式名称。

SELECT column_name, data_type 
FROM information_schema.columns
WHERE table_name = 'your_table_name' AND table_schema = 'your_schema_name';

or using psql:

或使用 psql：

\d+ your_schema_name.your_table_name

回答by Honza Kucha?

Updated Pratik answer to support more schemas and nullables:

更新了 Pratik 答案以支持更多模式和可空值：

SELECT
    "pg_attribute".attname                                                    as "Column",
    pg_catalog.format_type("pg_attribute".atttypid, "pg_attribute".atttypmod) as "Datatype",

    not("pg_attribute".attnotnull) AS "Nullable"
FROM
    pg_catalog.pg_attribute "pg_attribute"
WHERE
    "pg_attribute".attnum > 0
    AND NOT "pg_attribute".attisdropped
    AND "pg_attribute".attrelid = (
        SELECT "pg_class".oid
        FROM pg_catalog.pg_class "pg_class"
            LEFT JOIN pg_catalog.pg_namespace "pg_namespace" ON "pg_namespace".oid = "pg_class".relnamespace
        WHERE
            "pg_namespace".nspname = 'schema'
            AND "pg_class".relname = 'table'
    );

回答by Michal Charemza

A version that supports finding the column names and types of a table in a specific schema, and uses JOINs without any subqueries

支持在特定模式中查找表的列名和类型的版本，并使用没有任何子查询的 JOIN

SELECT
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = 'my_schema'
    AND pg_class.relname = 'my_table'
ORDER BY
    attnum ASC;

回答by abhinav kumar

without mentioning schema also you can get the required details Try this query->

无需提及架构，您也可以获得所需的详细信息尝试此查询->

select column_name,data_type from information_schema.columns where table_name = 'table_name';

select column_name,data_type from information_schema.columns where table_name = 'table_name';

回答by Yordan Georgiev

    SELECT DISTINCT
        ROW_NUMBER () OVER (ORDER BY pgc.relname , a.attnum) as rowid , 
        pgc.relname as table_name ,
        a.attnum as attr,
        a.attname as name,
        format_type(a.atttypid, a.atttypmod) as typ,
        a.attnotnull as notnull, 
        com.description as comment,
        coalesce(i.indisprimary,false) as primary_key,
        def.adsrc as default
    FROM pg_attribute a 
    JOIN pg_class pgc ON pgc.oid = a.attrelid
    LEFT JOIN pg_index i ON 
        (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
    LEFT JOIN pg_description com on 
        (pgc.oid = com.objoid AND a.attnum = com.objsubid)
    LEFT JOIN pg_attrdef def ON 
        (a.attrelid = def.adrelid AND a.attnum = def.adnum)
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = pgc.relnamespace

    WHERE 1=1 
        AND pgc.relkind IN ('r','')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND n.nspname !~ '^pg_toast'

    AND a.attnum > 0 AND pgc.oid = a.attrelid
    AND pg_table_is_visible(pgc.oid)
    AND NOT a.attisdropped
    ORDER BY rowid
    ;

回答by Surendra babu Pasumarhti

select column_name,data_type from information_schema.columns where table_name = 'your_table_name' and table_catalog = 'your_database_name' and table_schema = 'your_schema_name';

select column_name,data_type from information_schema.columns where table_name = 'your_table_name' and table_catalog = 'your_database_name' and table_schema = 'your_schema_name';

回答by Jens van Hellemondt

To make this topic 'more complete'.

为了使这个主题“更完整”。

I required the column names and data types on a SELECT statement (not a table).

我需要 SELECT 语句（不是表）上的列名和数据类型。

If you want to do this on a SELECT statement instead of an actual existing table, you can do the following:

如果要在 SELECT 语句而不是实际现有表上执行此操作，可以执行以下操作：

DROP TABLE IF EXISTS abc;
CREATE TEMPORARY TABLE abc AS
-- your select statement here!
SELECT 
    *
FROM foo
-- end your select statement
;

select column_name, data_type 
from information_schema.columns 
where table_name = 'abc';
DROP IF EXISTS abc;

Short explanation, it makes a (temp) table of your select statement, which you can 'call' upon via the query provided by (among others) @a_horse_with_no_name and @selva.

简短的解释，它为您的选择语句制作了一个（临时）表，您可以通过（以及其他）@a_horse_with_no_name 和@selva 提供的查询“调用”该表。

Hope this helps.

希望这可以帮助。