Re: Merging records in a table with 2-columns primary key - Mailing list pgsql-general

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
howyou want the tables would look in the end? 

-Andy