Postgres node IN operator with parameters

Questions
, ,
1

Describe the problem/error/question

Postgres node is not working with IN Operator when the parameters is an Array. I suppose that’s because of the sanity checks.
E.g. I have a list of some workflows that I’m returning via the n8n API. Then I want to get all executions for each of them (I do not want to do it via the n8n API as it doesn’t provide a filtering functionality by date). So the fixed query would be like that:

SELECT
finished,
status,
"startedAt",
"stoppedAt",
"workflowId",
workflow_entity.name AS "workflowName"
FROM execution_entity, workflow_entity
WHERE "workflowId" = workflow_entity.id AND "startedAt"::date >= current_date-1 AND "startedAt"::date < current_date AND "workflowId" IN ('wfId1', 'WfId2')

But I haven’t found a way to do so with parameters. I’ve used couple of options. As an Array (Only the executions of the first workflowId has been returned)

As a query part (no success, seems the node just enclosed it into a string):

Is there any ways to use the IN operator?

Information on your n8n setup

  • n8n version: 1.39.1
  • Database (default: SQLite): Postgres
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: Docker
2

@Jon, maybe you know how it works? :slight_smile:

3

Hi @barn4k, I’m not an expert on Postgres or the node itself, so I’m just guessing here but maybe you could try turning the array of workflowIds into a string of comma separated workflowIds in the Query Parameters expressions? So something like this:

{{$json.appended_id.map(id => ‘${id}’).join(',')}}

4

I’ve tried to do so, but it seems in that way the entire list of comma-separated workflowIds are treated as one string, E.g.

['wfId1','wdId2','wfId3'] > 'wfId1,wdId2,wfId3'
5

Sorry @barn4k , for some reason the formatting got changed in the expression when I pasted earlier. Could you try adding backticks around the double quoted expression to create template literals like this:
CleanShot 2024-06-07 at 17.06.09

6

You could also dynamically construct the array of placeholders like ($1, $2, $3) using an expression. This is a MySQL example but it should be the same for Postgres:

image
image963×570 32.5 KB

The top expression loops through your array and creates something of the correct length with the format ($1, $2, $3).

The bottom expression turns your array into a list of parameters that matches.

1 Like
7

I didn’t know that the Query field is of the expression type. If I can use expressions there, why do I need the Query Parameters then?

Checked that option and seems something is buggy

image
image3646×963 369 KB

8

Query parameters are an extra layer of security to prevent SQL injection attacks. If you can trust the data you’re inserting into your SQL, then you don’t need them.

If you’d like help debugging your error, please post your workflow. Thanks!

9

It’s quite the same as in the first post

10

Thanks. Are you able to share the workflow JSON file with me, maybe as a DM? That way I can see the data you pinned

11

You can see it if you click on the red text below the workflow and hit the copy button :face_with_hand_over_mouth:

12

This indeed looks like a bug. I’ve reported it internally — thanks for bringing this up, @barn4k.

1 Like
13

Do you have a PR on this?

14

No. We have it on the roadmap, but can’t give an estimate right now I’m afraid

1 Like
15

Hey! I just came across the same issue. Turns out, you don’t need to use join(‘,’) on the query parameters.
Just use {{ $json.array }} and it should work!
I think n8n treats {{ $json.array.join(‘,’) }} as a string, not as an array.

2 Likes
16

Hm… Yes, that seems to be working fine. But totally unclear :sweat_smile:

17

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

18

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

2 Likes