# Insert into PostgreSQL database

**URL:** <https://discourse.nodered.org/t/insert-into-postgresql-database/31263>\
**Category:** General\
**Created:** [7 August 2020 12:22 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263 "2020-08-07T12:22:51Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [10 August 2020 11:51 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/21 "2020-08-10T11:51:19Z")

</div>

Hi Colin,  
I don't see anything coming out of the db node - it's like it isn't doing anything at all...

Here is the requested startup of Node-Red in the terminal:

```auto
Microsoft Windows [Version 10.0.17763.379]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>node-red
10 Aug 11:14:36 - [info]

Welcome to Node-RED
===================

10 Aug 11:14:36 - [info] Node-RED version: v1.1.0
10 Aug 11:14:36 - [info] Node.js version: v14.5.0
10 Aug 11:14:36 - [info] Windows_NT 10.0.17763 x64 LE
10 Aug 11:14:39 - [info] Loading palette nodes
10 Aug 11:14:49 - [info] Dashboard version 2.22.1 started at /ui
10 Aug 11:14:50 - [info] Settings file : \Users\Administrator\.node-red\settings.js
10 Aug 11:14:50 - [info] Context store : 'default' [module=memory]
10 Aug 11:14:50 - [info] User directory : \Users\Administrator\.node-red
10 Aug 11:14:50 - [warn] Projects disabled : editorTheme.projects.enabled=false
10 Aug 11:14:50 - [info] Flows file : \Users\Administrator\.node-red\flows_WIN-NR2019.json
10 Aug 11:14:50 - [info] Server now running at http://127.0.0.1:1880/
10 Aug 11:14:50 - [warn]

---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.

If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.

You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------

10 Aug 11:14:50 - [info] Starting flows
10 Aug 11:14:50 - [info] Started flows
10 Aug 11:29:19 - [info] Stopping flows
10 Aug 11:29:19 - [info] Stopped flows
10 Aug 11:29:19 - [info] Starting flows
10 Aug 11:29:19 - [info] Started flows
10 Aug 11:33:43 - [info] Stopping flows
10 Aug 11:33:43 - [info] Stopped flows
10 Aug 11:33:43 - [info] Starting flows
10 Aug 11:33:43 - [info] Started flows
10 Aug 11:33:49 - [info] [debug:fb664bf6.df2548]
{
  _msgid: '5f367f7b.8431f',
  payload: "insert into dev (content) values ('test1')",
  topic: '',
  queryParameters: {}
}
10 Aug 12:05:35 - [info] Stopping flows
10 Aug 12:05:35 - [info] Stopped flows
10 Aug 12:05:35 - [info] Starting flows
10 Aug 12:05:35 - [info] Started flows
10 Aug 12:46:03 - [info] Stopping flows
10 Aug 12:46:03 - [info] Stopped flows
10 Aug 12:46:03 - [info] Starting flows
10 Aug 12:46:04 - [info] Started flows
10 Aug 12:46:19 - [info] Stopping flows
10 Aug 12:46:19 - [info] Stopped flows
10 Aug 12:46:19 - [info] Starting flows
10 Aug 12:46:19 - [info] Started flows
10 Aug 12:46:33 - [info] Stopping flows
10 Aug 12:46:33 - [info] Stopped flows
10 Aug 12:46:33 - [info] Starting flows
10 Aug 12:46:33 - [info] Started flows
10 Aug 12:47:25 - [info] Stopping flows
10 Aug 12:47:25 - [info] Stopped flows
10 Aug 12:47:25 - [info] Starting flows
10 Aug 12:47:25 - [info] Started flows

```

Thank you

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [10 August 2020 11:55 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/22 "2020-08-10T11:55:55Z")

</div>

I notice in your postgres table display you used  
`select * from public.measurement ...` while in node-red you use  
`select * from measurement `

is there a reason for this (note I've never used postgres)

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [10 August 2020 12:25 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/23 "2020-08-10T12:25:46Z")

</div>

I tried both ways actually - no difference ☹ but good spot!

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 August 2020 12:37 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/24 "2020-08-10T12:37:10Z")

</div>

It seems that the node doesn't work with nodejs 14.

If the node had a github repository then you could submit an issue, but since there does not seem to be one then it seems there is not much you can do other than downgrade nodejs or use a different postgres node. Did you choose that one for a particular reason.

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [10 August 2020 12:59 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/25 "2020-08-10T12:59:35Z")

</div>

Hi Colin, very interesting... I din't select this one for any reason in particular, I just need one that works as expected. Can you PLEASE suggest a node that will work in my environment?

Many thanks!

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 August 2020 13:19 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/26 "2020-08-10T13:19:14Z")

</div>

> [@jomacdon](#):
>
> Can you PLEASE suggest a node that will work in my environment?

I haven't used any of those nodes, and I suspect no-one here has, in which case you may just have to try them. I would start by looking at their github repositories (which should be linked from the page on the node-red flows site), see whether they are maintained and look at the Issues to see if they are being addressed.

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [11 August 2020 13:32 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/27 "2020-08-11T13:32:15Z")

</div>

So, some good news guys - I found a working PostgreSQL node it's called 'node-red-contrib-postgrestor-next' here is the results:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/5/05d387f05edb7fb1bb883c676a1231e296fac5e1.png)  
Here is my table called 'dev'  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/ce9516b46e76d01cb09e3f829af94faf9c1573b6.png)

