Postgres INSERT with SELECT lookup as one value

[openup]

Describe the problem/error/question

We have a workflow that inserts data from KoboToolbox into a PostgreSQL database. Most of the columns come from the KoboToolbox form so we use the Insert operation and {{ $json.ABC }} syntax. That works well. but one of the new columns is a more complex PostGIS lookup based on latitude and longitude values. I can do this starting with a new N8N workflow PostgreSQL step and using the Execute Query Operation. But how do I add it to the existing Insert Operation step? The validation in the Operation does not like my SELECT statement.

INSERT INTO
    ...
VALUES
    (
        '{{ $json._submission_time }}',
        (
            SELECT
                ofc_sbrb_name
            FROM
                suburbs
            WHERE
                ST_Contains(
                    ST_SetSRID(wkb_geometry :: geometry, 4326),
                    ST_SetSRID(
                        ST_MakePoint({{ $json._geolocation[1] }}, {{ $json._geolocation[0] }}),
                        4326
                    ) :: geometry
                )
            LIMIT
                1
        ), {{ $json._geolocation[1] }}, {{ $json._geolocation[0] }}, '{{ $json.Name }}'
    );

Thanks for your help.

[n8n]

It looks like your topic is missing some important information. Could you provide the following if applicable.

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

[openup]

Thanks, I’m not sure how this applies as I am running everything in the cloud on https://n8n.io/.

• n8n version: The web one?
• Database (default: SQLite): PostgreSQL
• n8n EXECUTIONS_PROCESS setting (default: own, main): Do not know.
• Running n8n via (Docker, npm, n8n cloud, desktop app): Cloud
• Operating system: macOS, but really it is in Chrome.

[ihortom]

Welcome to the community @openup !

Tip for sharing information

Pasting your n8n workflow

---

Ensure to copy your n8n workflow and paste it in the code block, that is in between the pairs of triple backticks, which also could be achieved by clicking </> (preformatted text) in the editor and pasting in your workflow.

```
<your workflow>
```

That implies to any JSON output you would like to share with us.

Make sure that you have removed any sensitive information from your workflow and include dummy or pinned data with it!

---

Have you considered using query parameters instead of the n8n expression, Postgres node documentation | n8n Docs?

[openup]

@ihortom

Postgres node documentation | n8n Docs

Thank you for the reply. Do you mean we would have a step before the INSERT that generates an input query parameter for the INSERT step? So the complex PostGIS query wouldn’t run in the INSERT step but before it.

[ihortom]

No, that is not what I meant. Did you read the document I pointed out to which explains “query parameters”, Postgres node documentation | n8n Docs?

Your query would turn into something like this

INSERT INTO
    ...
VALUES
    (
        $1,
        (
            SELECT
                ofc_sbrb_name
            FROM
                suburbs
            WHERE
                ST_Contains(
                    ST_SetSRID(wkb_geometry :: geometry, 4326),
                    ST_SetSRID(
                        ST_MakePoint($2, $3),
                        4326
                    ) :: geometry
                )
            LIMIT
                1
        ), $2, $3, $4
    );

where the substitutes for the parameters are listed in the “Query Parameters” options, which allows for the expression usage