Node.js操作PostgreSQL插入数据去重：Azure函数代码优化求助

PostgreSQL中INSERT...ON CONFLICT（即UPSERT）是解决重复插入问题的最优方案（兼容性优于仅PostgreSQL 15+支持的MERGE），核心是通过唯一约束识别冲突，冲突时更新现有数据而非重复插入。

前置准备：添加唯一约束 #

首先要确定业务唯一标识，比如假设project_prefix + material_code是唯一确定一条库存记录的组合，需给表添加联合唯一约束：

ALTER TABLE stock2.stock 
ADD CONSTRAINT unique_stock_key UNIQUE (project_prefix, material_code);

如果表已有单独主键或其他唯一字段，替换成对应字段即可。

修改后的代码 #

优化点：

• 替换单条INSERT为UPSERT语句
• 改用批量插入（避免10000次循环调用数据库，大幅提升性能）
• 移除无意义的全表查询，优化日志输出

const { app } = require('@azure/functions');
const { Client } = require('pg');
const XLSX = require('xlsx');

app.storageBlob('storageBlobTrigger', {
    path: 'attachments/{name}',
    connection: 'MyStorageConnectionString',
    handler: async (blob, context) => {
        const connection = new Client({
            host: "++++++",
            user: "+++++",
            password: "++++",
            database: "++",
            port: 5432,
            ssl: {
                rejectUnauthorized: false
            }
        });
        try {
            await connection.connect();
            context.log(`Storage blob function processed blob "${context.triggerMetadata.name}" with size ${blob.length} bytes`);

            // 解析Excel文件并跳过表头
            const workbook = XLSX.read(blob, { type: 'buffer' });
            const sheetName = workbook.SheetNames[0];
            const sheet = workbook.Sheets[sheetName];
            const range = XLSX.utils.decode_range(sheet['!ref']);
            range.s.r = 1;
            sheet['!ref'] = XLSX.utils.encode_range(range);

            const data = XLSX.utils.sheet_to_json(sheet, { raw: false });
            context.log(`Parsed ${data.length} rows from Excel`);

            if (data.length === 0) {
                context.log("No data to process");
                return;
            }

            // 构建批量UPSERT语句
            const columns = [
                'project_prefix', 'material_code', 'producer_item_code',
                'description', 'serial_numbers', 'total_stock', 'unit', 'warehouse'
            ];
            const placeholders = data.map((_, idx) => 
                `($${idx*8+1}, $${idx*8+2}, $${idx*8+3}, $${idx*8+4}, $${idx*8+5}, $${idx*8+6}, $${idx*8+7}, $${idx*8+8})`
            ).join(', ');

            const values = data.flatMap(row => [
                row['Project Prefix'],
                row['Material Code'],
                row['Producer Item Code'],
                row['Description'],
                row['Serial Numbers'],
                row['Total Stock'],
                row['Unit'],
                row['Warehouse']
            ]);

            const query = `
                INSERT INTO stock2.stock (${columns.join(', ')})
                VALUES ${placeholders}
                ON CONFLICT (project_prefix, material_code) DO UPDATE SET
                    producer_item_code = EXCLUDED.producer_item_code,
                    description = EXCLUDED.description,
                    serial_numbers = EXCLUDED.serial_numbers,
                    total_stock = EXCLUDED.total_stock,
                    unit = EXCLUDED.unit,
                    warehouse = EXCLUDED.warehouse
            `;

            // 执行批量操作
            const result = await connection.query(query, values);
            context.log(`Successfully processed ${result.rowCount} rows (inserted/updated)`);

        } catch (error) {
            context.error("Error processing data:", error);
        } finally {
            await connection.end();
        }
    }
});

关键说明 #

1. UPSERT逻辑：当插入记录与现有记录的唯一约束冲突时，会用新数据更新指定字段（EXCLUDED代表待插入的新数据）
2. 批量操作：将10000行数据一次性提交给数据库，比循环单条插入效率提升数倍
3. 日志规范：用context.log替代console.log，适配Azure Functions的日志体系
4. 空数据校验：避免无数据时执行无效的数据库操作

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