Insert into PostgreSQL database

General
1

Hello guys,
I'm wondering what I'm doing wrong here, I have been scouring the forum / google to find the answer and I think I'm nearly there but as a new user of Node-Red I can't figure out why this is not working:

image
image1920Ă—1080 253 KB

My Template Node looks like this:
image
image1920Ă—1040 99.2 KB

My Function Node looks like this:
image
image1920Ă—1040 118 KB

I'm using the 'node-red-contrib-re-postgres' Node in my Flow... Can anyone please advise?
Thank you!

2

I couldn't see any errors shown in the debug output, but the screenshot is a bit small so I may be missing something.
How does that node indicate if there is an error in the query? If you don't know then what happens if you intentionally make a syntax error in the query?

[Edit] It might be worth enabling the output and see if there is anything useful there. Also add a Catch node and see if it catches anything from the node.

3

Hi Colin,

I enabled the output linked to a debug node but nothing is show - not even when I add the Catch :frowning:

It would be awesome to have an example flow that works so I can figure out where i'm going wrong.
Here is an isolated flow which is trying to select * from a PostgreSQL table... Can you take a look and provide some guidance please?

[{"id":"64a54801.081918","type":"tab","label":"Flow 5","disabled":false,"info":""},{"id":"86a304d9.a3a9f8","type":"inject","z":"64a54801.081918","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":320,"y":300,"wires":[["8ba1dcb5.0e777"]]},{"id":"574fbc21.9c66a4","type":"template","z":"64a54801.081918","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"select * from measurement where sensor_id = 'fc306a1a-3282-4d37-a95c-56d5621c1379'","output":"str","x":640,"y":300,"wires":[["8baad2ae.fe0e","fb664bf6.df2548"]]},{"id":"8ba1dcb5.0e777","type":"function","z":"64a54801.081918","name":"","func":"msg.queryParameters = msg.payload;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":480,"y":300,"wires":[["574fbc21.9c66a4"]]},{"id":"8baad2ae.fe0e","type":"postgres","z":"64a54801.081918","postgresdb":"1a9f0afa.d4dbb5","name":"","output":true,"perrow":false,"rowspermsg":"1","outputs":1,"x":860,"y":300,"wires":[["fb664bf6.df2548"]]},{"id":"fb664bf6.df2548","type":"debug","z":"64a54801.081918","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":990,"y":220,"wires":[]},{"id":"1a9f0afa.d4dbb5","type":"postgresdb","z":"","hostname":"localhost","port":"5432","db":"testdb2","ssl":false}]

Thank you!

4

You have to follow this FAQ entry when posting flows, otherwise they are not importable.
However, I note that there is a sample flow on the node's page, so I suggest you start with that https://flows.nodered.org/node/node-red-contrib-re-postgres

5

Hi Colin,
I already did start with that example but am having no luck... Here is the Flow properly added to the forum:

[{"id":"8ba1dcb5.0e777","type":"function","z":"64a54801.081918","name":"","func":"msg.queryParameters = msg.queryParameters || {};\n//msg.queryParameters.param1 = 1;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":500,"y":300,"wires":[["574fbc21.9c66a4"]]}]

In this example I am trying to insert a single entry into PostgrSQL but I can even make this work, any guidance would be gratly appreciated, thank you.

6

You said that you started with the sample flow. Did that work?

Assuming it did then, moving on to your flow, add a debug node showing what is going into the db node. Set it to Show Complete Message and show us what it gives.

Did you try my suggestion of feeding an obviously invalid query into the db node and seeing what happens?

7

Hi Colin,
Nothing is working, regardless of how I construct the flow nodes... I tried this every way I can think of - here is the Show-Complete-Message from the debug node as requested:

image
image1585Ă—449 24.4 KB

It looks to me to be correct but it just does not work, do you have a working Flow you can share with me?
This way I can eliminate anything I'm doing wrong and test this against a know-working example, thank you.

8

That isn't the sample flow, get that working first.

9

How many different postage nodes do you have installed?

If you have more than one, there is a possibility that there is a conflict. I know at least one node-red-contrib-postgres-multi has a compatibility warning in it's README:

This module is designed assuming you will only use this one or Kris' original version in a project, but not both at the same time.

10

Auto correct strikes again!

A good possibility though. @jomacdon, if you have more than one then remove the ones you don't want as @zenofmud suggests. If you think you only have one then, if the sample flow still does not work, then start node red in a terminal and look at the output, if it looks ok to you then post it here (use the </> button and copy/paste it in).

1 Like
11

I have just one postgres in this project the 'node-red-contrib-re-postgres' one... @Collin you are correct, it isn't the flow from the example because it doesn't work - none work for me :frowning:
My Node-Red is running from a Windows Schedule task so starting in in terminal shows errors, I think I need to reboot my machine which is a shame because I have a few other things running... Have either of you two guys been able to get this working?

12

image
image1920Ă—1080 243 KB

13

Have you tried the example on the node's flow page that @Colin suggested three days ago?

14

Stop the background one running and start it in a terminal.

15

Hello,
Yes, this is where I started - with the example on the node page, I also searched for a long time to find the answers before coming to the Forum

16

Did the example flow work for you?

If not, did you create the database/table that goes with that flow?

17

Hi, no the example flow didn't work for me - I even wrote the query to match my database but again without any luck, as requested, my terminal outpout:{ _msgid: '5f367f7b.8431f', payload: "insert into dev (content) values ('test1')", topic: '', queryParameters: {} }
This should insert just "test1" into my table called "dev" in column "content".
I am pulling my hair out on this as I can't see why it's not working :frowning:

18

Post the flow you used for the sample which didn't work. Until you get the sample working there is no point trying the insert.

19

OK, here are some more details...
My PostgreSQL table:

image
image1920Ă—1080 295 KB

The Flow I used based on the example provided

[{"id":"a719ec3f.5e1e9","type":"tab","label":"Flow 3","disabled":false,"info":""},{"id":"9db08151.9b72e","type":"inject","z":"a719ec3f.5e1e9","name":"trigger","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":150,"y":140,"wires":[["e4e62dfc.b9685"]]},{"id":"90234c95.12b99","type":"template","z":"a719ec3f.5e1e9","name":"format query","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"select * from measurement where numeric_value > $param1","x":553,"y":138,"wires":[["bcb21762.870678","e1c9f296.68ee7"]]},{"id":"bcb21762.870678","type":"postgres","z":"a719ec3f.5e1e9","postgresdb":"47c2ceaa.9ce9b","name":"","output":true,"perrow":false,"rowspermsg":"1","outputs":1,"x":720,"y":134,"wires":[["b9fb2278.0a17f"]]},{"id":"a37e5920.3aaab8","type":"debug","z":"a719ec3f.5e1e9","name":"query output","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":950,"y":240,"wires":[]},{"id":"599b2e8d.55434","type":"http request","z":"a719ec3f.5e1e9","name":"","method":"POST","ret":"txt","url":"http://localhost:1880/incoming_data","x":530,"y":500,"wires":[["43322974.6b14d8"]]},{"id":"43322974.6b14d8","type":"debug","z":"a719ec3f.5e1e9","name":"http result","active":false,"console":"false","complete":"payload","x":713,"y":500,"wires":[]},{"id":"e4e62dfc.b9685","type":"function","z":"a719ec3f.5e1e9","name":"setup params","func":"msg.queryParameters = msg.queryParameters || {};\nmsg.queryParameters.param1 = 1000;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":353,"y":136,"wires":[["90234c95.12b99"]]},{"id":"e1c9f296.68ee7","type":"debug","z":"a719ec3f.5e1e9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":260,"wires":[]},{"id":"b9fb2278.0a17f","type":"json","z":"a719ec3f.5e1e9","name":"","property":"payload","action":"","pretty":false,"x":910,"y":140,"wires":[["a37e5920.3aaab8"]]},{"id":"47c2ceaa.9ce9b","type":"postgresdb","z":"a719ec3f.5e1e9","hostname":"localhost","port":"5432","db":"testdb2","ssl":false}]

Thank you

20

What do you see coming out of the db node when you run it (before the JSON node).

[Edit] Also you still haven't posted the startup log from stopping the background version of node red and starting it in a terminal.