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:
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.
Hi Colin,
I enabled the output linked to a debug node but nothing is show - not even when I add the Catch 
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!
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
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.
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?
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:
That isn't the sample flow, get that working first.
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.
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).
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
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?
Have you tried the example on the node's flow page that @Colin suggested three days ago?
Stop the background one running and start it in a terminal.
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
Did the example flow work for you?
If not, did you create the database/table that goes with that flow?
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
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.
OK, here are some more details...
My PostgreSQL table:
[{"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
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.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy