I'm running against a problem running this query in Node using node-postgres. It runs perfectly in psql. Here it is:
insert into list_items (dataitems_id, idx, lists_id) select li.dataitems_id, nextval('dadac'),(select $1 as lists_id)
from list_items li where li.lists_id in
(select da.referenced_li_id from dataitems_attributes_li da where da.dataitems_id in
(select d.id from dataitems d, dataitems_attributes da where d.datatype='product' and da.dataitems_id=d.id and da.key='summary' and da.value in ($2,$3,$4,$5))
and da.key='products_by_colour')
with parameters:
[ 12423,
'BLACK CATS',
'CATS BLACK',
'BLACK',
'CATS' ]
The schema states that
dataitems.id=bigint,
dataitems_attributes.dataitems_id=bigint(fk),
dataitems_attributes_li.referenced_li_id=bigint(fk),
list_items.lists_id=bigint(fk)
I am getting a "type coercion" error from the pg driver:
{ [error: failed to find conversion function from unknown to bigint]\n name: 'error',\n length: 112,\n severity: 'ERROR',\n code: 'XX000',\n detail: undefined,\n hint: undefined,\n position: undefined,\n internalPosition: undefined,\n internalQuery: undefined,\n where: undefined,\n file: 'parse_coerce.c',\n line: '476',\n routine: 'coerce_type' }
I tried forcefully coercing returns from inner queries to ::bigint but this did not help. Here is my revised query:
insert into list_items (dataitems_id, idx, lists_id) select li.dataitems_id, nextval('ebebccd'),(select 12423 as lists_id)::bigint from list_items li where li.lists_id in
(select da.referenced_li_id::bigint from dataitems_attributes_li da where da.dataitems_id in
(select d.id::bigint from dataitems d, dataitems_attributes da where d.datatype='product' and da.dataitems_id=d.id and da.key='summary'
and da.value in ($2,$3,$4,$5)) and da.key='products_by_colour');
Any ideas what to do short of using a stored procedure to circumvent this behaviour of the driver?