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
