Node-Red to PostgreSQL

[Chrolloo123]

I just recently used Node-red. I created a function that can save to local postgres using node-red-contrib-postgres-multi package

I have a data from my mqtt client:

    msg.topic = 'data'
    msg.payload = [
            { key: 'pumpingstation', value: '0.00' },
            { key: 'centrifuge', value: '21.08' },
            { key: 'Dafpump1', value: '5.20' },
            { key: 'Dafpump2', value: '23.10' }
          ]

in my function node it look like this:

    var topic1 = msg.topic;
    var payload1 = msg.payload;
    
    msg.payload = [
        {
            query: 'INSERT INTO mqtt_table (topic, payload) values (1, $topic), (2, $payload)',
            params: {
                topic: topic1,
                payload: payload1,
            },
        },
        {
            query: 'commit',
        },
    ];
    // Return the modified message
    return msg;

and in my postgres node I have set up my credentials correctly. There's no error but it's not saving to my database. Is there something wrong with my process? Let me know if my question is not clear. Thanks!

[zenofmud]

add a debug node (set to display the complete msg object) to the output of the Postgres node and also add a catch node connected to another debug node (set to display the complete msg object) and see what shows up.

[Chrolloo123]

on function 3 it shows

[
  {
    query: 'INSERT INTO mqtt_table (topic, payload) values (1, $topic), (2, $payload)',
    params: {
      topic: 'data',
      payload: [
        { key: 'pumpingstation', value: '1.00' },
        { key: 'centrifuge', value: '21.08' },
        { key: 'Dafpump1', value: '5.20' },
        { key: 'Dafpump2', value: '23.10' }
      ]
    }
  },
  { query: 'commit' }
]

there's no debug output on postgres node.

[zenofmud]

what it the full name of the postgres node you are using?

[Chrolloo123]

node-red-contrib-postgres-multi

[zenofmud]

That node hasn't been updated in over 5 years and if you go to the GitHub page and look at the issues it looks like it is broken

I would try another node.

[Chrolloo123]

can you recommend a node for postgres?

[zenofmud]

Since I don't use Postgres I can't give an opinion. I would suggest you

1. do a search on the forum using Postgres and look at what others are using
2. go to the ~Flowtab on the forum, search forPostgres` and go to the GitHub pages for the nodes and look at the issues for each node.

That should help you decide.