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.
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 -
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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4