UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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

And then I have another larger table holding the rest of user information:

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

Whenever the mobile app notices, that the user authenticated against several social networks - I merge his data in my custom function:

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"):

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

And now I have a problem, because while merging user data I can not just:

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

And same for the authoring part - I can not just:

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

Because this might result in PRIMARY KEY(uid, author) conflicts in the words_reviews table.

I wish there would be possibility to add ON CONFLICT DO NOTHING /* and drop the review */ to the both UPDATE's above, but there is no such thing described at https://www.postgresql.org/docs/9.5/static/sql-update.html

What would you please recommend in my situation?

I'd like to merge user data including reviews and silently drop any conflicting review records...

Regards

Alex