hello. So I switched to node-red-contrib-postgrestor-next and It's actually working but for some reason I have a problem in json format. I have a posgres column that is jsonb and when I tried to deploy I encounter an error it says "error: invalid input syntax for type json"
my json data is a valid json I already checked that:
[{"key":"pumpingstation","value":"0.00"},{"key":"centrifuge","value":"2.2"},{"key":"Dafpump1","value":"5.01"},{"key":"Dafpump2","value":"22.69"}]
and in my postgres node query I have:
INSERT INTO public.mqtt_table (data, created_at, created_time, created_date)
VALUES ('{{msg.payload}}', '{{msg.timestamp}}', '{{msg.time}}', '{{msg.date}}');
The error exist in msg.payload. I have a function before my postgres node:
var input = msg.payload
// Split the input string into key-value pairs
var pairs = input.split(",");
// Initialize an array to store the JSON objects
var output = [];
// Loop through each key-value pair
for (var i = 0; i < pairs.length; i++) {
// Split the pair into key and value
var parts = pairs[i].split(" ");
// Create a JSON object with the key and value
var obj = {
"key": parts[0],
"value": parts[1]
};
// Add the object to the output array
output.push(obj);
}
var currentDate = new Date();
var date = currentDate.getFullYear() + "-" + (currentDate.getMonth() + 1) + "-" + currentDate.getDate();
var time = currentDate.getHours() + ":" + currentDate.getMinutes() + ":" + currentDate.getSeconds() + "." + currentDate.getMilliseconds();
msg.date = date;
msg.time = time;
msg.payload = JSON.stringify(output);
msg.timestamp = new Date().toISOString();
return msg;
for my flow I included it below.
[
{
"id": "3095d062c0825ec3",
"type": "mqtt in",
"z": "14ee6d7566a42e0e",
"name": "",
"topic": "data",
"qos": "0",
"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 (data, created_at, created_time, created_date) \nVALUES ('{{msg.payload}}', '{{msg.timestamp}}', '{{msg.time}}', '{{msg.date}}');",
"postgresDB": "e0ddc1469372b4eb",
"output": true,
"outputs": 1,
"x": 870,
"y": 220,
"wires": [
[
"32e561ec20b60389"
]
]
},
{
"id": "e3844ce7e1b2ac9a",
"type": "rbe",
"z": "14ee6d7566a42e0e",
"name": "",
"func": "rbe",
"gap": "",
"start": "",
"inout": "out",
"septopics": true,
"property": "payload",
"topi": "topic",
"x": 350,
"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": 520,
"y": 220,
"wires": [
[
"871d89e7315bfc39"
]
]
},
{
"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}\n\nvar currentDate = new Date();\nvar date = currentDate.getFullYear() + \"-\" + (currentDate.getMonth() + 1) + \"-\" + currentDate.getDate();\nvar time = currentDate.getHours() + \":\" + currentDate.getMinutes() + \":\" + currentDate.getSeconds() + \".\" + currentDate.getMilliseconds();\n\nmsg.date = date;\nmsg.time = time;\nmsg.payload = JSON.stringify(output);\nmsg.timestamp = new Date().toISOString();\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 700,
"y": 220,
"wires": [
[
"e151017ec914297c",
"32e561ec20b60389"
]
]
},
{
"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": 1050,
"y": 120,
"wires": []
},
{
"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"
}
]
flows (2).json (5.0 KB)
