Random and unexpected query parameter behavior

Questions
1

While testing query parameters in the Postgres node, I noticed behavior that differs from normal PostgreSQL placeholder handling.

In PostgreSQL, placeholders like $1, $2, $3 should refer to the same parameter regardless of where they appear in the query. However, in n8n they seem to behave differently.

Input

image
image624×145 6.64 KB

Test 1

SELECT 
$1 AS line1,
$2 AS line2,
$1 AS line3

Output

{
  "line1": "value1",
  "line2": "value1",
  "line3": "value2"
}

This is unexpected because $2 should resolve to value2.

Test 2

SELECT 
$3 AS line1,
$2 AS line2,
$1 AS line3

Output

{
  "line1": "value1",
  "line2": "value2",
  "line3": "value3"
}

Parameters are assigned based on the order of appearance in the query, rather than by their placeholder number.

Question

Is this a bug in how query parameters are parsed before execution?

2

@abc Have you tried mapping those parameters sequentially? Based on their order of appearance? In the SQL string as n8n currently parses them by position rather than by numerical index, not very sure but do you like the syntax for using direct expressions instead to bypass the parameter parser entirely?

3

What you are witnessing is most likely caused by the way n8n parses and maps the parameters beofre it sends the query. to PostgreSQL.

The Postgres node internally builds a parameter array in the order that placeholders appear in query text. If the parser treats each placeholder as a new positional binding instead of reusing numeric index, $1 could end up being mapped differently depending on the setup. This is more of a pre-processing limitation rather than a PostfreSQL engine problem.

Some workarounds you could try:

-Avoid reusing the same placeholder multiple times,

-Try moving repeated vlaues into a CTE/subquery so that each placeholder is only referenced once.

4

Yeah this is a known bug, n8n’s Postgres node reassigns the $N placeholders by order of appearance instead of passing them through to Postgres natively. So $1 showing up twice doesnt reuse the same value, it just grabs the next one in line. Your best workaround is to just use expressions directly in the query instead of the query parameters field.

5

@AnthonyAtXRay @Anshul_Namdev @achamm Thanks everyone for the help! :heart:

If this is a known issue, it would be great if someone could share the link to the issue.

1 Like
6

Heres the github issue for it that I recalled seeing! PostgreSQL node v2.5 upsert operation generates invalid parameterized query syntax · Issue #20122 · n8n-io/n8n · GitHub

its basically the postgres node rewriting the $N placeholders instead of passing them through natively, slightly diffrent case, same concept. Here’s a visual solution to what we’ve said, to help you understand.