In PostgreSQL, In order to select random rows from a table one can use TABLESAMPLE clause or the RANDOM() function in combination with the ORDER BY and LIMIT clauses. Let’s see how to
- Get the random rows from PostgreSQL using RANDOM() function.
- Get the random rows from PostgreSQL using TABLESAMPLE clause
- Random N percent of the rows from Postgresql Table
- Random N rows from Postgresql Table
Method 1: Using the TABLESAMPLE Clause
The TABLESAMPLE clause allows you to retrieve a sample of rows from a table. It is important to note that TABLESAMPLE is supported in PostgreSQL versions 9.5 and later.
SELECT * FROM your_table TABLESAMPLE BERNOULLI (percentage);
- BERNOULLI (percentage): Specifies the percentage of the table to be sampled.
We will be using Student_detail table.
For example, to select approximately 30% of the rows from the ”student_detail” table:
SELECT * FROM student_detail TABLESAMPLE BERNOULLI (30);
Output:
Method 2: Using the RANDOM() Function
The RANDOM() function generates a random number between 0 and 1. By combining this with ORDER BY and LIMIT, you can select a specified number of random rows.
SELECT * FROM your_table ORDER BY RANDOM() LIMIT number_of_rows;
We will be using Student_detail table.
Example 1 : Select Random N Number of rows in PostgreSQL:
For example, to select 5 random rows from the student_detail table:
SELECT * FROM student_detail ORDER BY RANDOM() LIMIT 5;
Output:
Example 2 : Select Random N Percentage of rows in PostgreSQL:
select * from student_detail where random() < 0.7
We will be generating random numbers between 0 and 1, then will be selecting with rows less than 0.7. So the resultant table will be
Output:
Performance Comparison of both methods
- ORDER BY RANDOM(): This method is straightforward but can be inefficient for large tables because it requires generating a random number for each row and then sorting the entire table.
- TABLESAMPLE: This method can be more efficient for large tables as it retrieves a sample of rows directly from the storage engine without the need to sort the entire table.
