Mqtt-Nodered-Pgadmin

[vishnupriya]

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:

[
    {
        "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"
    }
]

[vishnupriya]

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

Template node:

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

Function node:

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

[Steve-Mcl]

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?)

[UnborN]

It seems that you are using the 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
   
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.
   
   

Test Flow:

[{"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.