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.

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. — vitaly-t, Jun 13 '17 at 15:10
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. — vitaly-t, Jun 13 '17 at 15:13
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. — woozyking, Jun 13 '17 at 20:42

vitaly-t · Accepted Answer · 2017-06-14T15:32:15.370

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

edited Jun 14 '17 at 15:32

answered Jun 13 '17 at 15:24

vitaly-t

24,279
15
116
138

1

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. – woozyking Jun 13 '17 at 20:32
@woozyking 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. – vitaly-t Jun 14 '17 at 04:12

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

1 Answers1

Linked