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

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, 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 escaping me. If the record does not exist
>     how can you UPDATE it?
>
>
> here the table with reviews of users "uid" done by users "author":
>
> CREATE TABLE words_reviews (
>         uid integer NOT NULL CHECK (uid <> author) REFERENCES
> words_users ON DELETE CASCADE,
>         author integer NOT NULL REFERENCES words_users(uid) ON DELETE
> CASCADE,
>         nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
>         review varchar(255),
>         updated timestamptz NOT NULL,
>         PRIMARY KEY(uid, author)
> );
>
> Since I am merging user ids (from several to one), I need to change the
> records in the above table too.

Yeah, still trying to figure out why the smallest uid becomes the merged 
uid, but that is another train of thought:)

>
> I need to update the PK by changing either "uid" or "author".
>
> But this might give me conflicts, because there might be such a PK
> already...

Hence my suggestion for INSERT ON CONFLICT UPDATE. If the PK combination 
does not exist you INSERT a new record. If it does exist you leave the 
PK combination alone and UPDATE the rest of the information in the row 
to the current data.

>
> Regards
> Alex
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com