Re: Merging records in a table with 2-columns primary key

On 04/02/2017 09:26 AM, Alexander Farber wrote:
> Good afternoon,
>
> I have prepared a simple test case for my question -
>
> CREATE TABLE users (
> uid SERIAL PRIMARY KEY,
> name varchar(255) NOT NULL
> );
>
> 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)
> );
>
> Here I fill the above tables with sample data -
>
> 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');
>
> And finally here is my problematic custom stored function:
>
> 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;
>
> The purpose of the function is to merge several user records to one (with the lowest uid).
>
> While merging the reviews records I delete all self-reviews and try to copy over as many remaining reviews as possible.
>
> However with PostgreSQL 9.5 the following 2 calls fail:
>
> 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
>
> I have provided more context at
> http://stackoverflow.com/questions/43168406/merging-records-in-a-table-with-2-columns-primary-key
>
> Also I have tried to create an SQL Fiddle at
> http://sqlfiddle.com/#!15/5f37e/2
> for your convenience
>
> Regards
> Alex

I'm not sure what you are trying to do. You posted a sample starting point, which is great. Perhaps you could post how you want the tables would look in the end?

-Andy