Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-16 Thread Alexander Farber
Yasin, thank you for this suggestion, but - On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: ​​ >> Have you tried with inner block and do nothing on exception; >> > > >> BEGIN >> >> >> .. >> BEGIN >> >> UPDATE words_social >>

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-15 Thread David G. Johnston
The convention on these lists is to inline or bottom-post. On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: > ​​ > Have you tried with inner block and do nothing on exception; > > ​I suppose that would work - though using an exception path for expected logic is

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-15 Thread Yasin Sari
Have you tried with inner block and do nothing on exception; BEGIN .. BEGIN UPDATE words_social SET uid = out_uid WHERE uid = ANY(_uids); EXCEPTION WHEN OTHERS THEN --do nothing or write NULL means do nothing

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Peter Geoghegan
On Tue, Mar 14, 2017 at 4:55 AM, Alexander Farber wrote: > I wish there would be possibility to add ON CONFLICT DO NOTHING /* and drop > the review */ to the both UPDATE's above, but there is no such thing > described at

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread David G. Johnston
On Tue, Mar 14, 2017 at 1:25 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > But this might give me conflicts, because there might be such a PK > already... > > You need to remove the "might" and figure out which ones will and which will not. You can update the ones will not and

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Adrian Klaver
On 03/14/2017 01:25 PM, Alexander Farber wrote: Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver > wrote: On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all,

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver wrote: > On 03/14/2017 09:00 AM, Alexander Farber wrote: > >> My initial idea has been not to use ON CONFLICT at all, but instead only >> UPDATE the words_reviews records (set "uid" or "author" to out_uid) for

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Adrian Klaver
On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then I am afraid the logic is

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Adrian Klaver
On 03/14/2017 08:38 AM, Alexander Farber wrote: Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver > wrote: On 03/14/2017 07:23 AM, Alexander Farber wrote: in _uids array I have all user ids of player.

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver wrote: > On 03/14/2017 07:23 AM, Alexander Farber wrote: > >> >> in _uids array I have all user ids of player. >> >> I want to merge his or her data, including reviews, to a single user id: >> out_uid. >> >>

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Adrian Klaver
On 03/14/2017 07:23 AM, Alexander Farber wrote: Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has Alright I see that you are

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has been rated or this user has rated someone) and then re-INSERT ON CONFLICT DO NOTHING

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Adrian Klaver
On 03/14/2017 06:52 AM, Alexander Farber wrote: I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid,

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
Sorry, missed the last DELETE: DELETE FROM words_reviews WHERE author <> out_uid AND author = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid, author, nice,

[GENERAL] UPDATE ... ON CONFLICT DO NOTHING

2017-03-14 Thread Alexander Farber
Good afternoon, I am working on a small game, where the mobile app initially sends social network user data (name, city, gender) to PostgreSQL 9.5 backend and I store that data in a table: CREATE TABLE words_social ( sid varchar(255) NOT NULL, social integer NOT NULL CHECK (0 <=