Re: UPDATE ... ON CONFLICT DO NOTHING

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