Search code examples
node.jspostgresqlpg-promise

How to return insert query result values using pg-promise helpers

I am using pgp.helpers.insert to save data into PostgreSQL which is working well. However, I need to return values to present in a response. I am using:

this.collection.one(this.collection.$config.pgp.helpers.insert(values, null, 'branch'))

which returns no data.

What I want to be able to do is return the branch id after a successful insert, such as:

INSERT into branch (columns) VALUES (values) RETURNING pk_branchID
Solution

  • Simply append the RETURNING... clause to the generated query:

    const h = this.collection.$config.pgp.helpers;
const query = h.insert(values, null, 'branch') + 'RETURNING pk_branchID';
      
await this.collection.one(query);

    You must have a large object there, if you want to automatically generate the insert. Namespace helpers is mostly valued when generating multi-row inserts/updates, in which case a ColumnSet is used as a static variable:

    const h = this.collection.$config.pgp.helpers;
const cs = new h.ColumnSet(['col_a', 'col_b'], {table: 'branch'});
const data = [{col_a: 1, col_b: 2}, ...];

const query = h.insert(data, cs) + 'RETURNING pk_branchID';
      
await this.collection.many(query);

    Note that in this case, we do .many, as 1 or more rows/results are expected back. This can even be transformed into just an array of id-s:

    await this.collection.map(query, [], a => a.pk_branchID);

    see: Database.map