How can I get a random cartesian product in PostgreSQL?

Question

I have two tables, custassets and tags. To generate some test data I'd like to do an INSERT INTO a many-to-many table with a SELECT that gets random rows from each (so that a random primary key from one table is paired with a random primary key from the second). To my surprise this isn't as easy as I first thought, so I'm persisting with this to teach myself.

Here's my first attempt. I select 10 custassets and 3 tags, but both are the same in each case. I'd be fine with the first table being fixed, but I'd like to randomise the tags assigned.

SELECT
    custassets_rand.id custassets_id,
    tags_rand.id tags_rand_id
FROM
    (
        SELECT id FROM custassets WHERE defunct = false ORDER BY RANDOM() LIMIT 10
    ) AS custassets_rand
,
    (
        SELECT id FROM tags WHERE defunct = false ORDER BY RANDOM() LIMIT 3
    ) AS tags_rand

This produces:

custassets_id | tags_rand_id 
---------------+--------------
          9849 |         3322  }
          9849 |         4871  } this pattern of tag PKs is repeated
          9849 |         5188  }
         12145 |         3322
         12145 |         4871
         12145 |         5188
         17837 |         3322
         17837 |         4871
         17837 |         5188
....

I then tried the following approach: doing the second RANDOM() call in the SELECT column list. However this one was worse, as it chooses a single tag PK and sticks with it.

SELECT
    custassets_rand.id custassets_id,
    (SELECT id FROM tags WHERE defunct = false ORDER BY RANDOM() LIMIT 1) tags_rand_id
FROM
    (
        SELECT id FROM custassets WHERE defunct = false ORDER BY RANDOM() LIMIT 30
    ) AS custassets_rand

Result:

 custassets_id | tags_rand_id 
---------------+--------------
         16694 |         1537
         14204 |         1537
         23823 |         1537
         34799 |         1537
         36388 |         1537
....

This would be easy in a scripting language, and I'm sure can be done quite easily with a stored procedure or temporary table. But can I do it just with a INSERT INTO SELECT?

I did think of choosing integer primary keys using a random function, but unfortunately the primary keys for both tables have gaps in the increment sequences (and so an empty row might be chosen in each table). That would have been fine otherwise!

Answer 1

_{Updated to replace CTEs with subqueries which are typically faster.}

To produce truly random combinations, it's enough to randomize rn for the bigger set:

SELECT c_id, t_id
FROM  (
   SELECT id AS c_id, row_number() OVER (ORDER BY random()) AS rn
   FROM   custassets
   ) x
JOIN   (SELECT id AS t_id, row_number() OVER () AS rn FROM tags) y USING (rn);

If arbitrary combinations are good enough, this is faster (especially for big tables):

SELECT c_id, t_id
FROM   (SELECT id AS c_id, row_number() OVER () AS rn FROM custassets) x
JOIN   (SELECT id AS t_id, row_number() OVER () AS rn FROM tags) y USING (rn);

If the number of rows in both tables do not match and you do not want to lose rows from the bigger table, use the modulo operator % to join rows from the smaller table multiple times:

SELECT c_id, t_id
FROM  (
   SELECT id AS c_id, row_number() OVER () AS rn
   FROM   custassets -- table with fewer rows
   ) x
JOIN  (
   SELECT id AS t_id, (row_number() OVER () % small.ct) + 1 AS rn
   FROM   tags
       , (SELECT count(*) AS ct FROM custassets) AS small
   ) y USING (rn);

As mentioned in my comment, window functions (with appended OVER clause) are available in PostgreSQL 8.4 or later.