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

Поиск

Список

Период

Сортировка

От	Alexander Farber
Тема	Re: Merging records in a table with 2-columns primary key
Дата	2 апреля 2017 г. 21:27:28
Msg-id	CAADeyWj7K6V=E+rLNEksEtWjPVVc_2Rxkw3Mz=xmvY2vp8vC8Q@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Merging records in a table with 2-columns primary key (Andy Colson <andy@squeakycode.net>)
Список	pgsql-general

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

В списке pgsql-general по дате отправления:

От: Andy Colson
Дата: 02 апреля 2017 г., 21:13:07
Сообщение: Re: Merging records in a table with 2-columns primary key

От: Francisco Olarte
Дата: 02 апреля 2017 г., 21:37:36
Сообщение: Re: Merging records in a table with 2-columns primary key