1
query := "WITH b(ColA, ColB) AS (VALUES ($1,$2)) UPDATE schema_name.table_name AS a SET ColC = b.ColB FROM b WHERE a.ColA = b.ColA AND a.ColB = b.ColB"
res, err := db.Exec(query, 1, 1)

The above code fails with the following error:

pq: operator does not exist: bigint = text

"ColC" is of type BIGINT.

From my investigation, the driver is inserting the values as text instead of ints.

issue: https://github.com/lib/pq/issues/582

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Abhishek
  • 432
  • 5
  • 19
  • b.ColA and b.ColB are bigint? – Vao Tsun Mar 16 '17 at 10:53
  • 1
    Did you try adding explicit type casts? e.g. `query := "WITH b(ColA, ColB) AS (VALUES ($1::bigint,$2::bigint)) UPDATE schema_name.table_name AS a SET ColC = b.ColB FROM b WHERE a.ColA = b.ColA AND a.ColB = b.ColB" res, err := db.Exec(query, 1, 1)` – mkopriva Mar 16 '17 at 11:25
  • Yes explicit casting works but defeat the purpose of the driver inferring the type of the argument and writing to DB accordingly – Abhishek Mar 16 '17 at 11:33
  • @VaoTsun b is a temp table built with the injected values which are ints. – Abhishek Mar 16 '17 at 11:45
  • just to be sure, populate the result of `select pg_typeof(b.ColA),pg_typeof(b.ColB) from b` – Vao Tsun Mar 16 '17 at 11:49
  • @VaoTsun b is not a concrete table. It is a temp table built as part of the query mentioned in the question. – Abhishek Mar 16 '17 at 11:51
  • `WITH b(ColA, ColB) AS (VALUES ($1,$2)) select pg_typeof(b.ColA),pg_typeof(b.ColB) from b` please run it with same prepared values as you have error with – Vao Tsun Mar 16 '17 at 11:57
  • @Abhishek You were using this driver for explicit type inferences? – Benjamin Kadish Mar 16 '17 at 12:02

1 Answers1

2

It turns out to be the postgres behavior and not the driver. I had to use explicit casts to get my queries to work.

Abhishek
  • 432
  • 5
  • 19