Randomly Selecting Rows in PostgreSQL

Random Selection of Rows in PostgreSQL:

In PostgreSQL, you can retrieve a random selection of rows from a table by using specific functions and techniques to introduce randomness into your query. This is useful for sampling, testing, or creating randomized datasets.

Description

To randomly select rows in PostgreSQL, you can use the RANDOM() function in combination with ORDER BY and LIMIT. By ordering rows based on a random value, you achieve an unpredictable sequence of rows and can limit the results to get a desired sample size.

Syntax:

The general syntax for randomly selecting rows in PostgreSQL is as follows:

SELECT * FROM table_name ORDER BY RANDOM() LIMIT n;
table_name: The table from which you want to select rows.

Where -

• n: The number of random rows to retrieve.

Example: Selecting 5 Random Rows from a Table

Code:

-- Select 5 random rows from the "employees" table
SELECT * FROM employees
ORDER BY RANDOM()
LIMIT 5;

Explanation:

• SELECT * FROM employees: Retrieves all columns from the employees table.
• ORDER BY RANDOM(): Orders the rows by a randomly generated value for each row, resulting in a random ordering of the table’s rows.
• LIMIT 5: Restricts the result to 5 rows, providing a sample of 5 randomly chosen rows from the table.

Example: Different number of rows at random

This code demonstrates selecting a different number of rows at random.

Code:

-- Select 10 random rows from the "customers" table
SELECT * FROM customers               -- Selects all columns from "customers" table
ORDER BY RANDOM()                      -- Orders rows randomly using the RANDOM() function
LIMIT 10;                              -- Limits the result to 10 random rows

Tips for Large Datasets:

For very large tables, ordering by RANDOM() can be inefficient, as it involves sorting the entire dataset. In such cases, it is better to use other techniques, like using TABLESAMPLE (if available) or selecting rows based on random IDs within certain ranges.

All PostgreSQL Questions, Answers, and Code Snippets Collection.