From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Merging records in a table with 2-columns primary key |
Date: | 2017-04-02 15:27:28 |
Message-ID: | CAADeyWj7K6V=E+rLNEksEtWjPVVc_2Rxkw3Mz=xmvY2vp8vC8Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello Andy and others -
On Sun, Apr 2, 2017 at 5:13 PM, Andy Colson <andy(at)squeakycode(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2017-04-02 15:37:36 | Re: Merging records in a table with 2-columns primary key |
Previous Message | Andy Colson | 2017-04-02 15:13:07 | Re: Merging records in a table with 2-columns primary key |