How to Select Random Row in PostgreSQL?

In PostgreSQL, selecting a random row from a table can be a useful feature in scenarios such as sampling data for analysis or displaying random content. PostgreSQL provides several techniques to achieve this, including using the RANDOM() function or the ORDER BY RANDOM() clause. In this article, we will explore these methods and demonstrate how to select a random row from a table in PostgreSQL along with examples.

How to SELECT random rows?

In PostgreSQL, the SELECT statement is used to retrieve data from one or more tables in the database. It allows us to specify which columns to retrieve, as well as any filtering, sorting, or grouping criteria to apply to the data. Also, Selecting Random Row in PostgreSQL is achieved through the below method are as follow:

Using ORDER BY with RANDOM() Function
Using RANDOM() Function with OFFSET and LIMIT Clause

Let’s set up an Environment for Select Random Row

To understand How to Select Random Row in PostgreSQL we need a table on which we will perform various operations. So we create a table example_table.

Creating the table example_table

CREATE TABLE example_table 
(
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(50),
    gender VARCHAR(10),
    age INT
);

Inserting 10 values in example_table

INSERT INTO example_table (student_name, gender, age) VALUES
('Rahul Sharma', 'Male', 22),
('Priya Patel', 'Female', 21),
('Amit Singh', 'Male', 23),
('Anjali Desai', 'Female', 20),
('Vikram Verma', 'Male', 22),
('Pooja Reddy', 'Female', 23),
('Rajesh Kapoor', 'Male', 21),
('Neha Gupta', 'Female', 20),
('Kunal Suri', 'Male', 22),
('Meera Sharma', 'Female', 21);

--show table data
SELECT * FROM example_table;

Output:

example_table

1. Using ORDER BY with RANDOM() Function

In PostgreSQL, the RANDOM() function produces a random number between 0 and 1. It can be used to choose random rows by shuffle the result set when paired with ORDER BY.

Syntax:

SELECT * FROM your_table 
ORDER BY RANDOM() LIMIT value;

Explanation Replace your_table with the actual name of our table, and change the LIMIT value to get the desired amount of random rows.

Example 1: Selecting Rows Randomly

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

Output:

Selecting Rows Randomly

Explanation: The query mentioned above pulls five rows at random from the example_table table.

Example 2: Selecting Rows Randomly Based on Some Conditions

SELECT * FROM example_table 
WHERE gender='Female' 
ORDER BY RANDOM() LIMIT 5;

Output :

Selecting Rows Randomly Based on Some Conditions

Explanation: The query mentioned above retrieves five rows from the example_table at random with the gender set to Female.

2. Using RANDOM() Function with OFFSET and LIMIT

In PostgreSQL, the RANDOM() function produces a random number between 0 and 1. Selecting a random selection of rows from a table is the main goal of using RANDOM() with OFFSET and LIMIT in a PostgreSQL query.

Syntax:

SELECT * FROM your_table
OFFSET floor(RANDOM() * (SELECT COUNT(*) FROM your_table))
LIMIT value;

Explanation:

1) ‘your_table’: Replace this with the actual name of your table.
2) ‘RANDOM()’: This function generates a random value between 0 and 1.
3) ‘floor(random() * (SELECT COUNT(*) FROM your_table))’: This section produces a random offset by using floor() to round down after multiplying the number of rows in your table by a random amount. This aids in choosing a random starting place for the table.
4) LIMIT value: change the LIMIT value to get the desired amount of random rows.

Example:

SELECT * FROM example_table 
OFFSET floor(random() * (SELECT COUNT(*) 
FROM example_table)) LIMIT 5;

Output:

Output

Explanation: The query mentioned above selects 5 random rows from the example_table, starting from a dynamically determined offset within the table.

Conclusion

In Conclusion, Use the RANDOM() method with the ORDER BY clause to retrieve random rows from a database table. Using PostgreSQL’s OFFSET and LIMIT clauses with the RANDOM() functions offers a dependable and adaptable method for choosing random rows from a table. Additionally, we can indicate the maximum number of rows we wish to collect or fetch. This article has covered the use of the RANDOM() method to extract random rows from a table, complete with real-world applications.

Article Tags :

Databases

PostgreSQL

PostgreSQL Query