Insert multiple rows from an array of data to postgreSQL

Help
1

Hi,
I have an issue here. Need to ingest the array of data captured from SQL server into postgreSQL for my project. which is the easiest to ingest array of data into postgreSQL. Please find the below image how my payload looks like.

image
image1117×1082 59.6 KB

This is the workflow I developed to ingest one row of data into PostgreSQL. And it is working as expected.

image
image1203×984 64 KB

Thanks in advance!!!

2

Hi @Prasanna_Balusamy, and welcome to the Losant Forums!

So you have this working for one row, and your question is how can you insert multiple rows?

There are two ways you can approach this …

  1. Use a Loop Node to iterate over the items in your array. Inside of the loop, you can add the nodes you have for inserting a single row and do one row at a time (per iteration). You would need to do some adjustments to the payload paths to account for being inside of a loop.
  2. Write multiple SQL statements in the one PostGreSQL node, probably by using the {{#each}} Handlebars helper. Something along the lines of the following …
{{#each SQL_Result}}
insert into Db_Name(duration, eventtime, reasonno, scheduled, shift) values ({{duration}}, {{eventtime}}, {{reasonno}}, {{scheduled}}, {{shift}});
{{/each}}

You could even iterate over the object within each array item to get the keys and values - though that could be dangerous if the incoming data would not match the schema of the table you are writing to …

{{#each SQL_Result}}
insert into Db_Name({{#each this}}{{@key}}{{#unless @last}},{{/unless}}{{/each}}) values (({{#each this}}{{this}}{{#unless @last}},{{/unless}}{{/each}}));
{{/each}}

You will find the workflow’s template tester to be very helpful here. Given the payload you provided in your first screenshot, you can write a [string template] in the tester to construct your SQL statement and verify it is correct before even attempting to use it in the second SQL Node.

Let us know if you have any other questions, and again, welcome to Losant!

3

Hi @Dylan_Schuster
Thanks for your support. I am interested in loop node rather go with query method. can you share with me If any sample workflow to achieve this requirement would be helpful

4

Hi @Dylan_Schuster
Workflow is working as expected. With your support I could make this possible in loop node.
image
image

I am capturing few datetime stamp from SQL server. In the same time format, I need to ingest the data into postgreSQL. What I have issue here is when getting timestamp to Losant workflow it is automatically taking the timezone of my geographical location. I was trying with date/time node to match with original time format, but it is not working. Attached here the sample Image FYR. The actual time should 2023-05-23 02:23:40 but instead I get Tue May 23, 2023 14:23:40:000
can I get any comments?
image

5

This indicates that the value on the payload is parsed into a JavaScript Date Object, which is being localized and represented in your time zone by your web browser; for example, the same payload for me appears in the debug log as Tue May 23, 2023 04:53:40.000 GMT-04:00.

You can take that value and, using the {{formatDate}} helper, convert that into just about any syntax you would like when inserting it into your other database.

For example, if you wanted to insert it as an Epoch timestamp in milliseconds, you could convert it to that format as …

{{formatDate SQL_Result.[0].eventtime 'x'}}

// output: 1684832020000
6

Thanks for your support @Dylan_Schuster. That works for me. I have completed my entire workflow.