Syntax error in postgres in insert query

Describe the issue/error/question

I get a syntax error on postgres node when trying to run the following sql query :

with insert_to_entity_list as (
insert into staging_new_3.inventory_entity_list(code,status,created_at,updated_at)	
values ('{{$json["code"]}}','{{$json["status"]}}','{{$json["created_at"]}}','{{$json["updated_at"]}}')	
returning code, id as entity_id
), insert_to_product as ( insert into staging_new_3.inventory_product(sellmore,entity_id, brand_id, warehouse_id, name, status, ean, hsn, description, cancellation, tax_percentage, created_at, updated_at, mrp, edited_at, hide_on_app, key_features, guarantee_offered, serviceable_by, hot_deal, seller_id, product_details, purchase_price,"brandPrice") values({{$json["sellmore"]}},(select entity_id from insert_to_entity_list),{{$json["brand_id"]}},{{$json["warehouse_id"]}},'{{$json["name"]}}','{{$json["status"]}}','{{$json["ean"]}}','{{$json["hsn"]}}','{{$json["description"]}}','{{$json["cancellation"]}}',{{$json["tax_percentage"]}},'{{$json["created_at"]}}','{{$json["updated_at"]}}',{{$json["mrp"]}},'{{$json["edited_at"]}}',{{$json["hide_on_app"]}},'{{$json["key_features"]}}','{{$json["guarantee_offered"]}}','{{$json["serviceable_by"]}}',{{$json["hot_deal"]}},{{$json["seller_id"]}},'{{$json["product_details"]}}',{{$json["purchase_price"]}},{{$json["mrp"]}}) returning id as product_id ), link_product_category as ( insert into staging_new_3.inventory_product_category(entity_id, product_id, category_id) values((select entity_id from insert_to_entity_list),(select product_id from insert_to_product), {{$json["category_id"]}}) )
select code from insert_to_entity_list;

What is the error message (if any)?

