Postgres Upsert - Syntax Error

I am trying to implement an upsert for a Postgres node, using the following code:

INSERT INTO return VALUES ({{$node["Map Keys RETURNS"].json["id"]}},'{{$node["Map Keys RETURNS"].json["rrn"]}}',{{$node["Map Keys RETURNS"].json["order_id"]}},{{$node["Map Keys RETURNS"].json["order_item_id"]}},{{$node["Map Keys RETURNS"].json["product_id"]}},'{{$node["Map Keys RETURNS"].json["date_created"]}}','{{$node["Map Keys RETURNS"].json["status"]}}')
ON CONFLICT (id) DO
UPDATE SET rrn = EXCLUDED.rrn, order_id = EXCLUDED.order_id, order_item_id = EXCLUDED.order_item_id, product_id = EXCLUDED.product_id, date_created = EXCLUDED.date_created, status = EXCLUDED.status;

But, on execution, I keep getting the following exception:

error: syntax error at or near ","
    at Connection.parseE (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/node_modules/pg/lib/connection.js:604:11)
    at Connection.parseMessage (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/node_modules/pg/lib/connection.js:401:19)
    at Socket.<anonymous> (/usr/local/lib/node_modules/n8n/node_modules/pg-promise/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:310:20)
    at addChunk (_stream_readable.js:286:12)
    at readableAddChunk (_stream_readable.js:268:9)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

Pasting the rendered query result into a SQL client, executes the query correctly.

Also, if I change the Operation type to an INSERT, the node executes successfully - but, then I cannot upsert.

@go4cas have not had the change to get to this. Will try to check it out later today. Will keep you posted.

Thanks, @RicardoE105. It seems to be a general parsing issue with the PG client lib. Here’s another example of a straightfoward INSERT, which throws ‘syntax error at or near “m”’ error:

INSERT INTO student (id, name, contact_primary, address, student_type) VALUES ({{$node["Load STUDENTS"].json["id"]}},'{{$node["Load STUDENTS"].json["name_first"]}}','{{$node["Load STUDENTS"].json["contact_primary"]}}','{{$node["Load STUDENTS"].json["address"]}}','{{$node["Load STUDENTS"].json["student_type"]}}');

Okay the second example I fixed by replacing the single quotes (’) with dollar quotes ($$). I’ll test the upsert again, to see if this would resolved it.

@Daniel_Barra did you figure it out?

Yeah. It was my mistake (forgot ’ ’ in one of the parameters of the query)
Didn’t want to bother, so I deleted. Thanks anyway.

1 Like

@RicardoE105 disregard. Figured it out again lol