Node-Red to Posgresql

General
,
1

I have tried many postgres node but I can't find any node that's working.

I have a flow example here:

[
    {
        "id": "23fe0d38e71e8b24",
        "type": "postgrestor",
        "z": "ee21e3b499ee5ae7",
        "name": "insert",
        "query": "INSERT INTO temperature (speed1,speed2,speed3) VALUES ('{{msg.speed1}}','{{msg.speed2}}','{{msg.speed3}}');\n\n",
        "postgresDB": "12bdab9c7aa12974",
        "output": true,
        "outputs": 1,
        "x": 710,
        "y": 560,
        "wires": [
            [
                "d7d19540f0011117"
            ]
        ]
    },
    {
        "id": "90ad42656be8d5e5",
        "type": "inject",
        "z": "ee21e3b499ee5ae7",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            },
            {
                "p": "speed1",
                "v": "50",
                "vt": "str"
            },
            {
                "p": "speed2",
                "v": "60",
                "vt": "str"
            },
            {
                "p": "speed3",
                "v": "70",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payloadType": "date",
        "x": 530,
        "y": 560,
        "wires": [
            [
                "23fe0d38e71e8b24"
            ]
        ]
    },
    {
        "id": "d7d19540f0011117",
        "type": "debug",
        "z": "ee21e3b499ee5ae7",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 870,
        "y": 560,
        "wires": []
    },
    {
        "id": "12bdab9c7aa12974",
        "type": "postgresDB",
        "name": "postgres@localhost:5432/postgres",
        "host": "localhost",
        "hostFieldType": "str",
        "port": "5432",
        "portFieldType": "num",
        "database": "node-red-temp",
        "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": "xxx",
        "passwordFieldType": "str"
    }
]

I've seen this code in other forum solution and when I tried. It's not working on my side. Do you have sample postgres node that I can try?

2

what exactly is not working?

Does it connect? Does it error when you inject data? What error? Can you query and insert DATA using another application?

3

the flow above is not saving in my database. And the flow below is my recent updates but also not saving to my database. I have tried the query using query tool in my postgres and it's working

[
    {
        "id": "03a53e00c9c78103",
        "type": "function",
        "z": "14ee6d7566a42e0e",
        "name": "function 3",
        "func": "var topic1 = msg.topic;\nvar payload1 = JSON.stringify(msg.payload);\nmsg.payload = \"INSERT INTO public.mqtt_table (id, topic, payload) VALUES ('1','\" + topic1 + \"','\" + payload1 + \"');\";\n// Return the modified message\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 560,
        "y": 220,
        "wires": [
            [
                "83d4b160a599297a"
            ]
        ]
    },
    {
        "id": "3095d062c0825ec3",
        "type": "mqtt in",
        "z": "14ee6d7566a42e0e",
        "name": "",
        "topic": "data",
        "qos": "2",
        "datatype": "json",
        "broker": "bc8faf152aa409d6",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 190,
        "y": 220,
        "wires": [
            [
                "e3844ce7e1b2ac9a"
            ]
        ]
    },
    {
        "id": "e3844ce7e1b2ac9a",
        "type": "rbe",
        "z": "14ee6d7566a42e0e",
        "name": "",
        "func": "rbe",
        "gap": "",
        "start": "",
        "inout": "out",
        "septopics": true,
        "property": "payload",
        "topi": "topic",
        "x": 390,
        "y": 220,
        "wires": [
            [
                "03a53e00c9c78103"
            ]
        ]
    },
    {
        "id": "fd9a8caa5b0109b9",
        "type": "postgres",
        "z": "14ee6d7566a42e0e",
        "postgresdb": "6d425120c2df7839",
        "name": "db",
        "output": true,
        "perrow": false,
        "return_on_error": false,
        "limit_drop_intermediate": false,
        "limit_drop_if_in_queue": false,
        "outputs": true,
        "x": 930,
        "y": 220,
        "wires": [
            []
        ]
    },
    {
        "id": "83d4b160a599297a",
        "type": "function",
        "z": "14ee6d7566a42e0e",
        "name": "function 7",
        "func": "msg.queryParameters = msg.payload;\nconsole.log(msg.queryParameters)\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 740,
        "y": 220,
        "wires": [
            [
                "fd9a8caa5b0109b9"
            ]
        ]
    },
    {
        "id": "bc8faf152aa409d6",
        "type": "mqtt-broker",
        "name": "broker",
        "broker": "localhost",
        "port": "1884",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    },
    {
        "id": "6d425120c2df7839",
        "type": "postgresdb",
        "hostname": "localhost",
        "port": "5432",
        "db": "users",
        "ssl": false
    }
]
4

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