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...