1

I'm trying to refactor code so that I will connect to postgres DB using pgx, not pq. I've tried a lot of things, but I keep getting the ERROR: incorrect binary data format (SQLSTATE 22P03). Please find below both the original working code and my attempt at refactoring. What am I doing wrong?

// pq code
stmt, _ := txn.Prepare(pq.CopyIn(table, "id", "client_id", "channel_id", "feature"))
for _, user := range users{
    _, err = stmt.Exec(stringA, stringB, integer64, pq.Array(stringArray))
}

//pgx code
rows := make([][]interface{}, len(users))
ix := 0
for _, user := range users{
    rows[ix] = []interface{}{stringA, stringB, integer64, stringArray}
    ix++
}

_, err = txn.CopyFrom(context.Background(), pgx.Identifier{table},
        []string{"id", "client_id", "channel_id", "feature"}, pgx.CopyFromRows(rows))

EDIT: I got a hint from the pgx documentation: CopyFrom requires all values use the binary format.

The problem was my id (noted as stringA in the code above). It was created as:

stringA := uuid.New().String()

I augmented it with pgtype and now it works like a charm:

newStringA := &pgtype.UUID{}
_ := idPgtype.Set(stringA)
moratsam
  • 41
  • 3
  • Can you show the table's SQL definition? – mkopriva Jul 14 '21 at 06:29
  • I'd prefer not to; it's pretty huge + it's corporate. – moratsam Jul 14 '21 at 07:51
  • Just show the relevant code, i.e. the columns you're trying to copy from `(id,client_id,channel_id,feature)`. The types of those columns might be relevant to your problem. In general, if you're looking for help on SO, you should provide a [mcve]. – mkopriva Jul 14 '21 at 07:53
  • You are right, I didn't give enough code. I'll edit the question to include the solution. – moratsam Jul 15 '21 at 10:20
  • 3
    glad you fixed it, however,, the answer should be posted below, as an anwser. It should not appear within the question. –  Jul 15 '21 at 10:44

0 Answers0