PostgreSQL中如何检查表字段是否存在于自定义类型并生成新表？

需求：对比现有表test_table的字段与自定义类型test_type的属性，仅保留类型中存在的字段，以此创建新表。

原代码存在的问题 #

原代码中IF r.colname in test_type THEN的写法不符合PostgreSQL语法，无法直接判断字段是否属于自定义类型，需要先从系统表中获取自定义类型的字段列表再进行匹配。

正确实现方案 #

以下是完整的实现代码，会自动筛选符合条件的字段并创建新表（示例中新表名为new_test_table）：

-- 创建自定义类型和测试表
CREATE TYPE test_type AS (v1 int, v2 int, v3 int); 
CREATE TABLE test_table (v1 int, v2 int, v3 int, v4 int);

DO 
$$ 
DECLARE     
    type_fields text[];
    create_sql text;
BEGIN   
    -- 第一步：获取自定义test_type的所有字段名
    SELECT array_agg(attname)
    INTO type_fields
    FROM pg_attribute
    WHERE attrelid = 'test_type'::regtype
      AND attnum > 0
      AND NOT attisdropped;

    -- 第二步：动态生成创建新表的SQL语句，仅保留类型中存在的字段
    SELECT 'CREATE TABLE new_test_table AS SELECT ' || string_agg(quote_ident(colname), ', ') || ' FROM test_table'
    INTO create_sql
    FROM information_schema.columns 
    WHERE table_name = 'test_table' 
      AND table_schema = current_schema()
      AND column_name = ANY(type_fields);

    -- 第三步：执行动态SQL创建新表
    EXECUTE create_sql;
    RAISE NOTICE '新表new_test_table已创建，包含字段：%', array_to_string(type_fields, ', ');
END; 
$$ 
LANGUAGE plpgsql;

代码说明 #

• 获取自定义类型字段：通过pg_attribute系统表查询test_type的所有有效字段，存储到数组type_fields中。
• 生成创建表SQL：查询原表test_table中属于type_fields的字段，拼接成CREATE TABLE AS SELECT语句。
• 执行动态SQL：使用EXECUTE执行生成的SQL，完成新表创建。

验证结果 #

执行完代码后，查询新表结构：

\d new_test_table;

会看到新表仅包含v1、v2、v3三个字段，符合需求。

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