Search Postgresql Archives

Re: UPDATE ... ON CONFLICT DO NOTHING

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 03/14/2017 01:25 PM, Alexander Farber wrote:
Hi Adrian -

On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux