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

小开

最佳答案

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)
);

Change the hello world with your table name

小开

SELECT
column_name,
data_type
FROM
information_schema.columns
WHERE
table_name = 'table_name';

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

小开

打开 psql命令行并键入:

\d+ table_name

小开

更新了 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'
);

小开

不要忘记添加模式名称，以防有多个模式具有相同的表名称。

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

或使用 psql:

\d+ your_schema_name.your_table_name

小开

 --how to get a list column names and datatypes of a table in PostgreSQL?




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
;

小开

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 语句(而不是表)上的列名和数据类型。

如果希望对 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;

简短的解释是，它为 select 语句创建了一个(临时)表，您可以通过@a _ horse _ with _ no _ name 和@selva 提供的查询“调用”这个表。

希望这个能帮上忙。

小开

支持在特定架构中查找表的列名和类型并使用 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;

小开

在不提及模式的情况下，您还可以获得所需的详细信息试试这个查询->

选择 column _ name，data _ type 来自 information _ schema. column 其中 table _ name = ‘ table _ name’;

小开

下面将在提供的模式名称中列出所有表的所有不同数据类型。

\copy (select distinct data_type, column_name from information_schema.columns where table_name in (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' and schemaname = '<Your schema name>')) to 'datatypes.csv'  delimiter as ',' CSV header

小开

我一直在寻找一种不使用 PSQL cl 直接从 pgAdmin 4获得具有数据类型的列名的方法，并找到了一种变通方法。又多了一个选择:

右键单击所需的数据库 > generatedERD (Beta) > GenerateSQL (或 Alt + Ctrl + S) ，pgAdmin 4将打开查询编辑器，您可以在其中找到所有具有列名和数据类型的表:

小开

创建一个新函数来获取表格信息

CREATE FUNCTION xdesc(in t varchar) RETURNS table(column_name varchar,
data_type varchar) AS $$
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = $1
$$ LANGUAGE SQL;




select xdesc('rs_rail_job_index')