Обсуждение: Merging records in a table with 2-columns primary key
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
Also I have tried to create an SQL Fiddle at
for your convenience
Regards
Alex
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
Hello Andy and others -
On Sun, Apr 2, 2017 at 5:13 PM, Andy Colson <andy@squeakycode.net> wrote:
On 04/02/2017 09:26 AM, Alexander Farber wrote:http://stackoverflow.com/questions/43168406/merging-records- in-a-table-with-2-columns- primary-key
after some thinking, when I call
SELECT out_uid FROM merge_users(ARRAY[1,2,3,4]);
then "reviews" table should be empty (get rid of all self-reviews)
and "users" should contain just 1 record (1, 'User 1').
And if instead I call
SELECT out_uid FROM merge_users(ARRAY[1,2]);
then "reviews" should be (records with User 2 removed because overlapped with User 1):
uid | author | review
-----+--------+------------------------
1 | 3 | User 3 says: 1 is nice
1 | 4 | User 4 says: 1 is nice
3 | 1 | User 1 says: 3 is nice
3 | 4 | User 4 says: 3 is ugly
4 | 1 | User 1 says: 4 is ugly
4 | 3 | User 3 says: 4 is ugly
and "users":
uid | name
-----+--------
1 | User 1
3 | User 3
4 | User 4
So my 2 questions are -
1) Why the error when calling merge_users(ARRAY[1,2]) and then merge_users(ARRAY[1,2,3,4])?
2) Is there a way to use an UPDATE reviews instead of the inefficient (because copying) INSERT ... SELECT ... ON CONFLICT DO NOTHING?
Thank you
Alex
Alexander: On Sun, Apr 2, 2017 at 4:26 PM, Alexander Farber <alexander.farber@gmail.com> wrote: > The purpose of the function is to merge several user records to one (with > the lowest uid). It looks complicated ( more on this later ) > While merging the reviews records I delete all self-reviews and try to copy > over as many remaining reviews as possible. ... > 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). mmm, Maybe this is related to constrint immediateness, but anyway I would suggest another approach for calculating the set of new reviews. If you just delete every review for the set and capture the not-self-referential: with deleted as ( delete from reviews where uid in in_uids returning *) , candidates as ( select * from deleted where author not in in_uids ) You can then generate a new review-set from it with some criteria: , cleaned as (select author, min(review) as review from candidates group by 1) And then insert them back insert into reviews select $out_id, author, review from cleaned; If I were more fluent with the window functions I would recommend ordering the cleaned query by uid=$out_id DESC and getting the first row with one of them ( the DESC order puts true first, IIRC, so it favors keeping the original review for $out_id), or using string_agg to try to keep all the texts ). I've found that on many of this "merging" problems it's easier to extract all, clean them, reinsert. Normally my data sets are big so I just delete to a temporary ( not in sql way, just one which I create and then drop ) table, clean on it and reimport them. It also makes debugging the code much easier ( as the temp table can be cloned to test easily ). For encapsulation "with" helps a lot, or, in a function, you can use an real temporary table. Francisco Olarte