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?
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.