如何修复Node-Postgres插入鸟科数据时的PostgreSQL 42601错误

问题场景 #

调用insertIntoB_familiestbl函数向bird_families表插入鸟科数据时，触发42601错误，错误提示：

error: INSERT has more expressions than target columns

相关代码 #

建表与插入函数

function createBirdFamilies_rel(){//bird_families table
    return db.query(`
    CREATE TABLE bird_families (
        family_id SERIAL PRIMARY KEY,
        scientific_fam_name VARCHAR(55) UNIQUE NOT NULL,
        f_description TEXT,
        clutch_size int4range, 
        habitats text ARRAY,
        predators text ARRAY,
        o_id INT REFERENCES bird_orders(order_id)
    )
    `)
}

function insertIntoB_familiestbl(swappedData){//insert into bird_families
    const formattedData = parseObjToNestedArr(swappedData)
    const queryStr = format(`
         INSERT INTO bird_families
         (scientific_fam_name,f_description,clutch_size,habitats,predators,o_id)
         VALUES
            %L
         RETURNING *;
         `,formattedData
    )
   return db.query(queryStr)
}

数据插入链式调用

const seeder = ({wing_shapeData}) =>{
     ...etc (previous code that works)
      .then(()=>{
        return insertIntoW_shapetbl(wing_shapeData)
      })
      .then(({rows})=>{
            const ref = createWingsRef(rows)
            const swappedData = addWIdToOrders(birdsOrdersData,ref)
            return insertIntoB_orderstbl(swappedData)
      }).then(({rows})=>{
            const ref = createOrdersRef(rows)
            const swappedData = addOIdToFamilies(birdsFamiliesData,ref)
            return insertIntoB_familiestbl(swappedData) // 错误触发位置
      }).catch((err)=>{
         console.log(err)
      })
}

测试数据示例

const bird_familesTD = [
    {
        scientific_fam_name: "Mesitornithidae",
        f_description: "These are a family of small near-flightless birds endemic to Madagascar. Every species in this family (white, brown and sub-desert) is threatened. They are vocal birds that produce passerine-like sounds for territorial defence",
        clutch_size : '[2,3]',
        habitats: ["dry forests","woodlands"],
        predators:["reptiles","larger mammals"],
        order:"Mesitornithiformes"
    },
    {
        scientific_fam_name: "Strigidae",
        f_description: "This is a large family that includes the True Owl found on all continents except Antarctica. 95% are forest-dwelling but most are non-migratory with rest migrating depending on the seasons. These owls have large heads, elongated eyes and a short hooked bill that point downwards. Many species have ear tufts that are suggested to be for behavioural functions. They have talons that are sharp and hooked and with a reversible fourth toes (zygodactyly). Like most owls they hunt in low-light conditions. When confronted with danger they will crouch down, lower their heads, drop their wings and ruffle their feathers.",
        clutch_size : '[3,4]',
        habitats: ["forests","tundras","deserts"],
        predators:["none"],
        order:"Strigiformes"
    }
]

错误原因 #

1. pg-format占位符使用错误：%L会将传入的二维数组整体转换为一个带引号的字符串，而非展开为多行VALUES的行结构，导致SQL中VALUES子句只有一个值，但目标列有6个，列数不匹配。
2. 数据字段冗余：addOIdToFamilies处理后的数据可能仍保留原order字段，parseObjToNestedArr转换时会将该字段也加入数组，导致每个数据行的元素数量多于目标列数。

解决方案 #

1. 修正insertIntoB_familiestbl函数 #

确保formattedData为二维数组，并正确使用pg-format的%L占位符生成多行VALUES：

function insertIntoB_familiestbl(swappedData){//insert into bird_families
    // 过滤出需要插入的字段，排除冗余字段
    const filteredData = swappedData.map(item => ({
        scientific_fam_name: item.scientific_fam_name,
        f_description: item.f_description,
        clutch_size: item.clutch_size,
        habitats: item.habitats,
        predators: item.predators,
        o_id: item.o_id
    }));
    // 转换为二维数组，每个子数组对应一行数据
    const formattedData = filteredData.map(obj => Object.values(obj));
    // 使用%L生成多行VALUES结构
    const queryStr = format(`
         INSERT INTO bird_families
         (scientific_fam_name,f_description,clutch_size,habitats,predators,o_id)
         VALUES
            %L
         RETURNING *;
         `, formattedData
    );
    // 可选：打印生成的SQL验证格式
    // console.log(queryStr);
   return db.query(queryStr)
}

2. 清理addOIdToFamilies函数的冗余字段 #

处理数据时移除原order字段，只保留插入所需的6个字段：

function addOIdToFamilies(data, ref) {
    return data.map(item => {
        const o_id = ref[item.order];
        // 解构赋值排除order字段，添加o_id
        const { order, ...rest } = item;
        return { ...rest, o_id };
    });
}

3. 验证生成的SQL #

执行前打印queryStr，确保生成的SQL格式正确，示例如下：

INSERT INTO bird_families
(scientific_fam_name,f_description,clutch_size,habitats,predators,o_id)
VALUES
    ('Mesitornithidae', 'These are a family...', '[2,3]', '{"dry forests","woodlands"}', '{"reptiles","larger mammals"}', 1),
    ('Strigidae', 'This is a large family...', '[3,4]', '{"forests","tundras","deserts"}', '{"none"}', 2)
RETURNING *;

额外注意事项 #

• clutch_size为int4range类型，传入'[2,3]'格式的字符串可被PostgreSQL自动解析为range类型，无需额外处理。
• habitats和predators为数组类型，pg-format会自动将JS数组转换为PostgreSQL兼容的数组格式（大括号包裹）。

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