Node pg update multiple rows in one query (error: operator does not exist: text = integer)

Issue

I have the following query:

UPDATE test_schema.test_table as t SET
  test_row_value2 = c.test_row_value2
FROM (values
  ($1, $2, $3), ($1, $4, $5)
) as c(id, test_row_value1, test_row_value2)
WHERE c.id = t.id
  AND c.test_row_value1 = t.test_row_value1
RETURNING *

My parameters are the following:

[
  72,
  'table_name',
  'test_table',
  'database_name',
  'test_database'
]

When I run the query using node pg (version 8.7.3), I get the following error:

error: operator does not exist: text = integer

From my testing, I figured out it’s due to the following lines:

test_row_value2 = c.test_row_value2
c.id = t.id
c.test_row_value1 = t.test_row_value1

If I parameterize the right side of the above lines, I don’t get the error but nothing gets updated either. I ran the query with hardcoded values in PGAdmin and the query works fine and everything updates correctly. What am I doing wrong in node pg?

Solution

I had to change this part of the query:

c.id = t.id

to the following:

c.id = t.id::int

Notice the ::int part. I’m not sure why node pg requires the cast but everything seems to be working fine now.

Answered By – o.o

Answer Checked By – Terry (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.