Stuck! at Send multiple sensor data to a PSQL

RND_NIM · 19 April 2024 10:56

I am new to database and node-red. Here is my setup.

Raspberry Pi 4
Raspbian OS
Node-Red
Pi plates - (THERMOplate)
K-Type sensors
Database - PSQL

I have created a flow with one sensor output and it works and no errors.

My query code

INSERT INTO data (temperature1, time)
VALUES ({{msg.payload}}, CURRENT_TIMESTAMP)

This will update the database table and seems okay.

But the problem is: I need to send multiple sensor data outputs to the database and have no idea how to do it. Tried various things with function nodes and so on. And no luck. I wonder if anyone can help me with this.

I need to update the database with multiple data on a single raw and separate columns.

Thank you!

kitori · 19 April 2024 12:06

you probalby need new table with a foreign key.

im sending from ~20 location with 1- 20 datapoints over mqtt into a postgres db (Timescaledb) with this simple schema:

CREATE TABLE IF NOT EXISTS public.sensor
(
    id integer NOT NULL DEFAULT nextval('sensor_id_seq'::regclass),
    description text COLLATE pg_catalog."default",
    unit text COLLATE pg_catalog."default",
    scale double precision DEFAULT 1,
    mqtt_topic text COLLATE pg_catalog."default",
    CONSTRAINT sensor_pkey PRIMARY KEY (id)
)