error: syntax error at or near ","
    at Parser.parseErrorMessage (/usr/local/lib/node_modules/n8n/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/usr/local/lib/node_modules/n8n/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/usr/local/lib/node_modules/n8n/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/usr/local/lib/node_modules/n8n/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:526:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Can anyone please help me out here.
P.S : The result query comes on the bottom works well in dbeaver

Hi @Anubhav_Dubey, I am sorry to hear you’re having trouble here.

I assume the query you have shared is used inside an expression? My first guess is that one of the values you’re using in your expression resolves to an empty string so you have two commas , in a row.

Hi @MutedJam , there is no field that evaluates to an empty string - find the result below as shown in result in the postgres node :

with insert_to_entity_list as (
insert into staging_new_3.inventory_entity_list(code,status,created_at,updated_at)	
values ('48d663d3a45f5ced822081bb','A','2022-05-31T09:57:58.698Z','2022-05-31T09:57:58.699Z')	
returning code, id as entity_id
), insert_to_product as 
(insert into staging_new_3.inventory_product(sellmore,entity_id, brand_id, warehouse_id, name, status, ean, hsn, description, cancellation, tax_percentage, created_at, updated_at, mrp, edited_at, hide_on_app, key_features, guarantee_offered, serviceable_by, hot_deal, seller_id, product_details, purchase_price,"brandPrice") 
values(false,(select entity_id from insert_to_entity_list),258,319,'random product','A','890000000000','190','A delicious biscuit','Please refer to the return and replacement policy',18,'2022-05-31T09:57:58.698Z','2022-05-31T09:57:58.699Z',120,'2022-05-31T09:57:58.699Z',true,'[Object: null]','[Object: null]','All',false,1,'{"length" : "", "width" : "", "height" : "", "net_weight" : ""}',45.46,120) returning id as product_id ), 
link_product_category as 
( insert into staging_new_3.inventory_product_category(entity_id, product_id, category_id) values((select entity_id from insert_to_entity_list),(select product_id from insert_to_product), 416) )
select code from insert_to_entity_list;

Hi @MutedJam . can I get some resolution here?

Hey @Anubhav_Dubey, could you share your CREATE TABLE queries and provide an example workflow using which your problem could be reproduced? I don’t know when I’ll be able to look at this yet, but this would make it a lot easier.

@MutedJam this was my flow

Hey @Anubhav_Dubey,

Out of interest when you run that is it one of the last 2 postgres nodes that fails? are you also getting the error on every run or just a few?

the second last postgres node fails on every run @jon

While we are waiting for a create query to try and reproduce could you try the below workflow? The only change I have made is adding an If node so we can make sure there are no empty values, There has been a few times when a DB query will fail and while the UI looks like there is data an input item further down is missing something this should rule that out as a possible cause and it gives you a way to double check your data.

@jon , all the rows went into the true flow after the IF node you added, couple of fields were shown as [Object : null] in UI which were treated as empty here. Put a space instead of null as a fix for the fields and they go into the false flow now, but still the same error at the node.

Will share the create queries in a while.

here are the create table queries :

CREATE TABLE staging_new_3.inventory_entity_list (
	id serial4 NOT NULL,
	code varchar(64) NOT NULL,
	status bpchar(1) NOT NULL DEFAULT 'A'::character varying,
	created_at timestamptz NOT NULL,
	updated_at timestamptz NOT NULL,
	CONSTRAINT inventory_entity_list_code_unique UNIQUE (code),
	CONSTRAINT inventory_entity_list_pkey PRIMARY KEY (id)
);

CREATE TABLE staging_new_3.inventory_product (
	id serial4 NOT NULL,
	entity_id int4 NOT NULL,
	brand_id int4 NULL,
	warehouse_id int4 NULL,
	seller_id int4 NOT NULL,
	"name" varchar(255) NOT NULL,
	status varchar(1) NOT NULL DEFAULT 'A'::character varying,
	hide_on_app bool NOT NULL DEFAULT true,
	ean varchar(32) NULL,
	hsn varchar(32) NULL,
	description text NULL,
	key_features text NULL,
	guarantee_offered text NULL,
	cancellation text NULL,
	serviceable_by varchar(255) NULL,
	tax_percentage numeric(16, 4) NOT NULL,
	comission numeric(16, 4) NOT NULL DEFAULT '0'::numeric,
	mrp numeric(16, 4) NULL,
	"brandPrice" numeric(16, 4) NULL,
	"salePrice" numeric(16, 4) NULL,
	"baseCost" numeric(16, 4) NULL,
	created_at timestamptz NOT NULL,
	updated_at timestamptz NOT NULL,
	"preferredCost" numeric(16, 4) NULL,
	hot_deal bool NOT NULL DEFAULT false,
	edited_at timestamptz NULL,
	sellmore bool NOT NULL DEFAULT false,
	"type" varchar(32) NULL,
	product_details jsonb NULL,
	sys_period tstzrange NOT NULL DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone),
	purchase_price numeric NULL,
	CONSTRAINT inventory_product_pkey PRIMARY KEY (id)
);
CREATE TABLE staging_new_3.inventory_product_category (
	id serial4 NOT NULL,
	entity_id int4 NOT NULL,
	product_id int4 NOT NULL,
	category_id int4 NOT NULL,
	CONSTRAINT inventory_product_category_pkey PRIMARY KEY (id)
);

@jon were you able to reproduce this at your end?

Hey @Anubhav_Dubey,

I have not had a chance to look I was off last week, I will find out if @MutedJam had a chance to take a poke at it.

Sorry guys, for some reason I thought you were discussing something else.

Anyway, let me try to reproduce this by creating the example tables, inserting some dummy data and then running the problematic query using the Postgres node.

Hey @Anubhav_Dubey, I tried to give this a go but it seems your query is quite a painful one to set up manually so couldn’t finish this as I originally hoped.

So let’s simplify this a bit and rule a few possible causes.

Are you also seeing a problem when running your insert_to_entity_list outside of your full workflow (using some mock data rather than data coming from other database nodes)? If so, could you please share the JSON version of the mock data using which this can be reproduced?

Could you also try removing column by column from your query until you have found the offending one? In particular I wonder if this might be related to the jsonb column or another column possibly containing values with double curly braces.

Lastly, could you also verify if this behaviour still persists using query parameters instead of using expressions inside your query (which would be a good idea any as it closes a potential SQL injection attack vector). So instead of doing SELECT foo FROM bar WHERE baz = '{{$json["qux"]}}' do SELECT foo FROM bar WHERE baz = $1 and specify the value of $1 in the Query Parameters field like so:

image