I'm a beginner with Node-Red, I try to insert data to PostgreSQL but the data is still not inserted on the table. I don't have any idea what the reason is?
Perhaps someone could help me to solve it? thank you
I attached a screenshot of my work...
Putting the query into msg.topic does not seem right to me. Can you confirm that this is correct?
Also you might want to use a different node for PostgreSQL integration.
I try to run and click timestamp, this is the result.. there is no error but the value is not show onthe data base
this is my project flow,
inserttopostgre.txt (9.8 KB)
Try to put your query into msg.payload instead of msg.topic and it should work.
Edit: Hm okay, you changed the flow already in the meantime. However, I tested the node and it worked.
Also there is no error shown because you need to put a debug node at the output of the sql node
Try this:
[
{
"id": "48acf0a74b57fcc9",
"type": "tab",
"label": "Flow 5",
"disabled": false,
"info": ""
},
{
"id": "2e20d6086aa2abc8",
"type": "postgres",
"z": "48acf0a74b57fcc9",
"postgresdb": "f368632229c86926",
"name": "PgData",
"output": true,
"perrow": false,
"rowspermsg": "1",
"return_on_error": false,
"limit_queries": "0",
"limit_by": "payload",
"limit_value": "1",
"limit_drop_intermediate": false,
"limit_drop_if_in_queue": false,
"outputs": true,
"x": 820,
"y": 320,
"wires": [
[
"f93122ef2bce6d94"
]
]
},
{
"id": "d529a78a2e573d90",
"type": "inject",
"z": "48acf0a74b57fcc9",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payloadType": "date",
"x": 140,
"y": 120,
"wires": [
[
"4a76f8c584e41fb2",
"15b28e14c0288025",
"4217e30cd0c26996",
"e07778d9b2c8fb02"
]
]
},
{
"id": "4a76f8c584e41fb2",
"type": "s7comm read",
"z": "48acf0a74b57fcc9",
"connection": "8243af575e4cc6d1",
"payload": "{\"S7_Type\":\"DB\",\"S7_DBnum\":\"1\",\"S7_Datatype\":\"W\",\"S7_Offset\":\"0\",\"S7_BitOffset\":\"0\",\"S7_Quantity\":\"1\",\"S7_Name\":\"Data1\"}",
"s7Name": "Data1",
"topic": "",
"name": "",
"signalSetted": false,
"none": "true",
"repeat": "",
"once": false,
"x": 380,
"y": 120,
"wires": [
[
"3b60fa3d52f17542"
]
]
},
{
"id": "5e6689052a9c45e6",
"type": "comment",
"z": "48acf0a74b57fcc9",
"name": "Read from PLC",
"info": "",
"x": 140,
"y": 40,
"wires": []
},
{
"id": "20cc6b5c533c30e6",
"type": "function",
"z": "48acf0a74b57fcc9",
"name": "Write to PostgreSQL",
"func": "var a = global.get('value1');\nvar b = global.get('value2');\nvar c = global.get('value3');\nvar now = new Date().toLocaleString(\"DE\");\n\nmsg.payload = \"INSERT INTO temperature (speed1,speed2,speed3) VALUES (\"+a+\",\"+b+\",\"+c+\");\"\nreturn msg;\n\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 580,
"y": 320,
"wires": [
[
"f5e2c8b9af25420c",
"2e20d6086aa2abc8"
]
]
},
{
"id": "3b60fa3d52f17542",
"type": "split",
"z": "48acf0a74b57fcc9",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": "2",
"arraySpltType": "len",
"stream": false,
"addname": "parse1",
"x": 550,
"y": 120,
"wires": [
[
"97b8418a2b3c7f7a",
"f5e2c8b9af25420c"
]
]
},
{
"id": "97b8418a2b3c7f7a",
"type": "function",
"z": "48acf0a74b57fcc9",
"name": "",
"func": "global.set('value1',msg.payload);\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 740,
"y": 120,
"wires": [
[]
]
},
{
"id": "15b28e14c0288025",
"type": "s7comm read",
"z": "48acf0a74b57fcc9",
"connection": "8243af575e4cc6d1",
"payload": "{\"S7_Type\":\"DB\",\"S7_DBnum\":\"1\",\"S7_Datatype\":\"W\",\"S7_Offset\":\"2\",\"S7_BitOffset\":\"0\",\"S7_Quantity\":\"1\",\"S7_Name\":\"Data2\"}",
"s7Name": "Data2",
"topic": "",
"name": "",
"signalSetted": false,
"none": "true",
"repeat": "",
"once": false,
"x": 380,
"y": 180,
"wires": [
[
"eca912ed55ac92e7"
]
]
},
{
"id": "4217e30cd0c26996",
"type": "s7comm read",
"z": "48acf0a74b57fcc9",
"connection": "8243af575e4cc6d1",
"payload": "{\"S7_Type\":\"DB\",\"S7_DBnum\":\"1\",\"S7_Datatype\":\"W\",\"S7_Offset\":\"4\",\"S7_BitOffset\":\"0\",\"S7_Quantity\":\"1\",\"S7_Name\":\"Data3\"}",
"s7Name": "Data3",
"topic": "",
"name": "",
"signalSetted": false,
"none": "true",
"repeat": "",
"once": false,
"x": 380,
"y": 240,
"wires": [
[
"f4dd6d25ef641ba8"
]
]
},
{
"id": "eca912ed55ac92e7",
"type": "split",
"z": "48acf0a74b57fcc9",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "parse2",
"x": 550,
"y": 180,
"wires": [
[
"e36f24485a5ee562",
"f5e2c8b9af25420c"
]
]
},
{
"id": "f4dd6d25ef641ba8",
"type": "split",
"z": "48acf0a74b57fcc9",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "parse3",
"x": 550,
"y": 240,
"wires": [
[
"6b21c14081f45bfd",
"f5e2c8b9af25420c"
]
]
},
{
"id": "e36f24485a5ee562",
"type": "function",
"z": "48acf0a74b57fcc9",
"name": "",
"func": "global.set('value2',msg.payload);\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 740,
"y": 180,
"wires": [
[]
]
},
{
"id": "6b21c14081f45bfd",
"type": "function",
"z": "48acf0a74b57fcc9",
"name": "",
"func": "global.set('value3',msg.payload);\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 740,
"y": 240,
"wires": [
[]
]
},
{
"id": "f5e2c8b9af25420c",
"type": "debug",
"z": "48acf0a74b57fcc9",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 750,
"y": 60,
"wires": []
},
{
"id": "e07778d9b2c8fb02",
"type": "delay",
"z": "48acf0a74b57fcc9",
"name": "",
"pauseType": "delay",
"timeout": "1",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"allowrate": false,
"x": 370,
"y": 320,
"wires": [
[
"20cc6b5c533c30e6"
]
]
},
{
"id": "f93122ef2bce6d94",
"type": "debug",
"z": "48acf0a74b57fcc9",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1010,
"y": 320,
"wires": []
},
{
"id": "f368632229c86926",
"type": "postgresdb",
"cfgname": "postgres",
"hostname": "localhost",
"port": "5432",
"db": "node-red-temp",
"ssl": false
},
{
"id": "8243af575e4cc6d1",
"type": "s7comm",
"ip": "192.168.0.1",
"port": "102",
"rack": "0",
"slot": "1",
"payload": [
{
"S7_Type": "DB",
"S7_DBnum": "1",
"S7_Datatype": "W",
"S7_Offset": "0",
"S7_BitOffset": "0",
"S7_Quantity": "1",
"S7_Name": "Data1"
},
{
"S7_Type": "DB",
"S7_DBnum": "1",
"S7_Datatype": "W",
"S7_Offset": "2",
"S7_BitOffset": "0",
"S7_Quantity": "1",
"S7_Name": "Data2"
},
{
"S7_Type": "DB",
"S7_DBnum": "1",
"S7_Datatype": "W",
"S7_Offset": "4",
"S7_BitOffset": "0",
"S7_Quantity": "1",
"S7_Name": "Data3"
},
{
"S7_Type": "DB",
"S7_DBnum": "1",
"S7_Datatype": "W",
"S7_Offset": "6",
"S7_BitOffset": "0",
"S7_Quantity": "1",
"S7_Name": "Data4"
},
{
"S7_Type": "DB",
"S7_DBnum": "1",
"S7_Datatype": "W",
"S7_Offset": "8",
"S7_BitOffset": "0",
"S7_Quantity": "1",
"S7_Name": "Data5"
},
{
"S7_Type": "M",
"S7_DBnum": "0",
"S7_Datatype": "B",
"S7_Offset": "0",
"S7_BitOffset": "3",
"S7_Quantity": "3",
"S7_Name": "MB1"
}
]
}
]
Once the database is working you might want to change the flow here and there to make it (more) stable.
Thanks for the response, but after I try this.. the data is still not inserted on the database table even there was no error.
Do I miss something in the PostgreSQL setting or something else?
Silly question, are you running both Node-RED and PostgreSQL on the same machine?
Yes.. im running on the same laptop. is that problem ?
No, not a problem. I just thought, maybe you are accessing the wrong database / server.
I've rechecked and tried to create a new database but it still not working. the data couldn't be inserted into the table. even I follow step-by-step tutorials from youtube but didn't work.
I also tried to connect with the MySQL node and everything goes normal and work. the data can be inserted into the MySQL database.
So, why is the reason actually by your experiences guess? maybe is there any special setting if I want to run PostgreSQL on Windows ? thank you for your response before..
It's hard to say, really. I ran the tests on a linux system. Did you try to use this node as well? It seems newer and the documentation is better. Also it was recommended by @Steve-Mcl, so you can't go wrong using that one, I guess 
- do
SELECTqueries work? - have you tried to test with
ODBCand for exampleExcel?
did you test with postgresql successfully in linux system sir ?
i've tried with other node but still the same. maybe i'll try also to install linux system and also try it there. but i'm still wondering why it's not working on windows...
Yes these were the versions I've used
- Ubuntu: 20.04.1 LTS
- Node-RED: 2.0.6
- Nodejs: 12.22.6
- postgres: 12.8
- node-red-contrib-re-postgres: 0.3.2
Do you have an example for the Posgrestor node sir ?
if you have ever tried could you share a flow example.
thank you
Yes it seems to work without problems:
[
{
"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"
}
]
Careful when sharing this one. The user/password won't be removed!
Wow... it's running as well sir.
I really appreciate your help.
Thank you so much
I'm glad to hear that 
Are your servers (Node-RED and PSQL) still running on Windows or did you switch to a Linux OS?
yes, sir
I'm still running on Windows 10.
Node-RED version: v2.0.6
Node.js version: v14.18.0
PostgreSQL14
I'm sorry
I have a little bit further question, how to send the integer data that I get from PLC to msg.speed1 for example.!
which node should I use to send the value of PLC to the msg.speed1?
in this screenshot, we put the fixed value from timestamp
