Re: 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(dot)klaver(at)aklaver(dot)com