Problem with Postgres and timestamp values

General
1

Hi all,

I have the following scenario:
Database A has a table along with several timestamp columns
Database B has the exact same schema for that table

I want to read rows from A and insert them in B.

Sounds easy? :slight_smile:

Thats what I read from DB A:

{"ts":"2020-04-23T05:15:29.782Z","country":"testcountry","city":"testcity","line":"A03","packageid":"e9a0abbe-2a92-4947-a635-fe05709777e8","boxid":"12345","configname":"Gen2_BC","module":"LineController","event":"Induct"}

And that is what JS/Node-Red creates:

INSERT INTO boxes (ts,country,city,line,packageid,boxid,configname,module,event) VALUES ('Thu Apr 23 2020 05:15:29 GMT+0000 (Coordinated Universal Time)','testcountry','testcity','A03','e9a0abbe-2a92-4947-a635-fe05709777e8','12345','Gen2_BC','LineController','Induct');

Why the hell is the timestamp column modified? I just want to insert the exact same string "ts" as I get it from DB A. So, something parses the ts column and modifies it to a human readable format. And I find no way to avoid that....

Thats the code I use:

sqlCommands = [];
sqlCommands.push({ "query" : "BEGIN;" });

for (i=0; i< msg.payload.length; i++)
{
    let row = msg.payload[i];
    let names= Object.keys(row).join(',');
    let values= Object.values(row).join("','");

    sqlCommand = "INSERT INTO boxes (" + names + ") VALUES ('" + values + "');";
    sqlCommands.push({ "query" : sqlCommand });
}

sqlCommands.push({ "query" : "COMMIT;" });
msg.payload= sqlCommands;
msg.rows = msg.payload.length;

return msg;

Any ideas? And I don't want to manually create the statement as I don't know if someone adds mor columns etc.

Thanks!

Version:
Node-Red 1.0.1
node-red-contrib-postgres-multi 0.2.0

2

I suspect the query results are typed and the ts is a date object. (Put a debug set to show complete object on the output of the first query to verify)

You code converts everything to string so ts.toString occurs causing the time format you see.

I would suggest you recode this to use params and pass the actual objects not string versions

Documentation shows an example....

(Partial snippet from documents)

{
    query: 'insert into mytable (id, message) values (1, $hello), (2, $world)',
        params: {
            hello: 'Hi there',
            world: 'O\'Rorke',
        },
}

So build the params object from your first query results - using the original object as the value (instead of hard coded strings in the example)

Make sense?

3

Thanks, that makes sense, yes. I just try to figure out an elegant way to do it for all properties in my msg object and create the query. Too bad that there is now way to use the original values and avoid that toString, or at least provide a formatter "none".

4

Ok, it works - what is the numbering for? It works without, I just want to make sure I don't created a bug here:

sqlCommands = [];
sqlCommands.push({ "query" : "BEGIN;" });

for (i=0; i< msg.payload.length; i++)
{
    let row = msg.payload[i];
    let names= Object.keys(row).join(',');
    let values= Object.keys(row).join(",$");

    sqlCommand = "INSERT INTO boxes (" + names + ") VALUES ($" + values + ");";
    sqlCommands.push({ "query" : sqlCommand, params : msg.payload[i] });
}

sqlCommands.push({ "query" : "COMMIT;" });
msg.payload= sqlCommands;
msg.rows = msg.payload.length;

return msg;

Thanks,

Alex

5

No idea, never used that node (nor Postgres) - but its on my TODO list :slight_smile:

6

As a side note - I seem to recall (when we last used postgres node(s) - that it also returned what looked like an object but was not a "standard object" with the usual .toString (and other) prototypes attached... so you had to mangle it first to get at all the parts of the result.

7

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