如何在pgx（通过sqlc）中使用带WHERE条件的copyfrom功能？

可以实现你的需求，但需要注意：PostgreSQL原生的COPY FROM命令不支持直接添加WHERE条件，没法在批量导入阶段直接过滤重复数据。不过可以通过「临时表中转 + 带条件的INSERT」的组合方式来达成目标，完全适配你给出的重复判断规则。

具体实现步骤 #

1. 创建临时表：结构和目标表asns完全一致，用于临时存储批量导入的数据
2. 用pgx CopyFrom批量导入数据到临时表：利用CopyFrom的高性能完成批量数据写入
3. 从临时表向目标表插入数据：带上你的重复判断条件，仅插入目标表中不存在的记录

代码示例 #

1. sqlc查询定义（在schema.sql中）

-- name: CreateTempASNsTable :exec
CREATE TEMP TABLE temp_asns (
    merchantId TEXT,
    return_provider TEXT,
    barcode TEXT,
    carrier TEXT,
    tracking_number TEXT,
    customer_email TEXT,
    order_id TEXT,
    order_name TEXT,
    order_number TEXT,
    return_line_item_id TEXT,
    rma TEXT,
    sku TEXT
) ON COMMIT DROP;

-- name: BulkCopyToTempASNs :copyfrom
COPY temp_asns (merchantId, return_provider, barcode, carrier, tracking_number, customer_email, order_id, order_name, order_number, return_line_item_id, rma, sku)
FROM STDIN;

-- name: InsertFromTempToASNs :exec
INSERT INTO asns (merchantId, return_provider, barcode, carrier, tracking_number, customer_email, order_id, order_name, order_number, return_line_item_id, rma, sku)
SELECT merchantId, return_provider, barcode, carrier, tracking_number, customer_email, order_id, order_name, order_number, return_line_item_id, rma, sku
FROM temp_asns
WHERE NOT EXISTS (
    SELECT 1
    FROM asns
    WHERE asns.merchantId IS NOT DISTINCT FROM temp_asns.merchantId
    AND asns.return_provider IS NOT DISTINCT FROM temp_asns.return_provider
    AND asns.barcode IS NOT DISTINCT FROM temp_asns.barcode
    AND asns.carrier IS NOT DISTINCT FROM temp_asns.carrier
    AND asns.tracking_number IS NOT DISTINCT FROM temp_asns.tracking_number
    AND asns.customer_email IS NOT DISTINCT FROM temp_asns.customer_email
    AND asns.order_id IS NOT DISTINCT FROM temp_asns.order_id
    AND asns.order_name IS NOT DISTINCT FROM temp_asns.order_name
    AND asns.order_number IS NOT DISTINCT FROM temp_asns.order_number
    AND asns.return_line_item_id IS NOT DISTINCT FROM temp_asns.return_line_item_id
    AND asns.rma IS NOT DISTINCT FROM temp_asns.rma
    AND asns.sku IS NOT DISTINCT FROM temp_asns.sku
);

2. Go代码实现（使用sqlc生成的客户端）

import (
    "context"
    "your-project/db/sqlc"
    "github.com/jackc/pgx/v5"
)

func BulkInsertASNs(ctx context.Context, db *pgx.Conn, q *sqlc.Queries, asns []sqlc.ASN) error {
    // 1. 创建临时表
    err := q.CreateTempASNsTable(ctx, db)
    if err != nil {
        return err
    }

    // 2. 构造CopyFrom所需的行数据
    rows := make([][]interface{}, len(asns))
    for i, a := range asns {
        rows[i] = []interface{}{
            a.MerchantId, a.ReturnProvider, a.Barcode, a.Carrier,
            a.TrackingNumber, a.CustomerEmail, a.OrderId, a.OrderName,
            a.OrderNumber, a.ReturnLineItemId, a.Rma, a.Sku,
        }
    }

    // 3. 批量导入到临时表
    _, err = q.BulkCopyToTempASNs(ctx, db, pgx.CopyFromRows(rows))
    if err != nil {
        return err
    }

    // 4. 从临时表插入目标表，应用重复过滤条件
    return q.InsertFromTempToASNs(ctx, db)
}

性能优化建议 #

如果你的重复判断字段组合可以作为唯一标识，建议给asns表创建唯一约束，这样可以把INSERT语句改成ON CONFLICT DO NOTHING，性能会比WHERE NOT EXISTS更优：

ALTER TABLE asns ADD CONSTRAINT asns_unique_key UNIQUE (
    merchantId, return_provider, barcode, carrier, tracking_number, 
    customer_email, order_id, order_name, order_number, return_line_item_id, 
    rma, sku
);

对应的插入语句修改为：

-- name: InsertFromTempToASNs :exec
INSERT INTO asns (merchantId, return_provider, barcode, carrier, tracking_number, customer_email, order_id, order_name, order_number, return_line_item_id, rma, sku)
SELECT merchantId, return_provider, barcode, carrier, tracking_number, customer_email, order_id, order_name, order_number, return_line_item_id, rma, sku
FROM temp_asns
ON CONFLICT DO NOTHING;

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