PostgresSql + Nodejs (ClaudiaJS)：如何将字符串数组转换为时间戳数组

Question

我正在编写API，它插入到一个多行的表中，我使用UNNEST来使它工作。

我所做的：

在js文件中：

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();
        });
}

请求体

[{
    "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"
}]

当我在postman中运行api时，得到的错误是：

列"timestamp_start“是带有时区的时间戳类型，而表达式是文本类型的。

问题显然来自我在sql中使用的字符串数组，我的问题是如何为UNNEST创建时间戳数组，或者任何建议都会得到赞赏。

谢谢

Answer 1

• 永远不要在处理程序中初始化数据库，请参阅：我应该在哪里初始化pg-承诺？
• 永远不要在HTTP处理程序中调用pgp-end()，它会破坏所有连接池。
• 使用静态ColumnSet类型生成多插入查询.
• 不要从db.any返回，在这个上下文中没有意义
• 必须在HTTP处理程序中提供HTTP响应
• 您为列class_id提供了一个令人困惑的语义。为什么它的名字是那样的，却被转换成一个timestamp
• 不要直接将对象与字符串连接起来。
• 永远不要手动连接SQL字符串，它会破坏格式设置，并将代码打开到SQL注入。
• 根据预期的结果使用数据库方法，即在您的情况下使用none，而不是any。请参阅：https://github.com/vitaly-t/pg-promise#methods

只初始化一次所需的所有内容：

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'}});

实现处理程序：

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
        });   
}

Answer 2

非常感谢“正义”，

它在铸造阵列之后工作。

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[])"