This is great news, at least this is working but now comes the real reason for getting a working PostgreSQL node...

I want to inject the results of an API call into the database table called 'sensors', here is what is happening in the flow which is bypassing the PostgreSQL node for now:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/8/e8669e9829029414b7225cabd6a1b2c8c57e3d90.png)

Here is my PostgreSQL command:

```auto
INSERT INTO SENSORS (id, device_id, name, unit) VALUES ('{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}');

```

The results are strange, all I get is a load of empty rows ☹

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/6/166edc608db8d4243a16ba84a8072ba7f05890d3.png)

What am I not doing correctly here?  
Many thanks!

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [11 August 2020 16:03 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/28 "2020-08-11T16:03:29Z")

</div>

INSERT INTO SENSORS (id, device\_id, name, unit) VALUES ('{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}');

Where are you using that?

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [11 August 2020 17:18 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/29 "2020-08-11T17:18:44Z")

</div>

Hi Colin,  
It's inside the PostgreSQL node:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d32cc909e61acf291882c44e89e254212ef2643c.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [11 August 2020 17:27 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/30 "2020-08-11T17:27:48Z")

</div>

I don't see anything in the node's readme that says you can use that syntax, though I am on my phone and may have missed it.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [11 August 2020 21:15 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/31 "2020-08-11T21:15:59Z")

</div>

Hi,

I havent worked with PostgreSQL either but after reading the docs

> **[node-red-contrib-postgrestor-next](https://flows.nodered.org/node/node-red-contrib-postgrestor-next)**
>
> Yet another Node-RED node to query PostgreSQL with query parameters

it expects the values to be passed in as parameters in array : **msg.params = ['Andrea']**

so i would suggest to try to change your postgrestor node configuration Query to :

` INSERT INTO SENSORS (id, device_id, name, unit) VALUES ($1, $2, $3, $4);`

according to the docs example and from your function pass :

`msg.params = ['{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}']`

fingers crossed 😃

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [11 August 2020 21:19 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/32 "2020-08-11T21:19:52Z")

</div>

Or perhaps @jomacdon meant to put the code using the moustache syntax in a Template node to expand the parameters and then feed that into the postgres node.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [11 August 2020 21:40 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/33 "2020-08-11T21:40:06Z")

</div>

i know .. but it seems that this **node-red-contrib-postgrestor-next** node  
doesnt accept a 'custom made' sql query  
so my suggestion was to remove the template, get the values from the API and structure them in a **msg.params** array using a function and test like this 🙂

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [12 August 2020 07:24 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/34 "2020-08-12T07:24:39Z")

</div>

> [@UnborN](#):
>
> **node-red-contrib-postgrestor-next** node  
> doesnt accept a 'custom made' sql query

I think it unlikely that you cannot give it a fully defined query with no variable parameters. In fact I imagine that is how @jomacdon initially tested it, though I don't know that.

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [12 August 2020 07:49 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/35 "2020-08-12T07:49:08Z")

</div>

Hello guys, I am just about to test the suggestion for @UnborN (Andy), I will update soon - Thanks!

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [12 August 2020 08:03 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/36 "2020-08-12T08:03:43Z")

</div>

Hello guys, I did the changes and can now see something in the PostgreSQL database BUT... it's not what is expected:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/c/5cc016fb865d4df45be5ecfc667690ac70b0fede.png)

The new function node has created the Parameters as suggested by @UnborN but they have not collected the data from the array coming from the API call:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/3/43c495390658cbb6814af84ede736bc83fd2478f.png)

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [12 August 2020 08:06 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/37 "2020-08-12T08:06:56Z")

</div>

Function Node code:

```auto
msg.params = ['{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}']
return msg;

```

PostgreSQL node:

```auto
INSERT INTO SENSORS (id, device_id, name, unit) VALUES ($1, $2, $3, $4);

```

Thank you.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [12 August 2020 14:59 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/38 "2020-08-12T14:59:53Z")

</div>

Hi,

opps .. at least we are writing something in the table 🙂  
try to **remove the single quotes and curly braces** around each value .. like this

```auto
msg.params = [payload.id, payload.deviceId, payload.name, payload.unit]
return msg;

```

and can you show as seperate screenshots of debugs from API request and function node ?

---

<div class="post-metadata">

**Author:** ![jomacdon](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@jomacdon](https://discourse.nodered.org/u/jomacdon)\
**Post date:** [12 August 2020 15:09 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/39 "2020-08-12T15:09:42Z")

</div>

Hi,  
I tried that, thank you but now nothing is coming out of the Function Node ☹

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [12 August 2020 15:20 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/40 "2020-08-12T15:20:34Z")

</div>

can you show as seperate screenshots of debugs from **API request** and **function node**?  
(and extend the view of debug from API to see how the value stucture is)  
set debugs to complete msg  
and also if you can repost your flow with the latest changes ?

[Previous page](https://discourse.nodered.org/t/insert-into-postgresql-database/31263.md?page=1)

[Next page](https://discourse.nodered.org/t/insert-into-postgresql-database/31263.md?page=3)