Issue: Postgresql ON CONFLICT
Author: paulfariello
State: closed
Created: 2016-05-20 07:23:12

Hi,

we can read in documentation that `INSERT … ON CONFLICT` is planed to be supported soon.
I'm currently using some home made query to do it, but I think it would be more stable to add support for it in peewee. I would be glad to give a try but I have some question before creating a merge request.

Looking at `generate_insert` (https://github.com/coleifer/peewee/blob/master/peewee.py#L1912) I'm wondering if the `ON CONFLICT` should be added as an `upsert` or as a `on_conflict`. 

In any case the general construction of the query is rather different for sqlite `ON CONFLICT` is at the beginning of the query while for postgresql it is after the values. Is it ok to add some specific variable on Database class such as `on_conflict_position` and then do some `if`in `generate_insert`.

Thanks


Well... so for one, I am working in the background on a rewrite of all the sql generation, cleaning it up quite a bit in the process. So I don't know that it'd be worth the effort. 


Ok so I'll wait for the complete rewrite.

Thanks

Le 20 mai 2016 16:32:12 GMT+02:00, Charles Leifer notifications@github.com a écrit :

> Well... so for one, I am working in the background on a rewrite of all
> the sql generation, cleaning it up quite a bit in the process. So I
> don't know that it'd be worth the effort. 
> 
> ---
> 
> You are receiving this because you authored the thread.
> Reply to this email directly or view it on GitHub:
> https://github.com/coleifer/peewee/issues/942#issuecomment-220622010

## 

Paul Fariello
Envoyé de mon téléphone. Veuillez excuser ma brièveté.


Reopening to add implementation to 2.8.x.


Is 2.8.x containing the sql generation rewrite ? Do you need some workforce for this issue ? 


The rewrite will be the 3.0 release, so as not to rock the boat for existing Peewee users.

I'd like to get upsert in 2.8 as it will allow people to use the feature without worrying about a significant upgrade.


One thing I've run into implementing this is the requirement that you must manually specify the constraint you expect to be violated.

I'm wondering how much of the `INSERT ... ON CONFLICT` API should be exposed to end-users? The full spec allows for a WHERE clause in the conflict target **and** conflict action. What a shit-show!

There was a patch submitted to PokemonGo-Map (https://github.com/AHAAAAAAA/PokemonGo-Map/commit/ae13302c75f6005aa4c33e13aac9b98c71dc0bd7) that added very basic Postgresql upsert support to Peewee. It only covers violations of the primary key constraint, which seems sensible for an application, but in practice I am sure it needs to be more flexible for general usage.

No constraint needs to be specified if the resulting action is "DO NOTHING", but in many cases I would guess that people would instead want the "DO UPDATE SET" logic to update the existing row.

My current thinking is that, while I had hoped the approach from the PokemonGo-Map project would work, I think I need to build something that more closely matches Postgresql's APIs.


Any updates on that?

I'm currently using a insert_many statement and want to efficiently bypass record already exists by updating it with the latest attributes.


No updates :( but still very much on my radar.


That's totally ok :). You've done plenty of good work as it is and we should all be very thankful to you.
I was able to circumvent the challenge with a custom SQL statement.

Thanks again!


why not just accept this PR and be done with it for time being

https://github.com/coleifer/peewee/pull/1153

https://www.postgresql.org/docs/9.5/static/sql-insert.html -- the need to be able to support more than just the trivial case seems to outweigh merging in a fix that addresses a single case. Also note that there are no test-cases for that PR. Nor any documentation. It's just not the kind of patch I like to merge.

Please see new discussion thread in #1307 (which consolidates discussion of upsert functionality for sqlite/mysql/postgres).