CODEWITHSUNDEEP
node.jspostgresqlpg-promise
woozyking
woozyking

Reputation: 5220

How to insert into table name as alias using pg-promise insert helper?

This is a follow-up question from this comment

The use case is for such query below:

INSERT INTO "GamingLogs" AS GL ("GameName", "TimeSpent")
VALUES ('LOL', '2'),
    ('DOTA2', '1'),
    ('Mobius Final Fantasy', '3')
ON CONFLICT ("GameName") DO UPDATE
SET "TimeSpent" = GL."TimeSpent" + EXCLUDED."TimeSpent"

Assume the data table contains primary string key on GameName, and an integer column TimeSpent. The purpose let's assume it logs my lifetime total hours of gaming time on given GameName.

UPDATE: simplified the query and added the structure of the data.

Upvotes: 2

Views: 769

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

You can use the flexible types in the helpers namespace to generate your own custom insert:

const pgp = require('pg-promise')(/*initialization options*/);

// data = either one object or an array of objects;
// cs = your ColumnSet object, with table name specified
// alias = the alias name string
function createInsertWithAlias(data, cs, alias) {
    return pgp.as.format('INSERT INTO $1 AS $2~ ($3^) VALUES $4^', [
        cs.table, alias, cs.names, pgp.helpers.values(data, cs)
    ]);
}

and then you simply append the conflict-resolution clause to it, since it is static.

API used in the example:

Upvotes: 1

Related Questions