PostgreSQL如何获取ENUM[]类型列的所有可选枚举值

背景 #

常规通过information_schema.columns查询字段类型的方式无法获取枚举数组(ENUM[])列的底层枚举可选值，该表的data_type字段针对数组类型只会返回ARRAY，需要通过查询PostgreSQL系统表实现需求。

实现查询 #

你可以直接使用如下SQL脚本，替换其中的schema名、表名、列名后执行，即可直接得到对应枚举数组列的所有可选枚举值：

SELECT enumlabel AS enum_optional_value
FROM pg_enum
WHERE enumtypid = (
    -- 获取枚举数组列的底层枚举类型OID
    SELECT elem_type.oid
    FROM pg_attribute col
    JOIN pg_type arr_type ON col.atttypid = arr_type.oid
    JOIN pg_type elem_type ON arr_type.typelem = elem_type.oid
    JOIN pg_class tbl ON col.attrelid = tbl.oid
    JOIN pg_namespace sch ON tbl.relnamespace = sch.oid
    WHERE
        sch.nspname = 'your_schema_name' -- 替换为实际schema，默认public
        AND tbl.relname = 'your_table_name' -- 替换为实际表名
        AND col.attname = 'your_enum_array_column' -- 替换为实际枚举数组列名
        AND col.attisdropped = FALSE -- 过滤已删除的字段
);

逻辑说明 #

• 系统表pg_attribute存储了所有表字段的基础元数据，通过schema名、表名、列名可定位到目标字段的类型OID
• 系统表pg_type存储了所有数据类型的元数据，数组类型的typelem字段存储了数组元素对应类型的OID，由此可拿到枚举数组的底层枚举类型OID
• 系统表pg_enum存储了所有枚举类型的可选值，通过枚举类型OID过滤即可得到所有可选枚举值

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