pgx copyfrom 带 WHERE 条件

最近发现不少小伙伴都对Golang很感兴趣，所以今天继续给大家介绍Golang相关的知识，本文《pgx copyfrom 带 WHERE 条件》主要内容涉及到等等知识点，希望能帮到你！当然如果阅读本文时存在不同想法，可以在评论中表达，但是请勿使用过激的措辞~

我想使用 pgx （通过 sqlc）copyfrom 功能和 copy from where 条件，如下所示：

（此条件取自 insert 上的工作规则）：

WHERE (EXISTS ( SELECT *
                FROM asns
                WHERE merchantId IS NOT DISTINCT FROM NEW.merchantId
                AND return_provider IS NOT DISTINCT FROM NEW.return_provider
                AND barcode IS NOT DISTINCT FROM NEW.barcode
                AND carrier IS NOT DISTINCT FROM NEW.carrier
                AND tracking_number IS NOT DISTINCT FROM NEW.tracking_number
                AND customer_email IS NOT DISTINCT FROM NEW.customer_email
                AND order_id IS NOT DISTINCT FROM NEW.order_id
                AND order_name IS NOT DISTINCT FROM NEW.order_name
                AND order_number IS NOT DISTINCT FROM NEW.order_number
                AND return_line_item_id IS NOT DISTINCT FROM NEW.return_line_item_id
                AND rma IS NOT DISTINCT FROM NEW.rma
                AND sku IS NOT DISTINCT FROM NEW.sku)) DO INSTEAD NOTHING;

有没有办法在 pgx 中做到这一点？谢谢。

正确答案

是的，有一种方法可以做到这一点。可以使用

func (pgconn *pgconn) copyfrom(ctx context.context, r io.reader, sql string) (commandtag, error)。

您可以在测试用例中找到使用示例：

func TestConnCopyFrom(t *testing.T) {
    t.Parallel()

    pgConn, err := pgconn.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
    require.NoError(t, err)
    defer closeConn(t, pgConn)

    if pgConn.ParameterStatus("crdb_version") != "" {
        t.Skip("Server does not fully support COPY FROM (https://www.cockroachlabs.com/docs/v20.2/copy-from.html)")
    }

    _, err = pgConn.Exec(context.Background(), `create temporary table foo(
        a int4,
        b varchar
    )`).ReadAll()
    require.NoError(t, err)

    srcBuf := &bytes.Buffer{}

    inputRows := [][][]byte{}
    for i := 0; i < 1000; i++ {
        a := strconv.Itoa(i)
        b := "foo " + a + " bar"
        inputRows = append(inputRows, [][]byte{[]byte(a), []byte(b)})
        _, err = srcBuf.Write([]byte(fmt.Sprintf("%s,\"%s\"\n", a, b)))
        require.NoError(t, err)
    }

    ct, err := pgConn.CopyFrom(context.Background(), srcBuf, "COPY foo FROM STDIN WITH (FORMAT csv)")
    require.NoError(t, err)
    assert.Equal(t, int64(len(inputRows)), ct.RowsAffected())

    result := pgConn.ExecParams(context.Background(), "select * from foo", nil, nil, nil, nil).Read()
    require.NoError(t, result.Err)

    assert.Equal(t, inputRows, result.Rows)

    ensureConnValid(t, pgConn)
}

只需使用 where 语句扩展 copy foo from stdin ... 即可。

到这里，我们也就讲完了《pgx copyfrom 带 WHERE 条件》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于的知识点！