How to use SELECT DISTINCT with RANDOM() function in PostgreSQL?

I am trying to run a SQL query to get four random items. As the table product_filter has more than one touple in product i have to use DISTINCT in SELECT, so i get this error:

for SELECT DISTINCT, ORDER BY expressions must appear in select list

But if i put RANDOM() in my SELECT it will avoid the DISTINCT result.

Someone know how to use DISTINCT with the RANDOM() function? Below is my problematic query.

SELECT DISTINCT
    p.id, 
    p.title
FROM
    product_filter pf
    JOIN product p ON pf.cod_product = p.cod
    JOIN filters f ON pf.cod_filter = f.cod
WHERE
    p.visible = TRUE
LIMIT 4
ORDER BY RANDOM();

Answer

You can simplify your query to avoid the problem a priori:

SELECT p.cod, p.title
FROM   product p
WHERE  p.visible
AND    EXISTS (
    SELECT 1
    FROM   product_filter pf
    JOIN   filters f ON f.cod = pf.cod_filter
    WHERE  pf.cod_product = p.cod
    )
ORDER  BY random()
LIMIT  4;

Major points:

• You have only columns from table product in the result, other tables are only checked for existence of a matching row. For a case like this the EXISTS semi-join is likely the fastest and simplest solution. Using it does not multiply rows from the base table product, so you don’t need to remove them again with DISTINCT.

• LIMIT has to come last, after ORDER BY.

• I simplified WHERE p.visible = 't' to p.visible, because this should be a boolean column.