PostgreSQL 18 INFORMATION_SCHEMA.columns 深度挖掘：10个生产级元数据查询实战

在数据库自动化管理和元数据分析领域，掌握系统视图的高级用法是区分普通开发者和资深工程师的关键能力。PostgreSQL 的 INFORMATION_SCHEMA.columns 视图就像一座未被充分开采的金矿，大多数开发者仅用它查询基础列信息，却不知其蕴含的丰富技术细节。本文将带您超越简单的 SELECT column_name, data_type 查询，解锁10个可直接用于生产环境的高级元数据查询技术。

1. 全面解析列字符集与排序规则

字符型字段的存储特性直接影响数据一致性和查询性能。以下查询可获取完整的字符集和排序规则信息：

SELECT 
    column_name,
    data_type,
    character_maximum_length AS max_chars,
    character_octet_length AS max_bytes,
    collation_name,
    CASE 
        WHEN collation_name IS NOT NULL THEN 
            (SELECT description FROM pg_collation WHERE collname = c.collation_name)
        ELSE 'N/A'
    END AS collation_desc
FROM 
    information_schema.columns c
WHERE 
    table_schema = 'public'
    AND table_name = 'customer'
    AND data_type IN ('character varying', 'text', 'char');

典型输出示例：

column_name	data_type	max_chars	max_bytes	collation_name	collation_desc
username	character varying	50	200	en_US.utf8	English_United States
address	text	∞	∞	NULL	N/A

提示： character_octet_length 考虑了多字节编码，在UTF-8中一个字符可能占用1-4个字节

2. 数值类型精度深度分析

数值字段的精度和范围对金融类应用至关重要。这个查询可揭示隐藏的精度参数：

SELECT
    column_name,
    data_type,
    numeric_precision AS actual_precision,
    numeric_precision_radix AS base_system,
    numeric_scale AS decimal_places,
    CASE 
        WHEN numeric_precision_radix = 2 THEN 
            (numeric_precision || '-bit binary (' || 
             round(power(2, numeric_precision)/2) || ' to ' || 
             (round(power(2, numeric_precision)/2)-1) || ')')
        WHEN numeric_precision_radix = 10 THEN
            (numeric_precision || '-digit decimal')
        ELSE 'N/A'
    END AS range_description
FROM
    information_schema.columns
WHERE
    table_schema = 'public'
    AND table_name = 'financial_transactions'
    AND data_type IN ('numeric', 'decimal', 'integer', 'bigint', 'smallint');

输出示例分析：

column_name	data_type	actual_precision	base_system	decimal_places	range_description
transaction_id	bigint	64	2	0	64-bit binary (9.22E+18 to 9.22E+18)
amount	numeric	15	10	2	15-digit decimal
exchange_rate	numeric	10	10	6	10-digit decimal

3. 自动标识列与序列追踪

PostgreSQL 的标识列(auto-increment)背后是序列在运作。此查询可揭示完整序列配置：

SELECT
    c.column_name,
    c.data_type,
    c.is_identity,
    c.identity_generation,
    c.identity_start,
    c.identity_increment,
    c.identity_maximum,
    c.identity_minimum,
    CASE 
        WHEN c.is_identity = 'YES' THEN
            (SELECT seqstart FROM pg_sequence 
             JOIN pg_class ON pg_sequence.seqrelid = pg_class.oid
             WHERE pg_class.relname = c.table_name || '_' || c.column_name || '_seq')
        ELSE NULL
    END AS actual_start_value
FROM
    information_schema.columns c
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'orders'
    AND c.is_identity = 'YES';

关键字段说明：

• identity_generation : ALWAYS(不可覆盖) 或 BY DEFAULT(可覆盖)
• identity_increment : 序列步长值
• actual_start_value : 当前序列实际起始值

4. 生成列(Generated Column)元数据提取

生成列是PostgreSQL 12+的重要特性，此查询可获取其完整定义：

SELECT
    c.column_name,
    c.data_type,
    c.is_generated,
    c.generation_expression,
    pg_get_expr(d.adsrc, d.adrelid) AS full_definition
FROM
    information_schema.columns c
    JOIN pg_attribute a ON a.attname = c.column_name
    JOIN pg_class cl ON cl.oid = a.attrelid
    JOIN pg_namespace n ON n.oid = cl.relnamespace
    LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'products'
    AND c.is_generated != 'NEVER';

示例输出：

column_name	data_type	is_generated	generation_expression	full_definition
total_price	numeric	ALWAYS	(quantity * unit_price)	(quantity * unit_price) STORED
tax_amount	numeric	ALWAYS	(total_price * 0.1)	(total_price * 0.1::numeric) STORED

5. 域类型(Domain)与UDT深度追踪

当列基于域类型或自定义类型时，需要追踪其底层类型：

SELECT
    c.column_name,
    c.data_type AS displayed_type,
    c.domain_name,
    c.udt_name AS underlying_type,
    CASE
        WHEN c.domain_name IS NOT NULL THEN
            (SELECT pg_catalog.format_type(t.oid, NULL) 
             FROM pg_type t 
             JOIN pg_namespace n ON n.oid = t.typnamespace
             WHERE t.typname = c.domain_name)
        WHEN c.udt_name != c.data_type THEN
            (SELECT pg_catalog.format_type(t.oid, NULL) 
             FROM pg_type t 
             WHERE t.typname = c.udt_name)
        ELSE 'N/A'
    END AS type_definition
