Insert into PostgreSQL database

General
21

Hi Colin,
I don't see anything coming out of the db node - it's like it isn't doing anything at all...

Here is the requested startup of Node-Red in the terminal:

Microsoft Windows [Version 10.0.17763.379]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>node-red
10 Aug 11:14:36 - [info]

Welcome to Node-RED
===================

10 Aug 11:14:36 - [info] Node-RED version: v1.1.0
10 Aug 11:14:36 - [info] Node.js  version: v14.5.0
10 Aug 11:14:36 - [info] Windows_NT 10.0.17763 x64 LE
10 Aug 11:14:39 - [info] Loading palette nodes
10 Aug 11:14:49 - [info] Dashboard version 2.22.1 started at /ui
10 Aug 11:14:50 - [info] Settings file  : \Users\Administrator\.node-red\settings.js
10 Aug 11:14:50 - [info] Context store  : 'default' [module=memory]
10 Aug 11:14:50 - [info] User directory : \Users\Administrator\.node-red
10 Aug 11:14:50 - [warn] Projects disabled : editorTheme.projects.enabled=false
10 Aug 11:14:50 - [info] Flows file     : \Users\Administrator\.node-red\flows_WIN-NR2019.json
10 Aug 11:14:50 - [info] Server now running at http://127.0.0.1:1880/
10 Aug 11:14:50 - [warn]

---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.

If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.

You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------

10 Aug 11:14:50 - [info] Starting flows
10 Aug 11:14:50 - [info] Started flows
10 Aug 11:29:19 - [info] Stopping flows
10 Aug 11:29:19 - [info] Stopped flows
10 Aug 11:29:19 - [info] Starting flows
10 Aug 11:29:19 - [info] Started flows
10 Aug 11:33:43 - [info] Stopping flows
10 Aug 11:33:43 - [info] Stopped flows
10 Aug 11:33:43 - [info] Starting flows
10 Aug 11:33:43 - [info] Started flows
10 Aug 11:33:49 - [info] [debug:fb664bf6.df2548]
{
  _msgid: '5f367f7b.8431f',
  payload: "insert into dev (content) values ('test1')",
  topic: '',
  queryParameters: {}
}
10 Aug 12:05:35 - [info] Stopping flows
10 Aug 12:05:35 - [info] Stopped flows
10 Aug 12:05:35 - [info] Starting flows
10 Aug 12:05:35 - [info] Started flows
10 Aug 12:46:03 - [info] Stopping flows
10 Aug 12:46:03 - [info] Stopped flows
10 Aug 12:46:03 - [info] Starting flows
10 Aug 12:46:04 - [info] Started flows
10 Aug 12:46:19 - [info] Stopping flows
10 Aug 12:46:19 - [info] Stopped flows
10 Aug 12:46:19 - [info] Starting flows
10 Aug 12:46:19 - [info] Started flows
10 Aug 12:46:33 - [info] Stopping flows
10 Aug 12:46:33 - [info] Stopped flows
10 Aug 12:46:33 - [info] Starting flows
10 Aug 12:46:33 - [info] Started flows
10 Aug 12:47:25 - [info] Stopping flows
10 Aug 12:47:25 - [info] Stopped flows
10 Aug 12:47:25 - [info] Starting flows
10 Aug 12:47:25 - [info] Started flows

Thank you

22

I notice in your postgres table display you used
select * from public.measurement ... while in node-red you use
select * from measurement

is there a reason for this (note I've never used postgres)

23

I tried both ways actually - no difference :frowning: but good spot!

24

It seems that the node doesn't work with nodejs 14.

If the node had a github repository then you could submit an issue, but since there does not seem to be one then it seems there is not much you can do other than downgrade nodejs or use a different postgres node. Did you choose that one for a particular reason.

25

Hi Colin, very interesting... I din't select this one for any reason in particular, I just need one that works as expected. Can you PLEASE suggest a node that will work in my environment?

Many thanks!

26

I haven't used any of those nodes, and I suspect no-one here has, in which case you may just have to try them. I would start by looking at their github repositories (which should be linked from the page on the node-red flows site), see whether they are maintained and look at the Issues to see if they are being addressed.

27

So, some good news guys - I found a working PostgreSQL node it's called 'node-red-contrib-postgrestor-next' here is the results:

image
image1920×1080 147 KB

Here is my table called 'dev'
image
image1920×1080 149 KB

This is great news, at least this is working but now comes the real reason for getting a working PostgreSQL node...

I want to inject the results of an API call into the database table called 'sensors', here is what is happening in the flow which is bypassing the PostgreSQL node for now:

image
image850×709 46.3 KB

Here is my PostgreSQL command:

INSERT INTO SENSORS (id, device_id, name, unit) VALUES ('{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}');

The results are strange, all I get is a load of empty rows :frowning:

image
image1920×1080 172 KB

What am I not doing correctly here?
Many thanks!

28

INSERT INTO SENSORS (id, device_id, name, unit) VALUES ('{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}');

Where are you using that?

29

Hi Colin,
It's inside the PostgreSQL node:

image
image1920×1080 176 KB

30

I don't see anything in the node's readme that says you can use that syntax, though I am on my phone and may have missed it.

31

Hi,

I havent worked with PostgreSQL either but after reading the docs

it expects the values to be passed in as parameters in array : msg.params = ['Andrea']

so i would suggest to try to change your postgrestor node configuration Query to :

INSERT INTO SENSORS (id, device_id, name, unit) VALUES ($1, $2, $3, $4);

according to the docs example and from your function pass :

msg.params = ['{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}']

fingers crossed :smiley:

32

Or perhaps @jomacdon meant to put the code using the moustache syntax in a Template node to expand the parameters and then feed that into the postgres node.

33

i know .. but it seems that this node-red-contrib-postgrestor-next node
doesnt accept a 'custom made' sql query
so my suggestion was to remove the template, get the values from the API and structure them in a msg.params array using a function and test like this :slight_smile:

34

I think it unlikely that you cannot give it a fully defined query with no variable parameters. In fact I imagine that is how @jomacdon initially tested it, though I don't know that.

35

Hello guys, I am just about to test the suggestion for @UnborN (Andy), I will update soon - Thanks!

36

Hello guys, I did the changes and can now see something in the PostgreSQL database BUT... it's not what is expected:

image
image1920×1080 237 KB

The new function node has created the Parameters as suggested by @UnborN but they have not collected the data from the array coming from the API call:

image
image1920×1080 271 KB

37

Function Node code:

msg.params = ['{{payload.id}}', '{{payload.deviceId}}', '{{payload.name}}', '{{payload.unit}}']
return msg;

PostgreSQL node:

INSERT INTO SENSORS (id, device_id, name, unit) VALUES ($1, $2, $3, $4);

Thank you.

38

Hi,

opps .. at least we are writing something in the table :slight_smile:
try to remove the single quotes and curly braces around each value .. like this

msg.params = [payload.id, payload.deviceId, payload.name, payload.unit]
return msg;

and can you show as seperate screenshots of debugs from API request and function node ?

39

Hi,
I tried that, thank you but now nothing is coming out of the Function Node :frowning:

40

can you show as seperate screenshots of debugs from API request and function node ?
(and extend the view of debug from API to see how the value stucture is)
set debugs to complete msg
and also if you can repost your flow with the latest changes ?