From: | Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Want records to be UNIQUE. When searching for dupes, stop on first matching record. |
Date: | 2019-04-18 22:26:00 |
Message-ID: | CAF4RT5TT7Vc4oUj8n7XtDiOnfRsB4eZLYQ634wPSo2P5dLpCdg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
Le jeu. 18 avr. 2019 à 22:41, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> a écrit :
> 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.
I know this and, indeed, I could have done it. But, the real thrust of
my question is how do I "drop out" of the query after finding the
first matching record. Putting indexes to the side - it's more of an
SQL question than just adding an index.
> That said your query is overly complicated...
> SELECT user_id, article_id
> FROM test_article
> GROUP BY user_id, article_id
> HAVING count(*) > 1
Sorry - the "WHERE 1 = (SELECT 1)" was just something I was testing
to get plans to be more complicated.
Forget that bit. Should have proofed better! :-(
> 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.
What I want to know is as above - once I find one match - that's it,
all bets are off, there's no point in looking any further. A dupe
means that my previous attempt to insert UNIQUE records failed - I'd
like to be told of that sooner rather than later.
Thanks for your input!
Rgs,
Pól...
> David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2019-04-18 22:37:25 | Re: Want records to be UNIQUE. When searching for dupes, stop on first matching record. |
Previous Message | David G. Johnston | 2019-04-18 21:40:53 | Re: Want records to be UNIQUE. When searching for dupes, stop on first matching record. |