CREATE TABLE IF NOT EXISTS public.sensor_data
(
    "time" timestamp with time zone NOT NULL DEFAULT now(),
    sensor_id integer,
    value double precision,
    CONSTRAINT sensor FOREIGN KEY (sensor_id)
        REFERENCES public.sensor (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

I have a node-red instance which connect to the mqtt topics, preparing the messages and route it into the db via link nodes

Query of the postgres node:

INSERT INTO sensor_data (sensor_id,value)
VALUES ($sensor_id,$value);

Change node for preparing the data:

[{"id":"6c9c939246770237","type":"change","z":"6a42cb14df845d14","name":"","rules":[{"t":"set","p":"queryParameters.sensor_id","pt":"msg","to":"1","tot":"num"},{"t":"set","p":"queryParameters.value","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":930,"y":300,"wires":[["1adca4880ad65a21"]]}]

grafik

im aggregating the values like hell, so the default value of the time column is enough for my use case

RND_NIM · 24 April 2024 02:47

Thank you very much Kitori. Sorry for the late reply as I was out for few days. I'll try this.

And about the mqtt. I am using Raspberry Pi 4 with THERMOplate and K-Type sensor. This is my THERMOplate node.

the sample table of the database

and the query for the table

CREATE TABLE data ( 
  number serial,
  time timestamp,
  temperature1 numeric,
  temperature2 numeric,
  temperature3 numeric
);

Actually I didn't look into mqtt yet. I was wondering; am I already using mqtt with these piPlates? My setup don't use GPIO pins directly. I have connected the sensor to the piplate and the piplate to the rspi. Sorry for these questions. I am an absolute beginner to this and still learning things while trying this setup.

RND_NIM · 25 April 2024 09:03

Hi again,

I've followed your guide (hoping am doing it in the right way).
This is not allowing me to upload the json file. So I'll paste that here.

[
    {
        "id": "6fcf1b94141223d4",
        "type": "inject",
        "z": "05a3e40a24ac33e4",
        "name": "temp",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 310,
        "y": 520,
        "wires": [
            [
                "d455797331e7e193",
                "fcbf31eb7b30634e",
                "e09c718f76df8566",
                "649d872205dbacc4"
            ]
        ]
    },
    {
        "id": "d455797331e7e193",
        "type": "ppTHERMO",
        "z": "05a3e40a24ac33e4",
        "config_plate": "7cb7053c56f101da",
        "name": "Tem.1",
        "channel": "1",
        "scale": "c",
        "tc_type": "k",
        "x": 530,
        "y": 520,
        "wires": [
            [
                "bfe25697273883b3"
            ]
        ]
    },
    {
        "id": "fcbf31eb7b30634e",
        "type": "ppTHERMO",
        "z": "05a3e40a24ac33e4",
        "config_plate": "7cb7053c56f101da",
        "name": "Tem.2",
        "channel": "2",
        "scale": "c",
        "tc_type": "k",
        "x": 530,
        "y": 580,
        "wires": [
            [
                "2aece502dae61a40"
            ]
        ]
    },
    {
        "id": "e09c718f76df8566",
        "type": "ppTHERMO",
        "z": "05a3e40a24ac33e4",
        "config_plate": "7cb7053c56f101da",
        "name": "Tem.3",
        "channel": "3",
        "scale": "c",
        "tc_type": "k",
        "x": 530,
        "y": 640,
        "wires": [
            [
                "54440b3e54e98fe8"
            ]
        ]
    },
    {
        "id": "54440b3e54e98fe8",
        "type": "change",
        "z": "05a3e40a24ac33e4",
        "name": "Tem3",
        "rules": [
            {
                "t": "set",
                "p": "queryParameters.sensor_id",
                "pt": "msg",
                "to": "3",
                "tot": "num"
            },
            {
                "t": "set",
                "p": "queryParameters.value",
                "pt": "msg",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 730,
        "y": 640,
        "wires": [
            [
                "fadebb7369031b70",
                "623cfe00b8c4c581"
            ]
        ]
    },
    {
        "id": "2aece502dae61a40",
        "type": "change",
        "z": "05a3e40a24ac33e4",
        "name": "Tem2",
        "rules": [
            {
                "t": "set",
                "p": "queryParameters.sensor_id",
                "pt": "msg",
                "to": "2",
                "tot": "num"
            },
            {
                "t": "set",
                "p": "queryParameters.value",
                "pt": "msg",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 730,
        "y": 580,
        "wires": [
            [
                "fadebb7369031b70",
                "d3859e23f7ec49cb"
            ]
        ]
    },
    {
        "id": "bfe25697273883b3",
        "type": "change",
        "z": "05a3e40a24ac33e4",
        "name": "Tem1",
        "rules": [
            {
                "t": "set",
                "p": "queryParameters.sensor_id",
                "pt": "msg",
                "to": "1",
                "tot": "num"
            },
            {
                "t": "set",
                "p": "queryParameters.value",
                "pt": "msg",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 730,
        "y": 520,
        "wires": [
            [
                "fadebb7369031b70",
                "195a1dcc565e0168"
            ]
        ]
    },
    {
        "id": "fadebb7369031b70",
        "type": "postgresql",
        "z": "05a3e40a24ac33e4",
        "name": "data",
        "query": "INSERT INTO sensor_data (sensor_id,value)\nVALUES ($sensor_id,$value);",
        "postgreSQLConfig": "c74e67cc43a6d4d2",
        "split": false,
        "rowsPerMsg": 1,
        "outputs": 1,
        "x": 1010,
        "y": 440,
        "wires": [
            [
                "587ed105ddf94980"
            ]
        ]
    },
    {
        "id": "7cb7053c56f101da",
        "type": "pi_plate",
        "model": "THERMOplate",
        "address": "0"
    },
    {
        "id": "c74e67cc43a6d4d2",
        "type": "postgreSQLConfig",
        "name": "",
        "host": "",
        "hostFieldType": "str",
        "port": "",
        "portFieldType": "num",
        "database": "",
        "databaseFieldType": "str",
        "ssl": "false",
        "sslFieldType": "bool",
        "applicationName": "",
        "applicationNameType": "str",
        "max": "10",
        "maxFieldType": "num",
        "idle": "1000",
        "idleFieldType": "num",
        "connectionTimeout": "10000",
        "connectionTimeoutFieldType": "num",
        "user": "",
        "userFieldType": "str",
        "password": "",
        "passwordFieldType": "str"
    }
]

and the psql table. And I am getting this error

can you please guide me through this.
Thank You!

kitori · 25 April 2024 11:49

you must first add your sensor to the sensor table.

Then if you get data, you lookup which id this sensor has, and then you can insert it

RND_NIM · 3 May 2024 02:13

Thank you. I'll try.

RND_NIM · 3 May 2024 08:17

It works.. Thank you very much!

Topic		Replies	Views
Input multiple sensor outputs into mysql General	13	1808	16 November 2020
Node Red data to SQL database General	4	778	5 July 2019
Node-RED Function for MQTT --> Influxdb General database , function-node , mqtt	5	291	15 September 2023
Count number of sensors reporting data General function-node	7	442	27 May 2022
Best way to connect multiple serial UART sensors to Node-Red? General	3	1334	27 December 2020

Stuck! at Send multiple sensor data to a PSQL

Related Topics