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.
|
| Список | 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 по дате отправления: