PostgresSql + Nodejs (ClaudiaJS) : How to cast array of string to array of timestamp

Question

I am writing API which insert into a table with multiple rows, I am using UNNEST to make it work.

What I have done:

in js file:

api.post(PREFIX + '/class/insert', function (request) {

    var db = pgp(dbconnect);

    //Params
    var data = request.body; //should be an array

    var classes = [];
    var starts = [];
    var ends = [];

    for (var i = 0; i < data.length; i++) {
        classes.push(data[i].class_id);
        starts.push(data[i].timestamp_start);
        ends.push(data[i].timestamp_end);
    }

    const PQ = require('pg-promise').ParameterizedQuery;

    var sql =
    "INSERT INTO sa1.class(class_id, timestamp_start, timestamp_end) " +
    "VALUES(  "+
        "UNNEST(ARRAY" + JSON.stringify(classes).replace(/"/g, "'") + "), " +
        "UNNEST(ARRAY" + JSON.stringify(starts).replace(/"/g, "'") + "), " +
        "UNNEST(ARRAY" + JSON.stringify(ends).replace(/"/g, "'") + ")" 

    const final_sql = new PQ(sql);

    return db.any(final_sql)
        .then(function (data) {
            pgp.end();

            return 'successful';
        })
        .catch(function (error) {
            console.log("Error: " + error);
            pgp.end();
        });
}

Request body

[{
    "class_id":"1",
    "timestamp_start":"2017-11-14 14:01:23.634437+00",
    "timestamp_end":"2017-11-14 15:20:23.634437+00"
}, {
    "class_id":"2",
    "timestamp_start":"2017-11-14 15:01:23.634437+00",
    "timestamp_end": "2017-11-14 16:20:23.634437+00"
}]

When I run api in postman, I get the error is:

column "timestamp_start" is of type timestamp with time zone but expression is of type text

Issue is obviously from ARRAY of string that I used in sql, my question is how to create ARRAY of timestamp for UNNEST, or any suggestion are appreciated.

Thanks

values in array are text, cast array to timestamp array and it will be ok — JustMe, Nov 14 '17 at 14:51

vitaly-t · Answer 1 · 2017-11-15T11:31:32.447

0

Never initialize the database inside the handler, see: Where should I initialize pg-promise
Never call pgp-end() inside HTTP handlers, it destroys all connection pools.
Use static ColumnSet type to generate multi-insert queries.
Do not return from db.any, there is no point in that context
You must provide an HTTP response within an HTTP handler
You are providing a confusing semantics for column class_id. Why is it called like that and yet being converted into a timestamp?
Never concatenate objects with strings directly.
Never concatenate SQL strings manually, it will break formatting and open your code to SQL injection.
Use Database methods according to the expected result, i.e. none in your case, and not any. See: https://github.com/vitaly-t/pg-promise#methods

Initialize everything needed only once:

const db = pgp(/*connection*/);

const cs = new pgp.helpers.ColumnSet([
    'class_id',
    {
        name: 'timestamp_start',
        cast: 'timestamp'
    },
    {
        name: 'timestamp_end',
        cast: 'timestamp'
    }
], {table: {table: 'class', schema: 'sa1'}});

Implement the handler:

api.post(PREFIX + '/class/insert', request => {

    const sql = pgp.helpers.insert(request.body, cs);

    db.none(sql)
        .then(data => {
            // provide an HTTP response here
        })
        .catch(error => {
            console.log('Error:', error);
            // provide an HTTP response here
        });   
}

edited Nov 15 '17 at 11:31

answered Nov 15 '17 at 11:25

vitaly-t

24,279
15
116
138

thanks @vitaly-t about your suggestion, I learnt a new thing. I will try it and get back soon – Phong Vu Nov 15 '17 at 12:05
The reason I initialised db in handler is: the api is executed by AWS Lambda which has limit time (more less 5 mins), and I sometimes got errors when Lambda expired before. I didn't know how to solve that error properly, so every time api is called, will make a new db connection. If anyone has better approach, please free to share. – Phong Vu Nov 15 '17 at 12:10
@PeterPham That doesn't make a new connection, just needlessly creates a new connection pool. The pool is capable of creating a new connection when needed. Are you using the latest version of pg-promise? – vitaly-t Nov 15 '17 at 12:17
I am using latest version of pg-promise. Could you please to give me some more code about how to use pool in this case? – Phong Vu Nov 15 '17 at 14:00
@PeterPham In a standard way: https://stackoverflow.com/questions/34382796/where-should-i-initialize-pg-promise – vitaly-t Nov 15 '17 at 14:02

score -1 · Answer 2 · answered Nov 14 '17 at 15:16

Many thanks to @JustMe,

It worked after casting array

var sql =
    "INSERT INTO sa1.class(class_id, timestamp_start, timestamp_end) " +
    "VALUES(  "+
        "UNNEST(ARRAY" + JSON.stringify(classes).replace(/"/g, "'") + "), " +
        "UNNEST(ARRAY" + JSON.stringify(starts).replace(/"/g, "'") + "::timestamp[]), " +
        "UNNEST(ARRAY" + JSON.stringify(ends).replace(/"/g, "'") + "::timestamp[])"

PostgresSql + Nodejs (ClaudiaJS) : How to cast array of string to array of timestamp

2 Answers2