sql - 合并具有 2 列主键的表中的记录

Question

我已经为我的问题准备了一个简单的测试用例-

在游戏中，玩家 ID 和名称存储在表 users 中:

CREATE TABLE users (
        uid SERIAL PRIMARY KEY,
        name varchar(255) NOT NULL
);

玩家可以在表reviews中用2列PK互相评价:

CREATE TABLE reviews (
        uid integer NOT NULL CHECK (uid <> author) REFERENCES users ON DELETE CASCADE,
        author integer NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
        review varchar(255),
        PRIMARY KEY(uid, author)
);

这里两个表都填充了示例数据:

INSERT INTO users (uid, name) VALUES (1, 'User 1');
INSERT INTO users (uid, name) VALUES (2, 'User 2');
INSERT INTO users (uid, name) VALUES (3, 'User 3');
INSERT INTO users (uid, name) VALUES (4, 'User 4');

INSERT INTO reviews (uid, author, review) VALUES (1, 2, 'User 2 says: 1 is nice');
INSERT INTO reviews (uid, author, review) VALUES (1, 3, 'User 3 says: 1 is nice');
INSERT INTO reviews (uid, author, review) VALUES (1, 4, 'User 4 says: 1 is nice');

INSERT INTO reviews (uid, author, review) VALUES (2, 1, 'User 1 says: 2 is nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 3, 'User 3 says: 2 is nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 4, 'User 4 says: 2 is ugly');

INSERT INTO reviews (uid, author, review) VALUES (3, 1, 'User 1 says: 3 is nice');
INSERT INTO reviews (uid, author, review) VALUES (3, 2, 'User 2 says: 3 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (3, 4, 'User 4 says: 3 is ugly');

INSERT INTO reviews (uid, author, review) VALUES (4, 1, 'User 1 says: 4 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 2, 'User 2 says: 4 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 3, 'User 3 says: 4 is ugly');

当我的移动应用注意到同一玩家正在使用多个用户 ID 时，它会将记录与如下所示的自定义存储函数合并。

在合并(到 out_uid)时，用户对他自己的评论被删除，任何由此产生的重叠评论也应该被删除。

(对于合并记录的背景:这真的很有必要，因为我运行了另一个有玩家评论的游戏多年，用户一直缠着我-为什么他们的评论和游戏统计数据不同，当他们登录时通过 Facebook、通过 Google+、通过 Apple Game Center...)

因为没有 UPDATE ... ON CONFLICT DO NOTHING - 我尝试用以下两个来帮助自己 INSERT ... SELECT ... ON CONFLICT DO NOTHING在自定义存储函数中:

CREATE OR REPLACE FUNCTION merge_users(
                in_uids integer[],
                OUT out_uid integer
        ) RETURNS integer AS
$func$
BEGIN
        SELECT
                MIN(uid)
        INTO STRICT
                out_uid 
        FROM users
        WHERE uid = ANY(in_uids);

        -- delete self-reviews
        DELETE FROM reviews
        WHERE uid = out_uid
        AND author = ANY(in_uids);

        DELETE FROM reviews
        WHERE author = out_uid
        AND uid = ANY(in_uids);

        -- try to copy as many reviews OF this user as possible
        INSERT INTO reviews (
                uid,
                author,
                review
        ) SELECT
                out_uid,        -- change to out_uid
                author,
                review
        FROM reviews
        WHERE uid <> out_uid
        AND uid = ANY(in_uids)
        ON CONFLICT DO NOTHING;

        DELETE FROM reviews
        WHERE uid <> out_uid
        AND uid = ANY(in_uids);

        -- try to copy as many reviews BY this user as possible
        INSERT INTO reviews (
                uid,
                author,
                review
        ) SELECT
                uid,
                out_uid,        -- change to out_uid
                review
        FROM reviews
        WHERE author <> out_uid
        AND author = ANY(in_uids)
        ON CONFLICT DO NOTHING;

        DELETE FROM reviews
        WHERE author <> out_uid
        AND author = ANY(in_uids);

        DELETE FROM users
        WHERE uid <> out_uid
        AND uid = ANY(in_uids);
END
$func$ LANGUAGE plpgsql;

不幸的是，有问题 - 请运行 2 个命令来查看它们:

test=> SELECT out_uid FROM merge_users(ARRAY[1,2]);
 out_uid 
---------
       1
(1 row)

test=> SELECT out_uid FROM merge_users(ARRAY[1,2,3,4]);
ERROR:  new row for relation "reviews" violates check constraint "reviews_check"
DETAIL:  Failing row contains (1, 1, User 4 says: 3 is ugly).
CONTEXT:  SQL statement "INSERT INTO reviews (
                uid,
                author,
                review
        ) SELECT
                uid,
                out_uid,        -- change to out_uid
                review
        FROM reviews
        WHERE author <> out_uid
        AND author = ANY(in_uids)
        ON CONFLICT DO NOTHING"
PL/pgSQL function merge_users(integer[]) line 38 at SQL statement

所以删除自评好像不行，求助。

我还想知道是否有比我使用INSERT ... SELECT ... ON CONFLICT DO NOTHING 的技巧更好的方法来合并reviews 记录。

为了您的方便，我创建了一个 SQL Fiddle .

我也曾在很有帮助的 pgsql-general 上问过这个问题邮件列表。

Answer 1

我想我会通过以下方式解决这个问题:

• 根据组合的用户 ID 删除任何 self 评价。
• 将其余部分组合在一起。

我认为这是失败的第一部分。试试这个删除:

DELETE FROM reviews
WHERE uid = ANY(in_uids) AND author = ANY(in_uids);

也就是说，旧的 uids 的任何组合都是一个问题。我不确定 in_uids 是否包含所有 等效的 uid，但我的想法是整个等效类都用于此目的。