From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: UPDATE ... ON CONFLICT DO NOTHING |
Date: | 2017-03-14 22:40:27 |
Message-ID: | 82c4f103-8f42-6ded-6be0-4085652f11ac@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 03/14/2017 01:25 PM, Alexander Farber wrote:
> Hi Adrian -
>
> On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2017-03-14 22:42:06 | Re: Postgres backup solution |
Previous Message | Adrian Klaver | 2017-03-14 22:30:02 | Re: Postgres backup solution |