MQTT & POSTGRESQL on Raspberry

[bert2713d]

Hello

I have a project to be able to view the data of a home temperature sensor by internet.
Here the main way used :

DS18BS20 Temperature sensor ------> ESP8266 NODEMCU --------> MQTT MOSQQUITTO Broker on a Raspberry PI3 ---------> Node-red on Raspberry ----------> Postgresql database on Raspberry ---------> grafana graphic maker on raspberry.

I was going step by step and I am very near to the end of my project . All step down are working execpted one : the link beetwen node-red and Postgresql.

I arrive to insert some data inside Postgresql database but I do not arrive to sent the value received from the MQTT. I have seach on the net some syntax example on this simple wish without succeed.

Could you help .

Here the node-red that I am trying to set-up

[{"id":"3af358c1.14d9b8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"71b4581.02c91a8","type":"mqtt in","z":"3af358c1.14d9b8","name":"","topic":"test","qos":"1","datatype":"auto","broker":"83a74e3d.f1929","x":80,"y":240,"wires":[["2a03924e.89ac8e","a45e3f89.02e63","211f943c.4bdd5c"]],"outputLabels":["data"]},{"id":"87331ac4.41ea38","type":"postgrestor","z":"3af358c1.14d9b8","name":"Postgre","query":"INSERT INTO bertrand (date) VALUES (value);\n\n\n","postgresDB":"45e64d43.dae164","output":true,"outputs":1,"x":560,"y":320,"wires":[]},{"id":"211f943c.4bdd5c","type":"function","z":"3af358c1.14d9b8","name":"data","func":"var payload =msg.payload;\nglobal.set("value", payload);\nreturn null;","outputs":1,"noerr":0,"x":310,"y":320,"wires":[["87331ac4.41ea38"]]},{"id":"2a03924e.89ac8e","type":"debug","z":"3af358c1.14d9b8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":280,"y":200,"wires":},{"id":"a45e3f89.02e63","type":"ui_gauge","z":"3af358c1.14d9b8","name":"","group":"370e7506.73ffaa","order":0,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":"1023","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":260,"y":120,"wires":},{"id":"83a74e3d.f1929","type":"mqtt-broker","z":"","name":"Mosquitto","broker":"192.168.0.29","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"45e64d43.dae164","type":"postgresDB","z":"","name":"Raspberry","host":"192.168.0.29","port":"5432","database":"essai","ssl":false,"max":"10","min":1,"idle":"1000"},{"id":"370e7506.73ffaa","type":"ui_group","z":"","name":"exemple","tab":"da2f1fc9.a34ae","disp":true,"width":"6","collapse":false},{"id":"da2f1fc9.a34ae","type":"ui_tab","z":"","name":"Tab 1","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

in advance thank

regards

Bertrand

[Colin]

Please see the link below on how to share a flow here so that it is importable. You can edit your previous post accordingly.
Do you have a particular reason for wanting to use postgresql? You might like to consider influxdb which is designed for exactly the requirement you describe. There are influx nodes that make it very easy to output to influx (node-red-contrib-influxdb) and grafana has built in support for influx.

How to share code or flow json

To share code samples or flow json in this forum, you need to take care to format it properly so that it is displayed correctly. The easiest way to do that is to click the 'Preformatted Text' button in the toolbar: [image] Then paste in your code or flow json. You should end up with three back-tick characters - ``` - on their own line before and after the code. On standard US English keyboards, this is on the same key as the ~ character. [IMG_20181111_155651] Using the backticks will ens…

[bert2713d]

I have choose postgresql just because I have use it for previous project.
You have right , I will shift to influxdb and use their nodes.

thanks

Bertrand

[bert2713d]

After looking the "influxdb" details , I am still seaching for a very simple example to insert a value from mqtt in a database with node-red.

[Colin]

Do you mean that you do not understand how to format the message to send to the value to the influx out node?

[Colin]

Something like this may be what you want. This shows how to write to a fixed field. In fact I use field names that can be programmatically derived from the mqtt topic so I can determine the field name automatically.

[{"id":"24c115a6.3b06d2","type":"function","z":"514a90a5.c7bae8","name":"Format fieldname and value","func":"/* Payload should be a value to be written to influxdb with field name \"the_field_name\"\n*/\nvar msg2 = null;\nvar value = Number(msg.payload);  // convert it to a number in case it is a string\nif (!isNaN(value)) {\n    msg2 = {payload: {\"the_field_name\": value}};\n}\nreturn msg2;","outputs":1,"noerr":0,"x":338,"y":379,"wires":[[]]},{"id":"f51e84dd.90c6b8","type":"comment","z":"514a90a5.c7bae8","name":"From MQTT","info":"","x":117.5,"y":380,"wires":[]},{"id":"bcad6e7e.f0585","type":"comment","z":"514a90a5.c7bae8","name":"To Influxdb Out node","info":"","x":587,"y":379,"wires":[]}]

[bert2713d]

Hi Colin

Thank you for your example.
However I still do not arrive to write in the database. To test , I am using a ESP8266 which sent a mqtt request with on value (potentiometer value) . by influxdb web interface , I have created a "essai" database. When I am using your script , it is created a measurment of the name of the measurement box of "influxdb out node" . But I do not arrive to read any value on the database.
down my flow.

[{"id":"3af358c1.14d9b8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"71b4581.02c91a8","type":"mqtt in","z":"3af358c1.14d9b8","name":"","topic":"test","qos":"1","datatype":"auto","broker":"83a74e3d.f1929","x":80,"y":240,"wires":[["a45e3f89.02e63","bfdc8c40.3b455","7e8a39b8.18c8f8"]],"outputLabels":["data"]},{"id":"a45e3f89.02e63","type":"ui_gauge","z":"3af358c1.14d9b8","name":"","group":"370e7506.73ffaa","order":0,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":"1023","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":260,"y":120,"wires":[]},{"id":"bfdc8c40.3b455","type":"debug","z":"3af358c1.14d9b8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":250,"y":240,"wires":[]},{"id":"da0448bd.07e6b8","type":"influxdb out","z":"3af358c1.14d9b8","influxdb":"1321c4e4.97cd1b","name":"temp","measurement":"temp","precision":"s","retentionPolicy":"","x":610,"y":300,"wires":[]},{"id":"7e8a39b8.18c8f8","type":"function","z":"3af358c1.14d9b8","name":"Format fieldname and value","func":"/* Payload should be a value to be written to influxdb with field name \"the_field_name\"\n*/\nvar msg2 = null;\nvar value = Number(msg.payload); // convert it to a number in case it is a string\nif (!isNaN(value)) {\n msg2 = {payload: {temp: value}};\n}\nreturn msg2;","outputs":1,"noerr":0,"x":300,"y":300,"wires":[["da0448bd.07e6b8"]]},{"id":"83a74e3d.f1929","type":"mqtt-broker","z":"","name":"Mosquitto","broker":"192.168.0.29","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"370e7506.73ffaa","type":"ui_group","z":"","name":"exemple","tab":"da2f1fc9.a34ae","disp":true,"width":"6","collapse":false},{"id":"1321c4e4.97cd1b","type":"influxdb","z":"3af358c1.14d9b8","hostname":"192.168.0.29","port":"8086","database":"essai","name":"Base influx","usetls":false,"tls":""},{"id":"da2f1fc9.a34ae","type":"ui_tab","z":"","name":"Tab 1","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

[ukmoose]

Add debug nodes along your flow (give each a name so you can easily identify each one in the debug panel)
Do you see what you expect to see?