[GENERAL] UPDATE ... ON CONFLICT DO NOTHING - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | [GENERAL] UPDATE ... ON CONFLICT DO NOTHING |
| Date | |
| Msg-id | CAADeyWh1S6BFPE_GdFNkSgjvthXegtM1P7ktz0bsZ8x8j2CuBw@mail.gmail.com Whole thread Raw |
| Responses |
Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING
Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING |
| List | pgsql-general |
Good afternoon,
I am working on a small game, where the mobile app initially sends social network user data (name, city, gender) to PostgreSQL 9.5 backend and I store that data in a table:CREATE TABLE words_social (
sid varchar(255) NOT NULL,
social integer NOT NULL CHECK (0 <= social AND social <= 6), /* Facebook, Googl+, 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 OR REPLACE FUNCTION words_merge_users(
in_users jsonb,
in_ip inet,
OUT out_uid integer /* the user id of the merged user */
) 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
_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
);
........
-- few users found -> merge their records to a single one
IF CARDINALITY(_uids) > 1 THEN
SELECT
MIN(uid),
MIN(created),
SUM(win),
SUM(loss),
SUM(draw),
AVG(elo),
SUM(medals),
SUM(coins)
INTO STRICT
out_uid, /* this is the new user id */
_created,
_win,
_loss,
_draw,
_elo,
_medals,
_coins
FROM words_users
WHERE uid = ANY(_uids);
-- How to merge words_reviews? Please read below...
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,
win = _win,
loss = _loss,
draw = _draw,
elo = _elo,
medals = _medals,
coins = _coins
WHERE uid = out_uid;
END IF;
END
$func$ LANGUAGE plpgsql;
This works well, but now I have introduced a table where users can rate each other ("author" can rate "uid"):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,
win = _win,
loss = _loss,
draw = _draw,
elo = _elo,
medals = _medals,
coins = _coins
WHERE uid = out_uid;
END IF;
END
$func$ LANGUAGE plpgsql;
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)
);
UPDATE words_reviews /* This will produce conflicts... */
SET uid = out_uid
WHERE uid = ANY(_uids);
DELETE FROM words_reviews
WHERE uid <> out_uid
AND uid = ANY(_uids);
UPDATE words_reviews /* This will produce conflicts... */
SET author = out_uid
WHERE author = ANY(_uids);
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);
pgsql-general by date: