# Msg.payload data writing to Postgres

**URL:** <https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142>\
**Category:** General\
**Created:** [23 February 2020 12:57 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142 "2020-02-23T12:57:26Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [23 February 2020 12:57 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/1 "2020-02-23T12:57:27Z")

</div>

Hi,  
I have Node-Red and PostgreSQL running on a RasPi4.  
From Node-Red I can Create and Drop tables and I can write data into a db table using a standard INSERT statement, so I'm happy the basics are working.

I am picking up some data from an API which is in JSON format.  
I just can't figure out the syntax to write the data to write it into the database.  
Seemingly I have tried combinations of 1, 2 and even 3 sets of {}  
I have tried with ( and ) and also [and] also with single 'data' and "data"

Much Googling and YouTubing.

Also the timestamp with timezone seems particularly difficult to work with.

I can't see conclusively which Node's to select to make the whole thing flow so I'm not sure posting my actual anything is valuable. I have tried so many combinations.

Any basic pointers appreciated.  
Thanks  
Richard

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [23 February 2020 13:38 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/2 "2020-02-23T13:38:00Z")

</div>

Without more details there is nothing we can do to help. Show us the incoming data (feed it into a debug node) and tell us the query you are trying to generate from that data.

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [24 February 2020 09:40 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/3 "2020-02-24T09:40:07Z")

</div>

That's fair.  
Here is the message received straight out of the Debug node.  
2/24/2020, 8:28:14 PM[node: cde20083.11da3]  
msg.payload : Object  
object  
name: "TWTR"  
history: object  
2020-02-18: object  
open: "36.78"  
close: "38.06"  
high: "38.07"  
low: "36.68"  
volume: "16821347"

My aim is to select the Table called SNAP and write the fields as received into the columns.  
I have been trying the MYDB node with zero success so far.

If I use the MYDB node on it's own with an insert statement and manually put the data in the insert statement it works. This tells me I can access and write to the table.

I have tried real basics.....  
Take the Inject Timestamp node and try to write the msg.payload into a table with the first column as timestamp and just can't get it right.

What node would you suggest?  
Does anyone know of a Tutorial, HowTo or just a link to someone who has written msg.payload to a table in PostgreSQL?

Thanks  
Richard

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 February 2020 10:04 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/4 "2020-02-24T10:04:42Z")

</div>

> [@little\_rich](#):
>
> I have been trying the MYDB node

Which node is that? Possibly node-red-contrib-something.  
Most database types allow you to specify a column which will automatically add the current time into a row when you insert it.  
You didn't answer the question, what should the insert query for the above data look like?

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [24 February 2020 10:19 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/5 "2020-02-24T10:19:56Z")

</div>

Thank's for your patience Colin.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/9/99655928b684f64f5dcd73bf4f1378971774658b.png)

Not sure whether that will show up but it is one of the PostgreSQL nodes and has the little elephant (it is blue)  
As opposed to the node-red-contrib-postgres  
I was persevering with the MYDB node only because I could prove the ability to write data into the table.

I specifically want to keep the Date/Time info that returns from the API and not the current Date/Time  
The table has the other columns set up in order so....  
Table name is TWTR  
date  
open  
close  
high  
low  
volume  
ready to receive the returned info as per the Debug payload data

There is a node called postgrestor which conflicts with node-red-contrib-postgres and I just don't know which one is better to install and try as you can only have one or the other.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [24 February 2020 10:44 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/6 "2020-02-24T10:44:26Z")

</div>

I believe you are using `node-red-contrib-mydb`

Why not build the query in a `template` node? In the 'Info' tab for the node it looks like you would put the request in msg.completeRawQuery

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [24 February 2020 10:51 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/7 "2020-02-24T10:51:24Z")

</div>

Thanks, zenofmud, I will have a look at that option.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 February 2020 11:09 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/8 "2020-02-24T11:09:48Z")

</div>

