如何在PostgreSQL中实现SQLite的PRAGMA table_info(table)等价功能

PostgreSQL没有直接对应PRAGMA table_info的语法，你可以通过以下两种方式获取等价的表结构信息：

方法一：使用标准SQL的information_schema.columns（兼容性好） #

这是ANSI标准的查询方式，适用于大多数关系型数据库，返回字段可与SQLite的table_info一一对应：

SELECT 
    ordinal_position AS cid,
    column_name AS name,
    data_type AS type,
    CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END AS "notnull",
    column_default AS dflt_value,
    CASE WHEN column_name IN (
        SELECT column_name
        FROM information_schema.key_column_usage
        WHERE table_name = 'your_table'
          AND table_schema = 'public'
          AND constraint_name LIKE '%_pkey'
    ) THEN 1 ELSE 0 END AS pk
FROM information_schema.columns
WHERE table_name = 'your_table'
  AND table_schema = 'public'
ORDER BY ordinal_position;

字段对应关系：

• cid：列的序号（对应SQLite的cid）
• name：列名（对应SQLite的name）
• type：数据类型（对应SQLite的type）
• notnull：1表示非空约束，0表示允许为空（对应SQLite的notnull）
• dflt_value：列的默认值（对应SQLite的dflt_value）
• pk：1表示该列是主键，0表示不是（对应SQLite的pk）

方法二：使用PostgreSQL系统表pg_catalog（信息更详细） #

如果需要获取更贴近PostgreSQL内部的类型信息（比如带长度限制的varchar(50)而非单纯的character varying），可以直接查询系统表：

SELECT 
    a.attnum AS cid,
    a.attname AS name,
    format_type(a.atttypid, a.atttypmod) AS type,
    CASE WHEN a.attnotnull THEN 1 ELSE 0 END AS "notnull",
    pg_get_expr(d.adbin, d.adrelid) AS dflt_value,
    CASE WHEN EXISTS (
        SELECT 1 FROM pg_constraint c
        WHERE c.conrelid = a.attrelid
          AND c.contype = 'p'
          AND a.attnum = ANY(c.conkey)
    ) THEN 1 ELSE 0 END AS pk
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = 'public.your_table'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

注意：将上述语句中的your_table替换为实际表名，public是默认的模式名，如果你的表在其他模式下，需要修改对应的模式名称。

内容的提问来源于stack exchange，提问作者David Muñoz Tord