How to get random rows within all possible options in postgresql

Question

How do you get all possible options based on 2 columns with a random value from the third column?

Id | color | shape
1  | red   | square
2  | blue  | square
3  | red   | square
4  | red   | circle
5  | blue  | circle

So we have all possible shapes and colors with one random id

Id | color | shape     or   Id | color | shape 
1  | red   | square         3  | red   | square
2  | blue  | square         2  | blue  | square
4  | red   | circle         4  | red   | circle
5  | blue  | circle         5  | blue  | circle

Answer 1

You can enumerate the distinct values in both columns separately, then cross join them to get all possible combinations. To generate the id, row_number() and a random sort is good enough:

select row_number() over(order by random()) id, c.color, s.shape
from (select distinct color from mytable) c
cross join (select distinct shape from mytable) s
order by c.color, s.shape

Of course, if all the combinations are already in the table, select distinct is sufficient:

select t.*, row_number() over(order by random()) id
from (select distinct color, shape from mytable t)

Answer 2

It's unclear whether you want to generate all possible combinations (see GMB's answer) or whether you just want to filter the existing combinations so that you get each only once. This you can do using the approach from this answer:

SELECT DISTINCT ON (color, shape)
  color,
  shape,
  first_value(id) OVER (PARTITION BY color, shape ORDER BY random())
FROM the_table;