Node-red-contrib-postgrestor-next

General
,
1

Everytime I tried to save my query to postgres I've got an error it says "error: invalid input syntax for type json"

here's my flow:

[
    {
        "id": "3095d062c0825ec3",
        "type": "mqtt in",
        "z": "14ee6d7566a42e0e",
        "name": "",
        "topic": "data",
        "qos": "2",
        "datatype": "utf8",
        "broker": "bc8faf152aa409d6",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 190,
        "y": 220,
        "wires": [
            [
                "e3844ce7e1b2ac9a"
            ]
        ]
    },
    {
        "id": "e151017ec914297c",
        "type": "postgrestor",
        "z": "14ee6d7566a42e0e",
        "name": "",
        "query": "INSERT INTO public.mqtt_table (id, topic, payload) \nVALUES ('1','{{msg.topic}}','{{msg.payload}}'::jsonb)",
        "postgresDB": "e0ddc1469372b4eb",
        "output": true,
        "outputs": 1,
        "x": 830,
        "y": 220,
        "wires": [
            [
                "32e561ec20b60389"
            ]
        ]
    },
    {
        "id": "e3844ce7e1b2ac9a",
        "type": "rbe",
        "z": "14ee6d7566a42e0e",
        "name": "",
        "func": "rbe",
        "gap": "",
        "start": "",
        "inout": "out",
        "septopics": true,
        "property": "payload",
        "topi": "topic",
        "x": 330,
        "y": 220,
        "wires": [
            [
                "0111a731629934cf"
            ]
        ]
    },
    {
        "id": "0111a731629934cf",
        "type": "function",
        "z": "14ee6d7566a42e0e",
        "name": "function 7",
        "func": "if (msg.payload.search('pumping station') != -1) msg.payload = msg.payload.replace('pumping station', 'pumpingstation')\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 500,
        "y": 220,
        "wires": [
            [
                "871d89e7315bfc39"
            ]
        ]
    },
    {
        "id": "32e561ec20b60389",
        "type": "debug",
        "z": "14ee6d7566a42e0e",
        "name": "debug 6",
        "active": true,
        "tosidebar": true,
        "console": true,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 930,
        "y": 140,
        "wires": []
    },
    {
        "id": "871d89e7315bfc39",
        "type": "function",
        "z": "14ee6d7566a42e0e",
        "name": "function 8",
        "func": "var input = msg.payload\n\n// Split the input string into key-value pairs\nvar pairs = input.split(\",\");\n\n// Initialize an array to store the JSON objects\nvar output = [];\n\n// Loop through each key-value pair\nfor (var i = 0; i < pairs.length; i++) {\n    // Split the pair into key and value\n    var parts = pairs[i].split(\" \");\n\n    // Create a JSON object with the key and value\n    var obj = {\n        \"key\": parts[0],\n        \"value\": parts[1]\n    };\n\n    // Add the object to the output array\n    output.push(obj);\n}\nmsg.payload = JSON.stringify(output)\n// Output the array of JSON objects\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 660,
        "y": 220,
        "wires": [
            [
                "32e561ec20b60389",
                "e151017ec914297c"
            ]
        ]
    },
    {
        "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": "e0ddc1469372b4eb",
        "type": "postgresDB",
        "name": "postgres@127.0.0.1:5432/users",
        "host": "127.0.0.1",
        "hostFieldType": "str",
        "port": "5432",
        "portFieldType": "num",
        "database": "users",
        "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": "postgres",
        "passwordFieldType": "str"
    }
]

Can you help me to debug it?

2

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