查询information_schema.columns时ltree类型显示为USER-DEFINED的解决方法

因为ltree是PostgreSQL的扩展类型，属于用户自定义类型范畴，information_schema.columns的data_type字段对这类类型会统一返回USER-DEFINED。要获取实际的类型名称，需要关联系统表pg_catalog.pg_type来查询：

SELECT 
    column_name,
    CASE 
        WHEN data_type = 'USER-DEFINED' THEN t.typname
        ELSE data_type
    END AS data_type
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_type t 
    ON c.udt_name = t.typname
WHERE table_name = 'contact';

说明： #

• udt_name字段存储了类型的内部名称，和pg_type表的typname字段一一对应
• 通过CASE判断逻辑，对标记为USER-DEFINED的类型替换为实际的类型名，其他内置类型则保持原有显示

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