How to insert into table name as alias using pg-pr

Question

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.

Answer 1

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:

• as.format - used by all query methods to format queries
• ColumnSet.table - gives you the specialized table object
• ColumnSet.names - gives you all the columns formatted
• helpers.values - gives you all the values formatted