Inserting multiple records with pg-promise

Question

I have a scenario in which I need to insert multiple records. I have a table structure like id (it's fk from other table), key(char), value(char). The input which needs to be saved would be array of above data. example: I have some array objects like:

lst = [];

obj = {};
obj.id= 123;
obj.key = 'somekey';
obj.value = '1234';
lst.push(obj);

obj = {};
obj.id= 123;
obj.key = 'somekey1';
obj.value = '12345';
lst.push(obj);

In MS SQL, I would have created TVP and passed it. I don't know how to achieve in postgres. So now what I want to do is save all the items from the list in single query in postgres sql, using pg-promise library. I'm not able to find any documentation / understand from documentation. Any help appreciated. Thanks.

Answer 1

I am the author of pg-promise.

There are two ways to insert multiple records. The first, and most typical way is via a transaction, to make sure all records are inserted correctly, or none of them.

With pg-promise it is done in the following way:

db.tx(t => {
    const queries = lst.map(l => {
        return t.none('INSERT INTO table(id, key, value) VALUES(${id}, ${key}, ${value})', l);
    });
    return t.batch(queries);
})
    .then(data => {
        // SUCCESS
        // data = array of null-s
    })
    .catch(error => {
        // ERROR
    });

You initiate a transaction with method tx, then create all INSERT query promises, and then resolve them all as a batch.

The second approach is by concatenating all insert values into a single INSERT query, which I explain in detail in Performance Boost. See also: Multi-row insert with pg-promise.

For more examples see Tasks and Transactions.

Addition

It is worth pointing out that in most cases we do not insert a record id, rather have it generated automatically. Sometimes we want to get the new id-s back, and in other cases we don't care.

The examples above resolve with an array of null-s, because batch resolves with an array of individual results, and method none resolves with null, according to its API.

Let's assume that we want to generate the new id-s, and that we want to get them all back. To accomplish this we would change the code to the following:

db.tx(t => {
    const queries = lst.map(l => {
        return t.one('INSERT INTO table(key, value) VALUES(${key}, ${value}) RETURNING id',
                       l, a => +a.id);
    });
    return t.batch(queries);
})
    .then(data => {
        // SUCCESS
        // data = array of new id-s;
    })
    .catch(error => {
        // ERROR
    });

i.e. the changes are:

• we do not insert the id values
• we replace method none with one, to get one row/object from each insert
• we append RETURNING id to the query to get the value
• we add a => +a.id to do the automatic row transformation. See also pg-promise returns integers as strings to understand what that + is for.

UPDATE-1

For a high-performance approach via a single INSERT query see Multi-row insert with pg-promise.

UPDATE-2

A must-read article: Data Imports.