Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux