Stuck! at Send multiple sensor data to a PSQL

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!

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 :wink:

1 Like

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.
image

the sample table of the database
image

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.

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!

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

1 Like

Thank you. I'll try.

It works.. Thank you very much! :blush:

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