Postgres UPSERT native support

[lefnire]

Looks like Postgres recently added UPSERT (9.5alpha is it?). Seems Sequelize handles psql upsert fine presently with its workaround, so obviously no rush.

Me personally (and maybe I need re-architecting), my current project's most common SQL operation is UPSERT. That operation seems quite slow; this is the resultant SQL which looks like it would be slow:

CREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert() RETURNS integer AS $func$ BEGIN INSERT INTO ... VALUES ...; RETURN 1; EXCEPTION WHEN unique_violation THEN UPDATE ... SET ... WHERE ...; RETURN 2; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert();

So I'd personally upgrade to Postgres 9.5 if native upsert becomes supported in sequelize

[mickhansen]

We still need a proper way to detect features based on the server version, but yeah ideally we'd want to use the built in UPSERT when possible.

[lefnire]

Possible sloppy short-term solution: pass version constructor options? Eg: new Sequelize('database', 'username', 'password', {..., version:'9.5'})

[janmeier]

@mickhansen All the query work regarding version detection is actually already in place - As far as I can see we just need to actually select the verison, and find a good datastructure for storing which features are supported by what versions

[janmeier]

One problem though is that pg 9.5 is not supported by travis - and probably won't be before its officially releasesd

[lorenz]

+1

[thynson]

Well, PostgreSQL 9.5 is released.

[ncwhale]

Sometimes, just need update one signal col when insert failed, right now I'll do it like this:

Models.Somemodel.create().then(_=>{do something here.}).catch(_=>{do something when fail});

but with pg 9.5 , how about add a suger option when insert fail? like this

Models.Somemodel.create({}, {conflict: { add another sub update/nothing query here}}).then(_=>{do something here.});

It most like the upsert on another way, but with other db also can also use it as is.(by add a catch and do the sub query.)

[tsheaff]

Any updates on this @mickhansen ? Our team is currently looking at how to optimize our upserts, as the native PostgreSQL 9.5 upsert is much faster. We'd strongly prefer not to re-implement upsert ourselves as a native query.

[mickhansen]

@tsheaff No one is working on it, but version detection is in place so it should not be a lot of work (alternative syntax based on version detected). We have support for testing against 9.5 aswell.

[tsheaff]

Awesome thanks for quick reply @mickhansen Any rough estimate on timeline?

[felixfbecker]

As he said, noone is working on it. PRs welcome :)

[tsheaff]

PR is already out @felixfbecker here: #6325

Waiting on code review and merge

[tsheaff]

Seems like #6325 hasn't been worked on in over a month. Anyone know more around this issue?

[AlJohri]

There is also #7174.

[haijianyang]

So, this issure when to be resolved?

[tsheaff]

This is a really stale issue. Is there any update on this?