Bulk INSERT in Postgres in GO using pgx

Question

I am trying to bulk insert keys in db in go here is the code Key Struct

type tempKey struct {
keyVal  string
lastKey int

}

Test Keys

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

Insertion part

dbUrl := "db url...."
conn, err := pgx.Connect(context.Background(), dbUrl)
if err != nil {
    println("Errrorr...")
}
defer conn.Close(context.Background())
sqlStr := "INSERT INTO keys (keyval,lastval) VALUES "
dollars := ""
vals := []interface{}{}
count := 1
for _, row := range data {
    dollars = fmt.Sprintf("%s($%d, $%d),", dollars, count, count+1)
    vals = append(vals, row.keyVal, row.lastKey)
    count += 2
}
sqlStr += dollars
sqlStr = sqlStr[0 : len(sqlStr)-1]
fmt.Printf("%s \n", sqlStr)

_, erro := conn.Exec(context.Background(), sqlStr, vals)
if erro != nil {
    fmt.Fprint(os.Stderr, "Error : \n", erro)
}

on running it throws error: expected 10 arguments, got 1

What is the correct way of bulk inserting.

colm.anseo · Accepted Answer · 2022-01-23 15:04:03Z

26

You are crafting the SQL statement by hand, which is fine, but you are not leveraging pgx which can help with this (see below).

Appending to the SQL string like so can be inefficient for large inputs

dollars = fmt.Sprintf("%s($%d, $%d),", dollars, count, count+1)

but also the final value has a trailing , where instead you need a termination character ; to indicate the end of the statement.

BTW this string truncation line is redundant:

sqlStr = sqlStr[0 : len(sqlStr)-1] // this is a NOOP

Anyway, better to use something more performant like strings.Builder when crafting long strings.

From the pgx docs, use pgx.Conn.CopyFrom:

func (c *Conn) CopyFrom(tableName Identifier, columnNames []string, rowSrc CopyFromSource) (int, error)

CopyFrom uses the PostgreSQL copy protocol to perform bulk data insertion. It returns the number of rows copied and an error.

example usage of Copy:

rows := [][]interface{}{
    {"John", "Smith", int32(36)},
    {"Jane", "Doe", int32(29)},
}

copyCount, err := conn.CopyFrom(
    pgx.Identifier{"people"},
    []string{"first_name", "last_name", "age"},
    pgx.CopyFromRows(rows),
)

answered Jan 23, 2022 at 15:04

colm.anseo

22.7k5 gold badges50 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AnswerRex Over a year ago

Thanks for additional info also..... I wasn't aware of Copy til now....made it worked

serge-v Over a year ago

@AnswerRex it can be a PK violation on CopyFrom depending on tables structure. If it is your case then create temp table on session, do bulk insert there and then do select to maintable from temp on conflict do nothing. See details in: stackoverflow.com/questions/13947327/…

serge-v · Accepted Answer · 2022-01-23 14:54:00Z

15

use batch (https://github.com/jackc/pgx/blob/master/batch_test.go):

batch := &pgx.Batch{}
batch.Queue("insert into ledger(description, amount) values($1, $2)", "q1", 1)
batch.Queue("insert into ledger(description, amount) values($1, $2)", "q2", 2)
br := conn.SendBatch(context.Background(), batch)

answered Jan 23, 2022 at 14:54

serge-v

7884 silver badges8 bronze badges

3 Comments

AnswerRex Over a year ago

Thanks....Its simple and worked like charm but I have doubt does this have any performance hits from the answer of @colm.anseo.

serge-v Over a year ago

yes. It slower than CopyFrom.

serge-v Over a year ago

Batch is slower than CopyFrom. But the usage depends on business case. If you have different inserts into multiple tables then it probably make sense to use batch. If you need maximum insertion rate into single table, then of course CopyFrom.

Collectives™ on Stack Overflow

Bulk INSERT in Postgres in GO using pgx

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related