如何用单条PostgreSQL查询提取表所有列的列名、数据类型及样本值

你说得没错，PostgreSQL的普通SQL确实不支持直接声明变量来遍历列，但我们可以通过动态SQL或者利用PostgreSQL内置的系统元数据视图来实现这个需求，不用再硬编码列名。下面提供两种实用的解决方案：

方法一：用PL/pgSQL函数封装（灵活适配任意表） #

如果需要反复查询不同表的样本数据，写一个可复用的函数是最佳选择。这个函数会自动读取目标表的列信息，然后动态获取每个列的样本值（这里默认取第一行的数据，你可以根据需求调整）：

CREATE OR REPLACE FUNCTION get_table_sample(table_name text)
RETURNS TABLE(column_name text, data_type text, sample_value text) AS $$
DECLARE
    col record;
BEGIN
    -- 从系统元数据中获取表的列名和数据类型
    FOR col IN 
        SELECT a.attname AS column_name, t.typname AS data_type
        FROM pg_attribute a
        JOIN pg_type t ON a.atttypid = t.oid
        WHERE a.attrelid = table_name::regclass
          AND a.attnum > 0  -- 排除系统隐藏列
          AND NOT a.attisdropped  -- 排除已删除的列
        ORDER BY a.attnum
    LOOP
        -- 动态执行查询，获取当前列的样本值并转为文本格式
        EXECUTE format('SELECT %I::text FROM %s LIMIT 1', col.column_name, table_name)
        INTO sample_value;
        
        RETURN NEXT;  -- 将当前列的结果加入返回集
    END LOOP;
END;
$$ LANGUAGE plpgsql;

使用时只需要传入表名：

-- 替换成你的目标表名
SELECT * FROM get_table_sample('your_target_table');

函数的优势： #

• 自动适配表结构变化，新增/删除列都不用修改代码
• 可以灵活调整样本逻辑（比如取随机行、非空值等，只需修改EXECUTE里的查询语句）

方法二：单条SQL查询（无需创建函数） #

如果只是临时查询，不想创建函数，可以用JSON转换的方式实现单条SQL搞定：

-- 替换成你的目标表名
SELECT 
    a.attname AS column_name,
    t.typname AS data_type,
    (row_to_json(sample_row)).*->>a.attname AS sample_value
FROM 
    pg_attribute a
JOIN 
    pg_type t ON a.atttypid = t.oid,
    -- 取表的第一行作为样本数据
    (SELECT * FROM your_target_table LIMIT 1) sample_row
WHERE 
    a.attrelid = 'your_target_table'::regclass
    AND a.attnum > 0
    AND NOT a.attisdropped
ORDER BY 
    a.attnum;

原理说明： #

1. 先通过子查询(SELECT * FROM your_target_table LIMIT 1)获取表的第一行数据
2. 用row_to_json()把这行数据转成JSON对象
3. 关联系统视图pg_attribute和pg_type获取列名和类型，再从JSON对象中提取对应列的样本值

注意点： #

• 如果目标表为空，sample_value会返回NULL
• 所有样本值会被转为文本格式，如果你需要保留原始类型，可以去掉::text和->>，改用->获取JSON值，但结果会是JSON类型

内容的提问来源于stack exchange，提问作者Andrew Goss