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