Postgres Query Parameters not liking null or empty parameters

Questions
1

Describe the problem/error/question

When using parameters on the postgres node and filling them with something like (expression mode): “{{$json.PurchaseOrderComments}}, {{$json.PurchaseOrderDetails}}” etc, if the key is present but [null] or ‘’ (empty) then I am getting an error.

What is the error message (if any)?

"Variable $2 (or whatever) out of range. Parameters array length: (whatever the array is minus the empty ones)

The text under query parameters shows something like “result 1, , result 3” which I would expect if the middle parameter is empty or null. When I click on the line the little popup that shows up under it properly shows the empty results being replaced with null. When I open the full modal I see the proper null results. I have tried with “replace empty strings with NULL” turned on and off.

Query parameters should be able to function just fine whether the input key has content or is empty or null. I thought it worked this way in the past. As a workaround I am now putting in {{$json.PurchaseOrderComments || ‘none’}} and then ‘NULLIF($1, ‘none’)’ in the sql query, which seems a bit excessive.

Information on your n8n setup

  • n8n version: Community 1.68.0
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): npm
  • Operating system: Ubuntu
1 Like
2

I have the same issue with Postgres Query node in

  • n8n version: Community 1.66.0
  • Running n8n via Docker
  • Operating system: Ubuntu
3

Received an automatic message requesting I bump this thread.

4

@EricSGS I’ve struggled with Postgres SQL syntax (coming from MSSQL world) and often results in PGSQL giving me error messages.

Questions:

  1. Is the error message you shared coming from PGSQL or n8n runtime?
  2. Have you been able to find the exact query text that PGSQL is attempting to execute? That might be a good place to start. (turn on query logging with pgsql log_statement=all)
5

It’s an n8n error.

image
image513×754 25 KB

image

Null should be an absolutely be an acceptable value… but…

I found the solution with the phrasing of this error (the error is phrased differently if you are trying more than one query parameter). Wrap the full query params with [ ] and it works just fine.

The examples in the documentation (and tips, and potentially error messaging) should be updated to recommend [ ].

image
image797×203 14.8 KB

6

Of course this causes further issues if the item is a string that has a comma in it. I tried using JSON.stringify() to encapsulate the string safely

[{{JSON.stringify($json.PurchaseOrderComments)}}, {{$json.PurchaseOrderCreationDate}}]

But that didn’t seem to work. Any suggestions?

EDIT: fixed, a single javascript set or markers encapsulating the whole thing and THEN a set of [ ]
{{[$json.PurchaseOrderComments, $json.PurchaseOrderCreationDate]}}

Docs should be update to show this kind of use.

1 Like