Postgres Node - errors inserting data using Execute Query as well as using upsert

Describe the problem/error/question

Hello, we just recently started evaluating n8n and have been encountering some issues while using the Postgres node using Execute query operation as well as upsert (Insert or Update). Please note, I am not having any issues using the Insert operation with manually mapping the columns. Insert option in the Postgres node is working well for our scenario.

  1. Using the execute query option to run an insert query using parameters is returning errors for certain rows of data. The error message reads as if the error is related data type conversion issues, but the input data from the prior node does not have any formatting or type issues. The workflow is inserting data from one source table to a destination table. Both tables have the exact same structure/column names/types, etc and there is no casting or transformation happening in between. I am able to successfully insert certain rows individually, but some others will error out.
    Also, the Replace Empty Strings with NULL option does not seem to be working using the Execute Query option and is inserting empty strings.
  2. Using the insert or update operation does not work at all. The “Map Each Column Manually” mode returns an instant error message in the UI stating:
    “No columns found in Postgres. Retry”
    Using the “Map Automatically” mode returns the following error after executing the node: ERROR: Column to match on not found in input item. Add a column to match on or set the ‘Data Mode’ to ‘Define Below’ to define the value to match on.

Thanks for your help.

What is the error message (if any)?

Problem in node ‘product_n8n_da - DEV‘
invalid input syntax for type boolean: “”

Please share your workflow

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

ERROR: invalid input syntax for type boolean: “”

Failed query: INSERT INTO “ADM”.product_n8n_da (product_id, product_identifier, product_name, product_type, series, intended_grades, copyright_year, subject_level1, subject_level2, publisher_prior, segment, sub_segment, state_specific_version, oer, district_created, supplier_id, created_date, modified_date) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)



7/9/2023, 11:17:29 PM


NodeOperationError: invalid input syntax for type boolean: ""
    at parsePostgresError (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Postgres/v2/helpers/utils.js:67:12)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Postgres/v2/helpers/utils.js:151:27
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at Object.router (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Postgres/v2/actions/router.js:48:30)
    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:652:28)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:596:53

The data for the record that is failing is as follows:

    "product_id": "2",
    "product_identifier": "c46a88c4cec718ac10d99e483baf268a55d92b736d4bfe430c5c2520341f702e",
    "product_name": "A|G|A, Texas Edition",
    "product_type": "Core Curriculum",
    "series": "HMH Traditional",
    "intended_grades": "9-12",
    "copyright_year": "2016",
    "subject_level1": "Math",
    "subject_level2": null,
    "publisher_prior": null,
    "segment": null,
    "sub_segment": null,
    "state_specific_version": true,
    "oer": false,
    "district_created": false,
    "supplier_id": "16",
    "prior_supplier_id": null,
    "created_date": "2023-03-01T16:01:27.165Z",
    "modified_date": "2023-04-07T17:40:29.405Z"

Information on your n8n setup

  • n8n version: self-hosted community Version 0.233.1
  • Database (default: SQLite): default
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system:
1 Like

Same issue with the insert or update operation ; I have been searching during the whole WE, without success

Hi @guillaume.lhoste, hi @dahmadi I am sorry you guys are having trouble. Let’s try to reproduce this problem and find a workaround. @dahmadi can you share your "CEMD_ADM".product_n8n_da table scheme please?

Thanks @MutedJam. Table DDL is:

CREATE TABLE "CEMD_ADM".product_n8n_da (
	product_identifier text NULL,
	product_name text NULL,
	product_type text NULL,
	series text NULL,
	intended_grades text NULL,
	copyright_year text NULL,
	subject_level1 text NULL,
	subject_level2 text NULL,
	publisher_prior text NULL,
	segment text NULL,
	sub_segment text NULL,
	state_specific_version bool NOT NULL DEFAULT false,
	oer bool NOT NULL DEFAULT false,
	district_created bool NOT NULL DEFAULT false,
	supplier_id int8 NULL,
	prior_supplier_id int8 NULL,
	created_date timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	modified_date timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT product__n8n_pk PRIMARY KEY (product_id)

A record that inserts successfully using the Execute Query operation (same as above) is as follows:

    "product_id": "1",
    "product_identifier": "2ce1f9c5f290101d8daa3ebaca8b4e289a0ea4d7c778513b10538e99b7b022c8",
    "product_name": "Florida's B.E.S.T. Go Math!",
    "product_type": "Core Curriculum",
    "series": "HMH Go Math!",
    "intended_grades": "K-5",
    "copyright_year": "2023",
    "subject_level1": "Math",
    "subject_level2": null,
    "publisher_prior": null,
    "segment": null,
    "sub_segment": null,
    "state_specific_version": true,
    "oer": false,
    "district_created": false,
    "supplier_id": "16",
    "prior_supplier_id": null,
    "created_date": "2023-03-01T16:01:27.165Z",
    "modified_date": "2023-03-01T16:01:27.167Z"

Let me know if you need anything else. Thanks again.

1 Like

Thank you so much for this additional example @dahmadi! It seems to me n8n is having trouble with the comma in the product_name field, causing it to read A|G|A as one query parameter and Texas Edition as the next parameter (and also shifting all additional parameters of course).

I’ll try to find a suitable workaround and will get back to you as soon as I can.

1 Like

Okay, this is actually simple once you know how it works (I didn’t until now, so I am very sorry this took a bit). If you simply send a valid JS array in the “Query Parameters” field (instead of a string), n8n wouldn’t try to split each parameter on the comma and instead use the raw values.

I’ve tested this on my own side like so:

(When copying this make sure to add your "CEMD_ADM" schema again to the query, I’ve removed it as I was using my default schema for testing.)

The INSERT statement works as expected:

The data also looks as expected when querying:

Hope this helps :slight_smile:

Please do let me know if you have any further questions on this.


Hi @MutedJam ,
Thanks for your support ; Apologies for jumping in, but don’t you have on your side the issue when selecting “insert or update”, where Columns to Match input box does not appear ?
The “Map Each Column Manually” mode returns an instant error message in the UI stating:
“No columns found in Postgres. Retry”
Thanks !

Hi @guillaume.lhoste, I could not reproduce your issue unfortunately :frowning:

Are other operations working for you (including something simple like Execute Query → SELECT 1) ?

Does your Postgres role perhaps not allow querying the respective table schema?

Yes, I can execute query, and I can even insert as shown below.

However, whenever I choose insert or update (upsert), columns are not found anymore.

I am running 0.236.0, and it occured since 0.234

1 Like

Excellent, thank you so much @MutedJam! This is working as expected now for the Execute query (all rows being inserted, NULLs being sent for empty strings).

I can confirm what @guillaume.lhoste has mentioned below for the upsert operations. I receive the same error in the UI for upsert using the Postgres node. It is not related to database/schema permissions. Thanks.

1 Like

Hi folks, I am so sorry for the trouble. I was now able to reproduce this on my end and will add this to our bug tracker for a closer look and fix by the engineering team.

You make my day @MutedJam :slight_smile: Thanks a lot.

1 Like

Hi folks, just a quick update on this one: A fix is coming!

This thread will be updated automatically once the fix is available in an n8n release :slight_smile:


New version [email protected] got released which includes the GitHub PR 6643.


Amazing! Thanks so much @MutedJam and @jan for the quick fix.

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.