# 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:** 5\
**Page:** 3

<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:43 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/41 "2020-08-12T15:43:49Z")

</div>

Hi,  
Here is a screen shot of the output from the API request (Sensors) array contains 1422 Objects:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/f/bf2ecaf682a3f2fc5e1ab180307610307fe26f88.png)  
Now the output from the function node (taking away last change) removing the moustache:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/f/cf37d46b1eb7155846744a606285712c20634db7.png)  
And finally my Flow:

```auto
[{"id":"e6d7d6d3.98ed08","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"bea21dcb.82e6a","type":"inject","z":"e6d7d6d3.98ed08","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":240,"y":140,"wires":[["a77381fe.655a6"]]},{"id":"a77381fe.655a6","type":"function","z":"e6d7d6d3.98ed08","name":"Auth Key","func":"msg.headers = { \n \"Content-Type\": \"application/json\"\n };\n\n\nmsg.payload = {auth_key:\"069e2b9c-682d-4780-83c2-d87fe07e87dc\"};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":140,"wires":[["be173f4f.cf483"]]},{"id":"be173f4f.cf483","type":"http request","z":"e6d7d6d3.98ed08","name":"RESTful API Get Sensors","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/sensors","tls":"","persist":false,"proxy":"","authType":"bearer","x":410,"y":240,"wires":[["d445179a.6f0648"]]},{"id":"d445179a.6f0648","type":"split","z":"e6d7d6d3.98ed08","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":350,"y":340,"wires":[["cbcd0743.726808"]]},{"id":"cbcd0743.726808","type":"split","z":"e6d7d6d3.98ed08","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":490,"y":340,"wires":[["535aebd3.7e9fc4"]]},{"id":"535aebd3.7e9fc4","type":"function","z":"e6d7d6d3.98ed08","name":"","func":"msg.params = ['{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}']\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":630,"y":340,"wires":[["619f105e.f998c"]]},{"id":"938430e8.27833","type":"postgrestor","z":"e6d7d6d3.98ed08","name":"","query":"INSERT INTO SENSORS (id, device_id, name, unit) VALUES ($1, $2, $3, $4);","postgresDB":"74bfc636.269f68","output":true,"outputs":1,"x":810,"y":340,"wires":[[]]},{"id":"dd369c3b.f317e","type":"debug","z":"e6d7d6d3.98ed08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":460,"wires":[]},{"id":"619f105e.f998c","type":"debug","z":"e6d7d6d3.98ed08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":260,"wires":[]},{"id":"74bfc636.269f68","type":"postgresDB","z":"","name":"postgres@127.0.0.1:5432/testdb2","host":"127.0.0.1","hostFieldType":"str","port":"5432","portFieldType":"num","database":"testdb2","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","max":"10","maxFieldType":"num","min":"1","minFieldType":"num","idle":"1000","idleFieldType":"num","user":"postgres","userFieldType":"str","password":"19Glasgow69","passwordFieldType":"str"}]

```

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 17:13 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/42 "2020-08-12T17:13:28Z")

</div>

1. im confused .. from your last photo we actually see a result from function node
2. when i import your flow i see that your function still has the moustache '{{payload.id}}' systax. did you remove it ?

if you try this in function ?

```auto
let id = msg.payload.id;
let deviceId = msg.payload.deviceId;
let name = msg.payload.name;
let unit = msg.payload.unit;

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

```

ps. your api doesnt give a reply for me ..  
and also you said you get 1422 Objects from the API .. you want a sinle object to be inserted in the db or all 1422 ?? 😃

---

<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 18:21 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/43 "2020-08-12T18:21:05Z")

</div>

Hi,

No, I didn't try this function as it's the first time I have seen it, BUT...  
I tried it just now and WOW!:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/2/429e90b7c00c1c138cfb9164acbd3b15fb7506d2.png)  
It worked!!!  
THANK YOU, THANK YOU, THANK YOU!!!  
I was wondering if it would EVER work - you did 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:** [12 August 2020 18:37 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/44 "2020-08-12T18:37:19Z")

</div>

cool 🕶

I must admit that we didnt fully understand the structure of the data coming from the API and the docs on PostgreSQL were not so clear but im glad it worked .. all the best

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [26 August 2020 18:37 UTC](https://discourse.nodered.org/t/insert-into-postgresql-database/31263/45 "2020-08-26T18:37:27Z")

</div>

Closed on 26 August 2020 18:37 UTC

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

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