Want records to be UNIQUE. When searching for dupes, stop on firstmatching record. - Mailing list pgsql-novice

Hi all,

I was fiddling round with a 10M record table recently, trying to get a
unique (simulated) combination for two INTEGERs user_id and article_id.

I eventually came up with a solution (is it the best way of doing
this? - secondary question):

INSERT INTO test_article (user_id, article_id)
SELECT * FROM
(
  WITH x AS
  (
    SELECT generate_series(1, 500) AS bill
  ),
  y AS
  (
    SELECT generate_series(1, 20000) AS fred
  )
  SELECT * FROM x
  CROSS JOIN y
) AS z
ORDER BY bill, fred;

But, while I was experimenting with this, I had to test many times to
see if I was, in fact, inserting UNIQUE records - it took me a while
to come up with the SQL above!

It took around 30s to search through my table each time I was checking
(thank God for SSDs :-) ). I used this construct to check for dupes
(is there a better one?):

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?

TIA and rgs,

Pól...