Re: Want records to be UNIQUE. When searching for dupes, stop onfirst matching record.

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Want records to be UNIQUE. When searching for dupes, stop onfirst matching record.
Дата
Msg-id CAKFQuwaLUp_KiNPPCTz+Ax=urmc5u-yAC_jr6j8Tn1Mp3=+EsQ@mail.gmail.com
обсуждение исходный текст
Ответ на Want records to be UNIQUE. When searching for dupes, stop on firstmatching record.  (Pól Ua Laoínecháin <linehanp@tcd.ie>)
Ответы Re: Want records to be UNIQUE. When searching for dupes, stop onfirst matching record.  (Pól Ua Laoínecháin <linehanp@tcd.ie>)
Список pgsql-novice
Дерево обсуждения
On Thu, Apr 18, 2019 at 1:15 PM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
SELECT (user_id, article_id)::text, count(*)
FROM test_article
WHERE 1 = (SELECT 1)
GROUP BY user_id, article_id
HAVING count(*) > 1

But what I really want (what I really, really want - apologies to the
Spice Girls) to know is, is there a query which will run and stop on
the first match? Using ANY, ALL or EXISTS or some construct like that?


Create a unique index over user_id, article_id on the test_article table.  The system will never allow a duplicate to be inserted in the first place.

That said your query is overly complicated...
SELECT user_id, article_id
FROM test_article
GROUP BY user_id, article_id
HAVING count(*) > 1

A where clause is optional and the composite and count in the select list aren't really needed unless you have some other unstated requirement for them.

David J.

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

Предыдущее
От: Pól Ua Laoínecháin
Дата:
Сообщение: Want records to be UNIQUE. When searching for dupes, stop on firstmatching record.
Следующее
От: Pól Ua Laoínecháin
Дата:
Сообщение: Re: Want records to be UNIQUE. When searching for dupes, stop onfirst matching record.