How to insert data to Postgres

Questions
1

Describe the issue/error/question

Hey folks, I am trying to understand if there’s a way to execute the INSERT operation in Postgres without writing the query.
I tried to follow the documentation, and I noticed there’s a way to map the query parameters to the table values. However, I don’t see that in the interface in my setup.

2022-09-09_16-49-31
2022-09-09_16-49-312874×1644 208 KB

In the example above I tried inserting the JSON representation.

{
  "id": 777654140,
  "Country": {{ $json["query"]["ip_country"] }}
}

What should I type into the Columns field?
I’m expecting to see the data table schema here to insert the values. For instance, Integromat follows the same approaches, which I find pretty nice.

Executing the query is not a problem, but I expect the no-code experience when I pick up INSERT mode.

What is the error message (if any)?

Please share the workflow

(Select the nodes and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow, respectively)

Share the output returned by the last node

Information on your n8n setup

  • n8n version: 0.193.4
  • Database you’re using (default: SQLite):
  • Running n8n with the execution process [own(default), main]:
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]:
2

Hi @kidrulit
Check out this example:

4

Is there a way to pass variables to Postgres without the Function module?
It seems there is no option of using the variables from previous sources in Function

I struggle passing data from wbhook to Postgres

5

Hey @kidrulit,

You could try using the Execute Query option and writing out your insert query that way. As part of our current node overhaul project though the way data is defined will be changed and will be much easier but that is going to be a few months away yet so for now either a function / set node if you want to use the insert option or building the query manually is the best bet.

6

Thanks!
Is it possible to list more than one property in the Query parameter field?

My following INSERT query fails when I pass all 14 properties in the Query Parameters field.

INSERT INTO public."page-events" (id, created_at, isp_provider, country, path, hostname, http_referrer, language, hash, utm_medium,  utm_source, utm_campaign, utm_content, utm_term)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)

It seems that the parameters are simply not loaded in the original query as I receive the error message ERROR: null value in column "id" of relation "page-events" violates not-null constraint

That’s how the parameters are listed:

2022-09-10_22-17-26
2022-09-10_22-17-262280×1076 151 KB

Am I missing something? Can I pass parameters directly to the query?

7

Hey @kidrulit,

That should be fine, the error seems to be Postgres saying the id is empty. The query you put looks to be very different from what you have in the expression field though.

Once you select execute query you can just pop in the first query and use expressions for the values.

8

Hey @Jon

Thanks for the reply.
Can you assist me with how does it differ from the Expression field? I didn’t quite get it.

The Insertion works fine when I execute in pg_admin
I simply copied the result of the Expression field and passed it instead of values.

INSERT INTO public."page-events" (id, created_at, isp_provider, country, path, hostname, http_referrer, language, uid_hash, utm_medium,  utm_source, utm_campaign, utm_content, utm_term)
VALUES (10000100, '2022-9-11 12:42', 'Datacamp Limited', 'Japan', '/pricing-page', 'www.spatial.chat', 'www.spatial.chat/pricing', 'jp', 'jsjdjsjdsjdjsj', 'seo', 'google', 'mark', 'none', 'oooo')

INSERT 0 1

Query returned successfully in 894 msec.
9

I guess I fundamentally missing how the query parameter mapping works in n8n.
Let’s test on a more straightforward case.

The input in a Query field:
SELECT * FROM public."page-events" WHERE id = $1;

The input in the Query Parameters field
{{ $json["id"] }}, , resulting 10000100,

2022-09-11_16-17-28
2022-09-11_16-17-282860×1628 224 KB

The n8n’s output is void.

When I run the SELECT * FROM public."landing-page-events" WHERE id = 10000100; in Query field I get the result of the string matching the id. I assume the Query ignores the Query Parameters field.

10

Ok, I think I got it. Thanks, I found a similar question on the forum.
Darg-and-dropping value from the previous step into the Query Parameters field doesn’t work; I need to specify the name.
When I put id but not the value to the Query Parameters, I get the result.

It’s super inconvenient, guys I would be happy to see more detailed instructions in the official doc. Is your doc open-sourced, can I contribute there?

1 Like
11

Hey @kidrulit,

The docs can be contributed to by anyone, I think we make some assumptions as most input fields work the same way.

When you use execute query you would use the same query that you would when using the database normally but for your values you can use variables which will be swapped out when running.

1 Like