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

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,
>                         author,
>                         nice,
>                         review,
>                         updated
>                 ) SELECT
>                         out_uid,
>                         author,
>                         nice,
>                         review,
>                         updated
>                 FROM words_reviews
>                 WHERE uid <> out_uid
>                 AND uid = ANY(_uids)
>                 ON CONFLICT DO NOTHING;
>
>                 DELETE FROM words_reviews
>                 WHERE uid <> out_uid
>                 AND uid = ANY(_uids);

Am I missing something, I thought word_reviews was?:

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)
);


So where are you getting?:

  SELECT
       out_uid,
       ...
FROM words_reviews

>
>                 -- try to copy as many reviews by this user as possible
>                 INSERT INTO words_reviews (
>                         uid,
>                         author,
>                         nice,
>                         review,
>                         updated
>                 ) SELECT
>                         uid,
>                         out_uid,
>                         nice,
>                         review,
>                         updated
>                 FROM words_reviews
>                 WHERE author <> out_uid
>                 AND author = ANY(_uids)
>                 ON CONFLICT DO NOTHING;
>


--
Adrian Klaver
adrian.klaver@aklaver.com