登录
首页 >  Golang >  Go问答

pgx copyfrom 带 WHERE 条件

来源:stackoverflow

时间:2024-04-14 08:18:39 295浏览 收藏

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

问题内容

我想使用 pgx (通过 sqlccopyfrom 功能和 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学习网公众号,带你了解更多关于的知识点!

声明:本文转载于:stackoverflow 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>