In PostgreSQL, we can select the random records from the database table. These records are of huge use sometimes and can be selected by using the RANDOM() function. This post will cover the method to select the random rows in a Postgres record.

How to Select Random Rows in PostgreSQL?

Generally, the RANDOM() function in Postgres is used to generate random numbers. But we can use it with the ORDER BY clause to retrieve random rows from a Postgres table. The basic syntax is

SELECT * FROM tab_name ORDER BY RANDOM() LIMIT no_of_rows;

In the above syntax:

- The table name is specified from which we want to retrieve the rows.

- The ORDER BY clause and the RANDOM() function are used together to get the random rows from a table.

- The number of rows you want to fetch/get is specified after the LIMIT keyword.

Let’s include an example for a better understanding of the topic.

Example 1: Using RANDOM() Function to Select Random Rows in Postgres

Consider the table named “test_scores”.

Now let’s suppose we want to get 3 random rows from the table. We can write the query as:

SELECT * FROM test_scores ORDER BY RANDOM() LIMIT 3;

This query returns 3 random rows from the table as shown in the following output:

We can see that the query has retrieved 3 random rows with no underlying set criteria.

Example 2: Retrieve Rows Based on Some Conditions

We can also place some conditions to extract the random records. For example, we can write the query to ask Postgres to generate the 3 random records for the male gender. The query will be:

SELECT * FROM test_scores WHERE candidate_gender = 'Male' 
ORDER BY random() LIMIT 3;

The query will return the output having 3 randomly retrieved rows of male gender like this:

This was all about fetching random rows in Postgres.

Conclusion

To get random rows from a database table, use the RANDOM() function with the ORDER BY clause. We can also specify the limit of the number of rows we want to fetch/retrieve. In this article, we have discussed getting the random rows from a table using the RANDOM() function with practical examples.