# Mqtt-Nodered-Pgadmin

**URL:** <https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648>\
**Category:** General\
**Created:** [29 September 2021 04:49 UTC](https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648 "2021-09-29T04:49:39Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![vishnupriya](https://avatars.discourse-cdn.com/v4/letter/v/e9c0ed/32.png) [@vishnupriya](https://discourse.nodered.org/u/vishnupriya)\
**Post date:** [29 September 2021 04:49 UTC](https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648/1 "2021-09-29T04:49:39Z")

</div>

Hi,  
I'm new to Node-red and I'm in urgent need for a help.  
I'm trying to insert dynamic data coming from mqtt sensor in to a table in my pgadmin. But I'm facing some issues with inserting data, because its showing ("Insert into ...") debug messages and not getting inserted when I look at the table though.  
I'm attaching my flow:

```auto
[
    {
        "id": "95f6ef359f445cc9",
        "type": "tab",
        "label": "Flow 2",
        "disabled": false,
        "info": ""
    },
    {
        "id": "b7aaa78383927fc7",
        "type": "inject",
        "z": "95f6ef359f445cc9",
        "name": "sensor1",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "senosrs/sensor1",
        "payload": "Open",
        "payloadType": "str",
        "x": 140,
        "y": 140,
        "wires": [
            [
                "e95866d32ab51948"
            ]
        ]
    },
    {
        "id": "d7c0ae0ffcf444e7",
        "type": "debug",
        "z": "95f6ef359f445cc9",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 510,
        "y": 280,
        "wires": []
    },
    {
        "id": "e95866d32ab51948",
        "type": "mqtt out",
        "z": "95f6ef359f445cc9",
        "name": "",
        "topic": "",
        "qos": "",
        "retain": "",
        "respTopic": "",
        "contentType": "",
        "userProps": "",
        "correl": "",
        "expiry": "",
        "broker": "4e7080fbb9618d51",
        "x": 390,
        "y": 160,
        "wires": []
    },
    {
        "id": "8fb587015d30842a",
        "type": "mqtt in",
        "z": "95f6ef359f445cc9",
        "name": "",
        "topic": "SODAQ_OUT/#",
        "qos": "0",
        "datatype": "auto",
        "broker": "4e7080fbb9618d51",
        "nl": false,
        "rap": true,
        "rh": 0,
        "x": 190,
        "y": 300,
        "wires": [
            [
                "c377a0593ccee2b4"
            ]
        ]
    },
    {
        "id": "c377a0593ccee2b4",
        "type": "json",
        "z": "95f6ef359f445cc9",
        "name": "",
        "property": "payload",
        "action": "",
        "pretty": false,
        "x": 280,
        "y": 400,
        "wires": [
            [
                "041917322802839a"
            ]
        ]
    },
    {
        "id": "10b8c3139f10323f",
        "type": "catch",
        "z": "95f6ef359f445cc9",
        "name": "",
        "scope": null,
        "uncaught": false,
        "x": 580,
        "y": 160,
        "wires": [
            [
                "d7c0ae0ffcf444e7"
            ]
        ]
    },
    {
        "id": "9d49731d557c831c",
        "type": "function",
        "z": "95f6ef359f445cc9",
        "name": "",
        "func": "\n//msg.params = [msg.payload.NameofDevice,\n//msg.payload.Date,\n//msg.payload.Time,\n//msg.payload.Temp];\nmsg.queryParameters = msg.payload\nmsg.parameters = [payload.NameofDevice, payload.Date, payload.Time, payload.Temp]\n//NameofDevice = msg.payload.NameofDevice;\n//Date = msg.payload.Date;\n//Time = msg.payload.Time;\n//Temp = msg.payload.Temp;\n//msg.params = [NameofDevice, Date, Time, Temp];\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 480,
        "y": 520,
        "wires": [
            [
                "83fb247497bf5955",
                "d7c0ae0ffcf444e7"
            ]
        ]
    },
    {
        "id": "83fb247497bf5955",
        "type": "postgrestor",
        "z": "95f6ef359f445cc9",
        "name": "",
        "query": "select * from demo;",
        "postgresDB": "a75d10a040ae5e36",
        "output": true,
        "outputs": 1,
        "x": 610,
        "y": 580,
        "wires": [
            [
                "d7c0ae0ffcf444e7"
            ]
        ]
    },
    {
        "id": "041917322802839a",
        "type": "template",
        "z": "95f6ef359f445cc9",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "INSERT INTO demo(name, day, timing, temperature) VALUES ('{{{payload.NameofDevice}}}', '{{{payload.Date}}}', '{{{payload.Time}}}', '{{{payload.Temp}}}')",
        "output": "str",
        "x": 380,
        "y": 440,
        "wires": [
            [
                "9d49731d557c831c"
            ]
        ]
    },
    {
        "id": "4e7080fbb9618d51",
        "type": "mqtt-broker",
        "name": "mqtt",
        "broker": "broker.hivemq.com",
        "port": "1883",
        "tls": "",
        "clientid": "",
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "sessionExpiry": ""
    },
    {
        "id": "a75d10a040ae5e36",
        "type": "postgresDB",
        "name": "postgres@127.0.0.1:5432/test",
        "host": "localhost",
        "hostFieldType": "str",
        "port": "5432",
        "portFieldType": "num",
        "database": "test",
        "databaseFieldType": "str",
        "ssl": "false",
        "sslFieldType": "bool",
        "max": "10",
        "maxFieldType": "num",
        "min": "1",
        "minFieldType": "num",
        "idle": "1000",
        "idleFieldType": "num",
        "connectionTimeout": "10000",
        "connectionTimeoutFieldType": "num",
        "user": "postgres",
        "userFieldType": "str",
        "password": "123",
        "passwordFieldType": "str"
    }
]

```

---

<div class="post-metadata">

**Author:** ![vishnupriya](https://avatars.discourse-cdn.com/v4/letter/v/e9c0ed/32.png) [@vishnupriya](https://discourse.nodered.org/u/vishnupriya)\
**Post date:** [29 September 2021 04:54 UTC](https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648/2 "2021-09-29T04:54:07Z")

</div>

I will also attach the necessary scrrenshots;  
Flow & demo of debug messages:

 ![Screenshot (127)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/f/9f81f97c0dbbd6dd142b776f62ec3771ee7b67c0.png)

Template node:

 ![Screenshot (124)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/2/420cbe923efc10cb0e2dad8fcd1daabd4a230688.png)

Template node code:  
INSERT INTO demo(name, day, timing, temperature) VALUES ('{{{payload.NameofDevice}}}', '{{{payload.Date}}}', '{{{payload.Time}}}', '{{{payload.Temp}}}')

Function node:

 ![Screenshot (125)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/0/20f45c2a0da5d7d5c94139baff3c5462e1c3683b.png)

Function node code:  
msg.queryParameters = msg.payload  
msg.parameters = [payload.NameofDevice, payload.Date, payload.Time, payload.Temp]  
return msg;

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [29 September 2021 06:29 UTC](https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648/3 "2021-09-29T06:29:15Z")

</div>

First thing first.

Use separate debug nodes and give them names so that we can understand where the debug message comes from.

Set the debug nodes to show complete message.

Next, what postgre node are you using? (node-red-contrib-????) AND what msg property does it tell you to send the INSERT query to? (what does the postgre nodes built-in help say?)

---

<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:** [29 September 2021 10:33 UTC](https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648/4 "2021-09-29T10:33:41Z")

</div>

It seems that you are using the **[node-red-contrib-postgrestor](https://flows.nodered.org/node/node-red-contrib-postgrestor)** node ?  
that says in its help file that :  
**" Postgres implements a template engine allowing parameterized queries "**  
and its gives two SELECT examples

Now for your flow you dont really need the Json, Template or Function node

1. you can set your MQTT in node to  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/f/0fdedb94c13174cdbe8c5eed398be0137c0f8409.png)
2. Template node is not needed just use your INSERT sql query correctly in the **postgres** template. Without triple curly brackets and `msg.` infront of each payload.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/6/26101024aa7f80f53d34a3af65bb0ea7795c6a22.png)

**Test Flow:**

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

```auto
[{"id":"d7c0ae0ffcf444e7","type":"debug","z":"95f6ef359f445cc9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":300,"wires":[]},{"id":"8fb587015d30842a","type":"mqtt in","z":"95f6ef359f445cc9","name":"","topic":"SODAQ_OUT/#","qos":"0","datatype":"json","broker":"4e7080fbb9618d51","nl":false,"rap":true,"rh":0,"x":140,"y":300,"wires":[["83fb247497bf5955"]]},{"id":"83fb247497bf5955","type":"postgrestor","z":"95f6ef359f445cc9","name":"","query":"INSERT INTO demo(name, day, timing, temperature) VALUES ('{{msg.payload.NameofDevice}}', '{{msg.payload.Date}}', '{{msg.payload.Time}}', '{{msg.payload.Temp}}')","postgresDB":"a75d10a040ae5e36","output":true,"outputs":1,"x":370,"y":300,"wires":[["d7c0ae0ffcf444e7"]]},{"id":"4e7080fbb9618d51","type":"mqtt-broker","name":"mqtt","broker":"broker.hivemq.com","port":"1883","tls":"","clientid":"","usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"a75d10a040ae5e36","type":"postgresDB","name":"postgres@127.0.0.1:5432/test","host":"localhost","hostFieldType":"str","port":"5432","portFieldType":"num","database":"test","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","max":"10","maxFieldType":"num","min":"1","minFieldType":"num","idle":"1000","idleFieldType":"num","user":"postgres","userFieldType":"str","password":"123","passwordFieldType":"str"}]

```

ps. if this doesnt work copy/paste a msg coming out of the MQTT in node using a complete msg Debug node.

---

<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:** [28 November 2021 10:34 UTC](https://discourse.nodered.org/t/mqtt-nodered-pgadmin/51648/5 "2021-11-28T10:34:02Z")

</div>

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