> [@little\_rich](#):
>
> specifically want to keep the Date/Time info that returns from the API

You have not shown a timestamp in the debug output you posted. Can you fully expand the payload and post a screenshot of the debug output so we can see exactly what it there.

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [24 February 2020 11:25 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/9 "2020-02-24T11:25:07Z")

</div>

Here is the message received straight out of the Debug node.  
2/24/2020, 8:28:14 PM[node: cde20083.11da3]  
msg.payload : Object  
object  
name: "TWTR"  
history: object  
2020-02-18: object  
open: "36.78"  
close: "38.06"  
high: "38.07"  
low: "36.68"  
volume: "16821347"

This particular API return has date only and not timestamp - my apologies  
I have a table called TWTR  
The columns are as follows:  
date  
open  
close  
high  
low  
volume

My aim is to write the data received from the api and put the relevant data in the relevant column within the table.  
So  
date would be 2020-02-19  
open would be 36.78  
etc

The insert statement looks like this...  
INSERT INTO public."SNAP"(  
date, open, close, high, low, volume)  
VALUES (?, ?, ?, ?, ?, ?);

What I don't know is how to set the syntax for all the ? to unpack the JSON data

Thanks for looking at my query. I wish I knew where to look but just can't find anything close.  
Cheers  
Richard

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 February 2020 11:47 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/10 "2020-02-24T11:47:47Z")

</div>

> [@little\_rich](#):
>
> 2020-02-18: object  
> open: "36.78"  
> close: "38.06"  
> high: "38.07"  
> low: "36.68"  
> volume: "16821347"

The reason I asked for a screenshot was to make this bit more clear. Is that an attribute called "2020-02-18" containing the open, close values etc? If so that is not a nice way to pack the data as you will have to interrogate the keys of the object. Is that contained within the history attribute? Can there be more that one date object in the history object?  
Also what type is the date column in the db?

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [24 February 2020 12:07 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/11 "2020-02-24T12:07:36Z")

</div>

In the db I have created the date column as the primary key and it is of data type date.  
I can easily change this if I have made a mistake.

Your other questions are tough for me.  
When I query the API, I can nominate a date as I did above or I could put in a range of dates in which case the returned data repeats with the date object followed by the open, close etc for that date. But I need to start simply and get one going and this appears to be the smallest/simplest (says me choking on it)

I'm kind of hoping once I see how to write one of these returned API datasets into PostgreSQL that I can figure the rest out. There are many queries I can construct and this is possibly the simplest.

But, I can't change what I get back or how it gets presented.

I have found a node called data mapper and am trying to find an example on YouTube of it's use.  
Thanks for your continued support.  
Richard

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [24 February 2020 12:13 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/12 "2020-02-24T12:13:39Z")

</div>

I just did the query in Postman and got this and wonder whether it answers your previous question...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/4/4463b54b743cd843760964c7ad6aafa6e4bf3398.png)

Thanks  
Richard

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [24 February 2020 12:46 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/13 "2020-02-24T12:46:25Z")

</div>

> [@little\_rich](#):
>
> In the db I have created the date column as the primary key and it is of data type date.

That means that you will only be allowed to have one row with that date. Better is to defind a field like `id` and have is an auto-incrimented number as the primary key.

In your debug you can see that '2020-02-18' is the 'key' of a key/value pair so to grab it you will need something like this in a function node  
`var k = Object.keys(msg.payload.history)[0]; // returns first `

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [24 February 2020 13:44 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/14 "2020-02-24T13:44:05Z")

</div>

And to follow on from that, to get for example the open value you can use  
`msg.payload.history[k].open`

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [25 February 2020 07:49 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/15 "2020-02-25T07:49:58Z")

</div>

@zenofmud thank you for the starter above. I look forward to having a go at this tonight.

For this table there will only be one entry per day so PKey of the date field is fine. There will be another table where the timestamp will come from the API and it can be the Pkey. It was certainly a consideration though.

It's exciting to be putting data into the database (hopefully) and I appreciate the assistance.  
Cheers  
Richard

---

<div class="post-metadata">

**Author:** ![little\_rich](https://avatars.discourse-cdn.com/v4/letter/l/839c29/32.png) [@little\_rich](https://discourse.nodered.org/u/little_rich)\
**Post date:** [25 February 2020 07:51 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/16 "2020-02-25T07:51:01Z")

</div>

@Colin thanks for this update as well. Very much appreciate your support and patience.  
I'm hoping to make a success tonight.  
Cheers  
Richard

---

<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:** [25 April 2020 07:56 UTC](https://discourse.nodered.org/t/msg-payload-data-writing-to-postgres/22142/17 "2020-04-25T07:56:30Z")

</div>

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