# Stuck! at Send multiple sensor data to a PSQL

**URL:** <https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338>\
**Category:** General\
**Tags:** database, raspberry-pi\
**Created:** [19 April 2024 10:56 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338 "2024-04-19T10:56:16Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![RND\_NIM](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@RND\_NIM](https://discourse.nodered.org/u/RND_NIM)\
**Post date:** [19 April 2024 10:56 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/1 "2024-04-19T10:56:17Z")

</div>

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.

 ![Screenshot 2024-04-19 162119](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/4/d422ad09f27595ed1294b66f9ced274fcaa94ccf.png)

My query code

```auto
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!

---

<div class="post-metadata">

**Author:** ![kitori](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kitori](https://discourse.nodered.org/u/kitori)\
**Post date:** [19 April 2024 12:06 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/2 "2024-04-19T12:06:19Z")

</div>

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:

```auto
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)
)

```

```auto
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:

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

```

Change node for preparing the data:

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/3/f3deb5b7389713fa8cdfec5b3aadf0037bd8fd25.png)

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

---

<div class="post-metadata">

**Author:** ![RND\_NIM](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@RND\_NIM](https://discourse.nodered.org/u/RND_NIM)\
**Post date:** [24 April 2024 02:47 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/3 "2024-04-24T02:47:57Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/2/d23cc0ff4b6b3c40f412221625ab4c0d307bc894.png)

the sample table of the database  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/9/e9f766f66321e45d391878892acd71a005a46c01.png)

and the query for the table

```auto
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.

---

<div class="post-metadata">

**Author:** ![RND\_NIM](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@RND\_NIM](https://discourse.nodered.org/u/RND_NIM)\
**Post date:** [25 April 2024 09:03 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/4 "2024-04-25T09:03:34Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/d/8da8bfeb977819c692dc8312a4db96be1b3f40b3.png)

```auto
[
    {
        "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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/5/15096f27f4f3678943e8398df3a4ea702439578b.png)

can you please guide me through this.  
Thank You!

---

<div class="post-metadata">

**Author:** ![kitori](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kitori](https://discourse.nodered.org/u/kitori)\
**Post date:** [25 April 2024 11:49 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/5 "2024-04-25T11:49:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![RND\_NIM](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@RND\_NIM](https://discourse.nodered.org/u/RND_NIM)\
**Post date:** [3 May 2024 02:13 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/6 "2024-05-03T02:13:27Z")

</div>

Thank you. I'll try.

---

<div class="post-metadata">

**Author:** ![RND\_NIM](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@RND\_NIM](https://discourse.nodered.org/u/RND_NIM)\
**Post date:** [3 May 2024 08:17 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/7 "2024-05-03T08:17:49Z")

</div>

It works.. Thank you very much! 😊

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [17 May 2024 08:18 UTC](https://discourse.nodered.org/t/stuck-at-send-multiple-sensor-data-to-a-psql/87338/8 "2024-05-17T08:18:17Z")

</div>

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