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.
- 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. - 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)
Details
Time
7/9/2023, 11:17:29 PM
Stack
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:
