In PostgreSQL, the tables maintain the default insertion order. To specify the table records in a particular order(ascending or descending), the “ORDER BY” clause is used in Postgres. However, if we have to specify the table’s records in a random order, then we can use the “ORDER BY RANDOM” function.

This post demonstrates various methods to explain the usage of the Postgres ORDER BY RANDOM function.

How to Use PostgreSQL ORDER BY RANDOM?

In Postgres, the ORDER BY clause is used with the RANDOM() function to get the random data from large tables.

SELECT col_list
FROM tab_name
ORDER BY RANDOM();

It retrieves the data faster because the “ORDER BY RANDOM” returns a random number from the table.

Example 1: How Does ORDER BY RANDOM() Work in Postgres?

A sample table named “staff_info” has already been created with the following content:

SELECT * FROM staff_info;

Now execute the SELECT command with the “ORDER BY RANDOM” to get the table’s data in random order:

SELECT * FROM staff_info
ORDER BY RANDOM();

The output shows that the “ORDER BY RANDOM()” function retrieves the table’s data in random order.

Example 2: How Does ORDER BY RANDOM() Work With the WHERE Clause in Postgres?

Use the “ORDER BY RANDOM()” with the “WHERE” clause to get the filtered random records:

SELECT * FROM staff_info
WHERE staff_id <= 8
ORDER BY RANDOM();

The ORDER BY RANDOM retrieves the random records, but according to the condition specified within the WHERE clause.

Example 3: How Does ORDER BY RANDOM() Work With the LIMIT Clause in Postgres?

Use the “ORDER BY RANDOM()” with the “LIMIT” clause to get only limited random records from the selected table:

SELECT * FROM staff_info
ORDER BY RANDOM()
LIMIT 5;

In the above snippet, the limit is specified as “5”; as a result, the RANDOM() function will retrieve only five random records from the “staff_info” table:

Whenever you use the "ORDER BY RANDOM()" function with the LIMIT clause, you'll get the tables' records within the specified limit but in a different/random order.

Example 4: How Does ORDER BY RANDOM() Work With the BETWEEN Operator in Postgres?

Use the “ORDER BY RANDOM()” with the “BETWEEN” operator to get the random records from the selected table, but within the specified range:

SELECT * FROM staff_info
WHERE staff_id BETWEEN 5 AND 10
ORDER BY RANDOM();

This time the “ORDER BY RANDOM” function retrieves the random records within the specified range.

Example 5: How Does ORDER BY RANDOM() Work With the IN Operator in Postgres?

Use the “ORDER BY RANDOM()” with the “IN” operator to get the random records based on the specific condition:

SELECT * FROM staff_info
WHERE staff_id BETWEEN 5 AND 10
ORDER BY RANDOM();

The above output shows that the ORDER BY RANDOM retrieves the random records from the staff_info table based on the condition specified in the “IN” operator.

Conclusion

In Postgres, the ORDER BY clause is used with the RANDOM() function to get the random data from large tables. The ORDER BY RANDOM can be used with different clauses and operators to avail maximum functionality, such as it can be used with WHERE clause, LIMIT clause, BETWEEN operator, etc. ORDER BY RANDOM is very useful when working with large Postgres tables. This post has explained the usage of the ORDER BY RANDOM in Postgres via numerous examples.