How do I write dynamic data to Postgres DB

[eldho1416]

Hello Everyone,

I'm doing a project in which I want to send data to my local MQTT broker and I will get the data in nodered by subscribing to the MQTT broker. Now I want to Insert the data coming out from "MQTT in" node to postgres DB. How do I Achieve this?

I have attached the flow for better understanding

[jbudd]

Do you know the name and structure of the database table?
Are you familiar with SQL?

[eldho1416]

I'm not very familiar with SQL, yes i do know name and structure of DB table.

[jbudd]

You need to convert your MQTT message into SQL

The SQL will be something like this.
Because I don't have a Postgre database I'm not sure if it needs to be in msg.payload or msg.topic.

INSERT INTO table_name (temp, humidity) VALUES ( {{{msg.Temp}}}, {{{msg.Humidity}}} )

A Change node like this might construct the SQL statement.

[
    {
        "id": "fc1c0b042a761aa4",
        "type": "change",
        "z": "ac631a6e.90e038",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "Temp",
                "pt": "msg",
                "to": "payload.Temp",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "Humidity",
                "pt": "msg",
                "to": "payload.Humidity",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "INSERT INTO table_name(temp, humidity) VALUES({{{ msg.Temp }}}, {{{ msg.Humidity }}})",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 380,
        "y": 280,
        "wires": [
            []
        ]
    }
]