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

On 03/14/2017 07:23 AM, Alexander Farber wrote:
> Hi Adrian,
>
> in _uids array I have all user ids of player.
>
> I want to merge his or her data, including reviews, to a single user id:
> out_uid.
>
> So I make a copy of related words_reviews records (where this user has

Alright I see that you are setting out_uid above the INSERT.
So you are INSERTing rows and if they CONFLICT you leave them alone and
have the DELETE remove them, correct?


> been rated or this user has rated someone) and then re-INSERT ON
> CONFLICT DO NOTHING those records into same table (but change the "uid"
> or "author" column). And finally DELETE old records.
>
> I hope my question is not too annoying, just trying to pick up tricks
> and better strategies here.
>
> Thank you
> Alex
>
> P.S. Below is my table data and the complete custom function for your
> convenience -
>
> CREATE TABLE words_social (
>         sid varchar(255) NOT NULL,
>
>         social integer NOT NULL CHECK (0 <= social AND social <= 6),  /*
> Facebook, Google+, Twitter, ... */
>         female integer NOT NULL CHECK (female = 0 OR female = 1),
>         given  varchar(255) NOT NULL CHECK (given ~ '\S'),
>         family varchar(255),
>         photo  varchar(255) CHECK (photo ~* '^https?://...'),
>         place  varchar(255),
>         stamp  integer NOT NULL,
>
>         uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
>         PRIMARY KEY(sid, social)
> );
>
> CREATE TABLE words_users (
>         uid SERIAL PRIMARY KEY,
>
>         created timestamptz NOT NULL,
>         visited timestamptz NOT NULL,
>         ip inet NOT NULL,
> .....
>         win    integer NOT NULL CHECK (win >= 0),
>         loss   integer NOT NULL CHECK (loss >= 0),
>         draw   integer NOT NULL CHECK (draw >= 0),
>
>         elo    integer NOT NULL CHECK (elo >= 0),
>         medals integer NOT NULL CHECK (medals >= 0),
>         coins  integer NOT NULL
> );
>
> 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)
> );
>
> CREATE OR REPLACE FUNCTION words_merge_users(
>                 in_users jsonb,
>                 in_ip inet,
>                 OUT out_uid integer
>         ) RETURNS RECORD AS
> $func$
> DECLARE
>         _user          jsonb;
>         _uids          integer[];
>         -- the variables below are used to temporary save new user stats
>         _created       timestamptz;
>         _win           integer;
>         _loss          integer;
>         _draw          integer;
>         _elo           integer;
>         _medals        integer;
>         _coins         integer;
> BEGIN
>         -- in_users must be a JSON array with at least 1 element
>         IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
>                 RAISE EXCEPTION 'Invalid users = %', in_users;
>         END IF;
>
>         _uids := (
>                 SELECT ARRAY_AGG(DISTINCT uid)
>                 FROM words_social
>                 JOIN JSONB_ARRAY_ELEMENTS(in_users) x
>                         ON sid = x->>'sid'
>                         AND social = (x->>'social')::int
>         );
>
>         IF _uids IS NULL THEN
>                 -- no users found -> create a new user
>                 INSERT INTO words_users (
>                         created,
>                         visited,
>                         ip,
>                         win,
>                         loss,
>                         draw,
>                         elo,
>                         medals,
>                         coins
>                 ) VALUES (
>                         CURRENT_TIMESTAMP,
>                         CURRENT_TIMESTAMP,
>                         in_ip,
>                         0,
>                         0,
>                         0,
>                         1500,
>                         0,
>                         0
>                 ) RETURNING uid INTO STRICT out_uid;
>
>         ELSIF CARDINALITY(_uids) = 1 THEN
>                 -- just 1 user found -> update timestamp and IP address
>                 SELECT
>                         uid
>                 INTO STRICT
>                         out_uid
>                 FROM words_users
>                 WHERE uid = _uids[1];
>
>                 UPDATE words_users SET
>                         visited = CURRENT_TIMESTAMP,
>                         ip      = in_ip
>                 WHERE uid = out_uid;
>         ELSE
>                 -- few users found -> merge their records to a single one
>                 SELECT
>                         MIN(uid),
>                         MIN(created),
>                         SUM(win),
>                         SUM(loss),
>                         SUM(draw),
>                         AVG(elo),
>                         SUM(medals),
>                         SUM(coins)
>                 INTO STRICT
>                         out_uid,
>                         _created,
>                         _win,
>                         _loss,
>                         _draw,
>                         _elo,
>                         _medals,
>                         _coins
>                 FROM words_users
>                 WHERE uid = ANY(_uids);
>
>                 -- 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);
>
>                 -- 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;
>
>                 DELETE FROM words_reviews
>                 WHERE author <> out_uid
>                 AND author = ANY(_uids);
>
>                 UPDATE words_social
>                 SET uid = out_uid
>                 WHERE uid = ANY(_uids);
>
>                 DELETE FROM words_users
>                 WHERE uid <> out_uid
>                 AND uid = ANY(_uids);
>
>                 UPDATE words_users SET
>                         visited       = CURRENT_TIMESTAMP,
>                         ip            = in_ip,
>                         created       = _created,
>                         vip_until     = out_vip,
>                         grand_until   = out_grand,
>                         banned_until  = out_banned,
>                         banned_reason = out_reason,
>                         win           = _win,
>                         loss          = _loss,
>                         draw          = _draw,
>                         elo           = _elo,
>                         medals        = _medals,
>                         coins         = _coins
>                 WHERE uid = out_uid;
>
>                 -- TODO merge playing stats here
>         END IF;
>
>         FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
>         LOOP
>                 IF NOT words_valid_user((_user->>'social')::int,
>                                          _user->>'sid',
>                                          _user->>'auth') THEN
>                         RAISE EXCEPTION 'Invalid user = %', _user;
>                 END IF;
>
>                 UPDATE words_social SET
>                         social = (_user->>'social')::int,
>                         female = (_user->>'female')::int,
>                         given  =  _user->>'given',
>                         family =  _user->>'family',
>                         photo  =  _user->>'photo',
>                         place  =  _user->>'place',
>                         stamp  = (_user->>'stamp')::int,
>                         uid    =
> out_uid
>                 WHERE sid = _user->>'sid'
>                 AND social = (_user->>'social')::int;
>
>                 IF NOT FOUND THEN
>                         INSERT INTO words_social (
>                                 sid,
>                                 social,
>                                 female,
>                                 given,
>                                 family,
>                                 photo,
>                                 place,
>                                 stamp,
>                                 uid
>                         ) VALUES (
>                                  _user->>'sid',
>                                 (_user->>'social')::int,
>                                 (_user->>'female')::int,
>                                  _user->>'given',
>                                  _user->>'family',
>                                  _user->>'photo',
>                                  _user->>'place',
>                                 (_user->>'stamp')::int,
>                                 out_uid
>                         );
>                 END IF;
>         END LOOP;
> END
> $func$ LANGUAGE plpgsql;
>
> -- usage example:
>
> -- SELECT out_uid FROM
>
words_merge_users('[{"sid":"abcde","auth":"1fe693affff84cb1e961857cccffffff","social":1,"given":"Abcde1","female":0,"stamp":1450102770},{"sid":"abcde","auth":"2fe693affff84cb1e961857cccffffff","social":2,"given":"Abcde2","female":0,"stamp":1450102880},{"sid":"abcde","auth":"3fe693affff84cb1e961857cccffffff","social":3,"given":"Abcde3","female":0,"stamp":1450102990},{"sid":"abcde","auth":"4fe693affff84cb1e961857cccffffff","social":4,"given":"Abcde4","female":0,"stamp":1450109999}]'::jsonb,
> '0.0.0.0'::inet);
>


--
Adrian Klaver
adrian.klaver@aklaver.com