How to Efficiently Perform Bulk Inserts in Postgres with Go Using pgx?

Bulk INSERT in Postgres in Go Using pgx: Resolved

In the given Go code, you're manually crafting an INSERT statement and executing it using pgx.Exec. However, this approach can be inefficient and prone to errors, especially for bulk insertions.

Issue Identification

The error message "expected 10 arguments, got 1" indicates a mismatch between the number of arguments provided and those expected by the database. This error arises because you're manually constructing the INSERT statement and might have made a mistake in the count or sequence of arguments.

Correct Approach

To perform bulk insertions efficiently, leverage pgx's CopyFrom function, which utilizes the PostgreSQL copy protocol designed for such operations. CopyFrom allows you to bulk insert rows from a source into a table.

Code Example Using CopyFrom

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v4"
)

const (
    dbUrl = "db url..."
)

type tempKey struct {
    keyVal  string
    lastKey int
}

func main() {
    conn, err := pgx.Connect(context.Background(), dbUrl)
    if err != nil {
        log.Fatalf("Error connecting to the database: %v", err)
    }
    defer conn.Close(context.Background())

    data := []tempKey{
        {keyVal: "abc", lastKey: 10},
        {keyVal: "dns", lastKey: 11},
        {keyVal: "qwe", lastKey: 12},
        {keyVal: "dss", lastKey: 13},
        {keyVal: "xcmk", lastKey: 14},
    }

    copyCount, err := conn.CopyFrom(
        context.Background(),
        pgx.Identifier{"keys"}, // Table name
        []string{"keyval", "lastval"}, // Column names
        pgx.CopyFromRows(data), // Copy from a slice of structs
    )

    if err != nil {
        log.Fatalf("Error copying data to the database: %v", err)
    }

    fmt.Printf("%d rows copied successfully.\n", copyCount)
}

The above is the detailed content of How to Efficiently Perform Bulk Inserts in Postgres with Go Using pgx?. For more information, please follow other related articles on the PHP Chinese website!