FROM
    information_schema.columns c
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'user_profiles'
    AND (c.domain_name IS NOT NULL OR c.udt_name != c.data_type);

典型用例：

• 发现基于域类型的字段（如email、phone等验证类型）
• 追踪自定义复合类型的实际结构

6. 时间类型精度与区间分析

时间字段的精度设置影响存储和计算精度：

SELECT
    column_name,
    data_type,
    datetime_precision AS fractional_seconds,
    CASE 
        WHEN data_type = 'interval' THEN 
            (SELECT interval_type FROM information_schema.columns 
             WHERE table_name = c.table_name AND column_name = c.column_name)
        ELSE 'N/A'
    END AS interval_fields,
    CASE
        WHEN data_type IN ('timestamp', 'timestamptz') THEN
            '精确到' || datetime_precision || '位小数秒'
        WHEN data_type = 'time' THEN
            '时间精度：' || datetime_precision || '位小数'
        WHEN data_type = 'interval' THEN
            '区间类型：' || (SELECT interval_type FROM information_schema.columns 
                           WHERE table_name = c.table_name AND column_name = c.column_name)
        ELSE 'N/A'
    END AS description
FROM
    information_schema.columns c
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'event_logs'
    AND data_type IN ('timestamp', 'timestamptz', 'time', 'interval');

7. 列级权限审计查询

安全审计时需要检查列的访问权限：

SELECT
    c.column_name,
    c.data_type,
    array_agg(p.privilege_type) AS privileges,
    array_agg(p.grantee) AS granted_to
FROM
    information_schema.columns c
    JOIN information_schema.column_privileges p 
      ON p.table_schema = c.table_schema 
     AND p.table_name = c.table_name 
     AND p.column_name = c.column_name
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'sensitive_data'
GROUP BY
    c.column_name, c.data_type;

输出示例：

column_name	data_type	privileges	granted_to
ssn	text	{SELECT,UPDATE}	{finance_role}
salary	numeric	{SELECT}	{hr_role,manager}

8. 默认值表达式解析

默认值可能包含函数调用和复杂表达式：

SELECT
    column_name,
    data_type,
    column_default,
    pg_get_expr(ad.adbin, ad.adrelid) AS parsed_expression,
    CASE 
        WHEN column_default LIKE 'nextval%' THEN '序列生成器'
        WHEN column_default LIKE '%::%' THEN '类型转换表达式'
        WHEN column_default ~ '\(.*\)' THEN '函数调用'
        ELSE '常量值'
    END AS default_type
FROM
    information_schema.columns c
    LEFT JOIN pg_attrdef ad ON ad.adrelid = 
        (SELECT oid FROM pg_class WHERE relname = c.table_name)
        AND ad.adnum = 
        (SELECT attnum FROM pg_attribute 
         WHERE attname = c.column_name AND attrelid = 
            (SELECT oid FROM pg_class WHERE relname = c.table_name))
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'system_config'
    AND column_default IS NOT NULL;

9. 跨表列特征对比分析

比较多个表的相似列特征：

WITH column_stats AS (
    SELECT
        table_name,
        column_name,
        data_type,
        character_maximum_length,
        numeric_precision,
        is_nullable,
        COUNT(*) OVER (PARTITION BY column_name, data_type) AS same_def_count
    FROM
        information_schema.columns
    WHERE
        table_schema = 'public'
        AND table_name IN ('customers', 'suppliers', 'employees')
)
SELECT
    column_name,
    data_type,
    string_agg(table_name, ', ' ORDER BY table_name) AS tables_using,
    MAX(character_maximum_length) AS max_length,
    MIN(character_maximum_length) AS min_length,
    same_def_count,
    CASE 
        WHEN COUNT(DISTINCT is_nullable) > 1 THEN '不一致'
        ELSE MIN(is_nullable)
    END AS nullability
FROM
    column_stats
GROUP BY
    column_name, data_type, same_def_count
HAVING
    COUNT(*) > 1
ORDER BY
    same_def_count DESC, column_name;

10. 综合元数据报告模板

终极元数据查询模板，生成完整的表结构报告：

SELECT
    c.column_name,
    c.ordinal_position AS position,
    c.data_type,
    CASE 
        WHEN c.character_maximum_length IS NOT NULL THEN 
            c.character_maximum_length || ' chars'
        WHEN c.numeric_precision IS NOT NULL THEN 
            c.numeric_precision || 
            CASE WHEN c.numeric_scale > 0 THEN 
                ',' || c.numeric_scale ELSE '' END || 
            ' digits (base ' || c.numeric_precision_radix || ')'
        WHEN c.datetime_precision IS NOT NULL THEN 
            c.datetime_precision || ' fractional seconds'
        ELSE ''
    END AS size_precision,
    c.is_nullable,
    c.column_default,
    c.is_identity,
    c.identity_generation,
    c.is_generated,
    c.collation_name,
    tc.constraint_type,
    kcu.constraint_name
FROM
    information_schema.columns c
    LEFT JOIN information_schema.key_column_usage kcu 
        ON c.table_schema = kcu.table_schema
        AND c.table_name = kcu.table_name
        AND c.column_name = kcu.column_name
    LEFT JOIN information_schema.table_constraints tc
        ON kcu.constraint_name = tc.constraint_name
        AND kcu.table_schema = tc.table_schema
WHERE
    c.table_schema = 'public'
    AND c.table_name = 'inventory'
ORDER BY
    c.ordinal_position;

将此查询结果导出为CSV或HTML，即可生成专业的数据库文档。