Postgresql 表结构、列名相关信息查询

SELECT A.attnum AS "序号",C.relname AS "表名",CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",A.attname AS "字段名",T.typname AS "类型",CASE whenSUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) isNUllthen '0'elsesubstr(SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ),2,CHAR_LENGTH(SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ))-2)end as "长度",
CASEA.attnotnull WHEN 't' THEN'是' WHEN 'f' THEN'否' END AS 不是null,CASEA.attnotnull WHEN 't' THEN'是' END AS "主键",d.description AS "注释" 
FROMpg_class C,pg_attribute A,pg_type T,pg_description d 
WHERE
-- 表名C.relname = 'j_auditing'  AND A.attnum > 0 AND A.attrelid = C.oid AND A.atttypid = T.oid AND d.objoid = A.attrelid AND d.objsubid = A.attnum
————————————————

2. 使用information_schema.columns视图

在SQL查询中，你可以通过查询information_schema.columns视图来获取表的结构信息。例如：

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

 注意：使用table_name = 'your_table_name'时，如果你的表名是大写的，你也需要确保在查询时使用大写，或者使用双引号将表名括起来，并确保查询时的大小写与表的实际大小写一致。例如：

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'your_table_name' -- 确保大小写一致或使用双引号

3.使用pg_catalog.pg_attribute和pg_catalog.pg_class表

对于更高级的查询或者当你需要更详细的信息时，你可以直接查询pg_catalog.pg_attribute和pg_catalog.pg_class表。例如：

SELECT a.attname AS column_name,pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,a.attnotnull AS is_not_null,(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS column_default
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'your_table_name' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;