PostgreSQL如何查询指定数据表的列属性与字段定义？

有两种常用方案可以满足你的需求：

方法1：psql命令行客户端专属快捷操作 #

如果你使用PostgreSQL官方自带的psql命令行工具，切换到test1数据库后直接执行以下命令即可：

\d+ users

返回结果会直接展示所有字段的类型、约束、默认值等全量定义，和你期望的格式高度匹配。

方法2：通用SQL查询（适配所有数据库客户端） #

如果使用的是DBeaver、Navicat等第三方客户端，无法使用psql专属命令，可以执行如下SQL查询系统表，直接生成你需要的格式结果：

SELECT 
  column_name || ': ' || 
  data_type || 
  -- 拼接字符串类型长度
  CASE WHEN character_maximum_length IS NOT NULL THEN '(' || character_maximum_length || ')' ELSE '' END ||
  -- 拼接非空约束
  CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END ||
  -- 拼接默认值
  CASE WHEN column_default IS NOT NULL THEN ' DEFAULT ' || column_default ELSE '' END ||
  -- 拼接主键约束
  CASE WHEN EXISTS (
    SELECT 1 FROM pg_constraint 
    WHERE conrelid = 'users'::regclass 
      AND conkey @> ARRAY[ordinal_position] 
      AND contype = 'p'
  ) THEN ' PRIMARY KEY' ELSE '' END AS column_definition
FROM information_schema.columns 
WHERE table_name = 'users' 
  AND table_schema = 'public' -- 若你的表不在public schema，请修改为对应schema名称
ORDER BY ordinal_position;

你给出的示例users表执行上述查询后，返回结果如下：

id: integer NOT NULL DEFAULT nextval('users_id_seq'::regclass) PRIMARY KEY
created_at: timestamp with time zone DEFAULT CURRENT_TIME
updated_at: timestamp without time zone DEFAULT CURRENT_TIME
contents: character varying(240) NOT NULL

注：SERIAL是PostgreSQL的语法糖，本质是integer类型加序列默认值，因此查询结果会展示实际的底层存储规则，如果你需要展示SERIAL字样，调整上述SQL的判断逻辑即可。

如果你需要完整的原始建表语句，可以直接调用内置函数：

SELECT pg_get_tabledef('users');

执行后会直接返回完整的CREATE TABLE语句，和表创建时的语句基本完全一致。

内容的提问来源于stack exchange，提问作者Ken Masters