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

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.

Halliard

While I understand your question from the reference, it would be good if you could elaborate and provide all the details right here, so both the question and answers can be useful to others, without having to go to another question. Plus it will help the search index to locate by keywords. – Coopery

For one thing, it is important to show what your data model is, to be able to show how data can be mapped into query values. I would presume that you do not use Date, Hour as properties, something else, but I would be just guessing. – Coopery

The data structure may not matter, but I updated the query in the question and added the explanation of the data structure, which hopefully makes the question focuses more on the title. – Halliard

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

Coopery

Without alias, the query would ERROR: column reference "TimeSpent" is ambiguous. The second approach is better, minor difference is that I ended up just using pgp.helpers.values in template string. – Halliard

@Halliard I have updated the answer. And yes, you can solve it in many different ways, the helpers API is ultimately flexible for that. The example I gave is just one such approach. – Coopery