Hi! I am loving n8n and am working my way through all of the different features. I am having a weird issue with the Postgres node, however. First I wanted to create an entry in a table manually. I kept getting that relation [table] does not exist. I tried the query I had constructed in the node using SQL Workbench, however, and it worked just fine… okay, weird, I figured I was still missing something. So then I just tried a super simple query from my table “cdc_rss”. - Select * from cdc_rss. Again, no dice. I get this:
ERROR: relation "cdc_rss" does not exist
error: relation "cdc_rss" does not exist
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:210:5)
at addChunk (_stream_readable.js:308:12)
at readableAddChunk (_stream_readable.js:289:11)
at Socket.Readable.push (_stream_readable.js:223:10)
at TCP.onStreamRead (internal/stream_base_commons.js:182:23)
I thought maybe I’ve configured the Postgres credentials incorrectly, but I’m using the same exact credentials I have in SQL Workbench. This query “Select * from cdc_rss” also works via SQL Workbench. Am I missing something obvious here? Perhaps n8n doesn’t support the latest version of Postgres?
My postgres instance is on RDS, but should be configured to all all public traffic. It’s working from my personal laptop, so not sure why it wouldn’t work from my n8n domain…
Welcome to the community @jman and really great to hear that you love n8n!
About your problem. I am not a Postgres expert in any way but I try my best anyway. Is it possible that you maybe have to add your schema? So something like: “public.cdc_rss”
@jan, Thanks, I tried that too. I was just poking around the Postgres node source and noticed that you/the author of that node are relying on pg-promise. The readme warns
ATTENTION: Never use ES6 template strings or manual concatenation to generate queries, as both can easily result in broken queries! Only this library's formatting engine knows how to properly escape variable values for PostgreSQL.
Could it be possible that somehow the way n8n is pulling the input from the “Execute Query” Postgres node input is introducing some kind of unexpected escape character? I’m copy and pasting the Expression result from my nodes into SQL Workbench and they’re working. I’ve checked the credentials a bunch of times and it looks like n8n is successfully connecting but somehow the query that gets sent to Postgres isn’t quite what it looks like. I can try to trace the code, but I wanted to see if you had any further suggestions before I keep chasing this.
Is there another type of DB node you’re more familiar with. I don’t want to overrely on you or the community here, but if there’s a DB people are more familiar with, I can easily switch over. I just used Postgres out of habit.
I just saw the issue @jman. The schema and the table should be set separately. When you set the name of the schema (which is needed) plus the table name on the field “table” the library parse it as “your_schema.your_table” and it should be parsed as “your_schema”.“your_table”. I will send a pull request in a bit fixing that.
