Pg-promise: Is there a helper for an UPSERT operation?

[seanlindo]

@vitaly-t,

Is there, or do you have any plans, to include a helper that maps to the newish UPSERT functionality? Using the helpers is extremely useful for performance and code readability, and now I have a use case where using this keyword is advantageous.

Thanks!

[vitaly-t]

There is more than one way to implement UPSERT logic within PostgreSQL today. Which one do you refer to as the newish?

[seanlindo]

To be more specific, I'm looking for a performant way to perform a bulk INSERT INTO...ON CONFLICT DO UPDATE operation.

[vitaly-t]

The existing helpers.insert already allows you to do that. You generate the insert, and then simply append the ON CONFLICT clause.

[vitaly-t]

May I assume this has been answered? Closing, since there was no reply.

[seanlindo]

Yes, it can be closed. Solution was super easy and performance is outstanding.

let query = this.pgp.helpers.insert(collection, col_set) + ' ON CONFLICT ON CONSTRAINT constraint_name_goes_here DO UPDATE SET modified_date = now()'

[davegri]

It would be nice if there was a way to combine the insert and update helpers. I would like to do:

const upsertContact = (contact) => {
  const insert = pgp.helpers.insert(contact, null, 'contacts');
  const update = pgp.helpers.update(contact);
  db.none(`${insert} ON CONFLICT (sf_id) DO ${update}`);
};

Even better would be

const upsertContact = (contact) => {
  const upsert = pgp.helpers.upsert(contact, null, 'contacts', 'sf_id');
  db.none(upsert);
};

the first generates

INSERT INTO "contacts"("email","password","sfId","orgId") values('emailvalue','passwordvalue','sfIdValue',1) ON CONFLICT DO update "contacts" set "email"='emailvalue',"password"='passwordvalue',"sfId"='sfIdValue',"orgId"=1

which doesn't work because it needs to be

INSERT INTO "contacts"("email","password","sfId","orgId") values('emailvalue','passwordvalue','sfIdValue',1) ON CONFLICT DO update set "email"='emailvalue',"password"='passwordvalue',"sfId"='sfIdValue',"orgId"=1

[vitaly-t]

@davegri The supported syntax for upsert operations in PostgreSQL is too verbose to be able to generate them automatically. They are extensible too much, and I do not see value in generating an SQL that represents only a small sub-syntax of what is possible, while supporting the full syntax is unrealistic.

Here're just couple scenarious:

• http://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions
• http://stackoverflow.com/questions/36083669/get-id-from-a-conditional-insert

The upsert logic can be infinitly complex, that's why this library does not generate any upsert stuff.

---

See also: SELECT=>INSERT example.

[davegri]

Could we have an option for generating the update clause without the table name? then I would be able to combine them

[vitaly-t]

Could we have an option for generating the update clause without the table name?

Of course, this is why we have all the low-level methods available: helpers.sets, helpers.values, ColumnSet.names, ColumnSet.values, etc.

It is all there so you can combine them into a custom and more complex insert or update scenario.

See helpers.

[AlJohri]

I think it would be great if the helper could handle the most basic case of replacing all (or some defined subset) of the columns without any constraints. Either that or perhaps some additional examples of using the low level methods to achieve this in the documentation.

For example:

I've defined a ColumnSet like thus:

const cs = new pgp.helpers.ColumnSet([
  'article_id',
  'topic_id',
  'potomac_pos_score',
  'potomac_neg_score',
  'potomac_decision_score',
  'potomac_class',
  'patuxent_score',
  'ensemble_score',
  'ensemble_class',
], {table: 'article_topics'});

And I'm trying to basically do a full upsert of the document:

const query = pgp.helpers.insert(articleTopics, cs) + " ON CONFLICT (article_id, topic_id) DO UPDATE " +
    "SET potomac_pos_score = EXCLUDED.potomac_pos_score, " +
        "potomac_neg_score = EXCLUDED.potomac_neg_score, " +
        "potomac_class = EXCLUDED.potomac_class, " +
        "potomac_decision_score = EXCLUDED.potomac_decision_score, " +
        "patuxent_score = EXCLUDED.patuxent_score, " +
        "ensemble_score = EXCLUDED.ensemble_score, " +
        "ensemble_class = EXCLUDED.ensemble_class";

Seems like a pretty common use case?

[vitaly-t]

@AlJohri this is one of those examples of going about it in the wrong way.

The rule of the thumb - over-complicating queries construction doesn't pay. All complex queries should instead reside in their own SQL files, see Query Files and pg-promise-demo.

And if you need to run a multi-insert or multi-update, you inject the result of the corresponding helpers.insert and helpers.update methods into the SQL via a raw-text variable.

[AlJohri]

@vitaly-t thanks for the feedback, I'll definitely check that out.

Coming from an ORM world, it just sort of seemed like doing a CREATE OR REPLACE operation (I'm replacing the entire row here) wouldn't require a custom query.

[AlJohri]

For anyone else that comes to this issue from Google, I found this case didn't warrant custom SQL since I was doing the same thing for a few different tables. I settled on this function instead:

function upsertReplaceQuery(data, cs) {
  return pgp.helpers.insert(data, cs) + 
    " ON CONFLICT (id) DO UPDATE SET " + 
    cs.columns.map(x => `${x.name} = EXCLUDED.${x.name}`).join(', ');   
}

I couldn't find a method or attribute on ColumnSet that returned an array of column names without traversing the .columns attribute, so if there's a better way of doing the above, please let me know.

[vitaly-t]

@AlJohri there are only so many basic methods we can think of, while the rest should be easy to compile from those. The full syntax for inserts and updates can be too verbose sometimes to try covering everything.

In your example you've got close enough to doing what you need. Couple of notes though:

• Since you access the column names directly, you need to escape those correctly
• You can use join() instead of join(', '), as the result is the same

In all, change you code to this, and it'll be fine:

function upsertReplaceQuery(data, cs) {
    return pgp.helpers.insert(data, cs) +
        ' ON CONFLICT(id) DO UPDATE SET ' +
        cs.columns.map(x => {
            var col = pgp.as.name(x.name);
            return col + ' = EXCLUDED.' + col;
        }).join();
}

UPDATE

A more recent version of the library started supporting method [ColumnSet.assignColumns], so the last part can be simplified:

function upsertReplaceQuery(data, cs) {
    return pgp.helpers.insert(data, cs) +
        ' ON CONFLICT(id) DO UPDATE SET ' +
                cs.assignColumns({from: 'EXCLUDED', skip: 'id'});
}

Note: I've added the skip part because you typically would want to skip updating the key column(s) on which the conflict occurred.

[kissmygritts]

@vitaly-t, I love this library, and the amount of work you put into it. Every problem I've had you already have a solution some where. I just need to be pointed in the right direction occasionally.

[MahimaSrikanta]

Can we do similar kind of UPSERT for the PostgreSQL with version 9.4. where we do not have 'ON CONFLICT'

[vitaly-t]

@MahimaSrikanta The ON CONFLICT part is simply appended to the insert that's generated. If it is not supported, you simply do not append it, and use one of the older solutions that worked in PostgreSQL v9.4. You can find those on StackOverflow.