PostgreSQL如何查询库中所有datetime类型字段及对应内容

可以实现，PostgreSQL无原生跨表同类型字段一键查询语法，可通过系统表遍历目标字段，动态拼接查询语句完成需求。

注意：PostgreSQL中没有命名为datetime的字段类型，通用时间类型包括timestamp（无时区时间戳）、timestamptz（带时区时间戳）、date（日期）、time（时间），可按需调整过滤规则。

实现步骤 #

1. 执行以下语句生成动态查询SQL

SELECT string_agg(
    format(
        'SELECT %L AS field_name, %L AS type_field, %I AS data, %L AS "table" FROM %I.%I',
        a.attname,
        t.typname,
        a.attname,
        c.relname,
        n.nspname,
        c.relname
    ),
    ' UNION ALL '
) AS query_sql
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE 
    -- 排除系统内置schema
    n.nspname NOT IN ('pg_catalog', 'information_schema')
    -- 仅查询普通业务表，排除视图、索引等对象
    AND c.relkind = 'r'
    -- 过滤目标时间类型，可按需调整类型列表
    AND t.typname IN ('timestamp', 'timestamptz', 'date', 'time')
    -- 排除已标记删除的无效字段
    AND a.attisdropped = false
    AND a.attnum > 0;

2. 复制上述语句输出的query_sql字段内容直接执行，即可得到符合要求的结果集，返回字段对应关系如下：
   • field_name：时间字段名
   • type_field：字段的数据类型
   • data：字段实际存储的时间值
   • table：字段所属的表名
     结果格式和给出的示例完全一致。

注意事项 #

• 若业务表数据量较大，拼接生成的SQL会全表扫描所有匹配的业务表，执行前请评估性能影响，不要直接在生产环境无评估执行
• 若仅需查询指定schema下的表，可在WHERE条件中增加n.nspname = '目标schema名称'缩小查询范围
• 若仅需匹配特定时间类型，修改t.typname IN (...)中的类型列表即可

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