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

On Thu, Apr 18, 2019 at 1:15 PM Pól Ua Laoínecháin <linehanp(at)tcd(dot)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.