How to Select Random Rows in PostgreSQL

Use ORDER BY RANDOM() LIMIT n when you want up to a fixed number of randomly ordered rows from a PostgreSQL table. For example, SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2; returns at most two rows from pets (or fewer if the table has fewer rows). For a rough percentage sample whose size may vary, use WHERE RANDOM() < probability instead. These patterns use PostgreSQL’s pseudo-random function for ordinary sampling; they are not suitable for cryptographic choices.

If you want to copy the basic statement from a small runnable example, this Python snippet prints the SQL to run in your PostgreSQL session. It does not connect to a database or execute the query.

Run this example:

query = "SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;"
print(query)

SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;

Select random rows with RANDOM()

RANDOM() returns a pseudo-random double precision value from 0 inclusive to 1 exclusive. A condition such as SELECT id, name FROM pets WHERE RANDOM() < 0.05; considers each row independently, retaining each with roughly a 5% chance. The result count varies: it can be smaller or larger than a target count, including no rows, especially for a small table. Each call supplies a value for the row being evaluated; those values are not promised to be unique.

For a bounded number of rows, use SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;. PostgreSQL assigns sort keys and returns the first two rows in that ordering, if available. This is convenient and straightforward, but it generally requires evaluating candidate rows and sorting them. It is not automatically faster than probability filtering, and it can be expensive on a large table. Add the columns you need in the select list instead of * when that makes the result easier to use.

Select random rows from a large table

For a large relation, choose between the simple ordering query and a sample based on the amount and kind of randomness your task needs. TABLESAMPLE BERNOULLI (5) requests an approximate 5% sample by considering rows individually. TABLESAMPLE SYSTEM (5) samples table blocks, so rows close together on disk may appear together in the result. Both percentages are approximate, and a small table can return substantially fewer or more rows than expected. TABLESAMPLE runs before a later WHERE condition, so filters can further reduce the sample.

For example, SELECT id, name FROM pets TABLESAMPLE BERNOULLI (5); gives a variable-size sample; it does not promise exactly five rows or five percent for each execution. SYSTEM can reduce the work needed to select a sample on large tables, but block sampling may be less representative when storage order follows a meaningful grouping. Compare results against your actual data and requirements rather than assuming either method is universally better.

To make sampling repeatable for a particular query setup, add REPEATABLE (42), as in SELECT id, name FROM pets TABLESAMPLE BERNOULLI (5) REPEATABLE (42);. The seed can reproduce a sample when the table and relevant conditions remain suitable and unchanged; it is not a promise that results stay identical after table changes or across every environment.

PostgreSQL’s pg_class.reltuples can provide an estimated row count after ANALYZE has collected statistics. For instance, SELECT reltuples FROM pg_class WHERE oid = 'public.pets'::regclass; looks up the estimate for that relation. It can be stale or approximate, so do not use it as an exact count. COUNT(*) provides the actual count for the query’s snapshot, but may need to inspect many rows; avoid fixed timing claims because cost depends on the data and system.

Select one random row with OFFSET

If you know the current row count and can accept the cost of skipping rows, a random offset can select one row by position: SELECT id, name FROM pets ORDER BY id OFFSET floor(random() * row_count) LIMIT 1;. Replace row_count with a count obtained for the same query context. Without an ORDER BY, row positions are unspecified; with an order, concurrent inserts or deletes can change which row occupies a position. Counting and skipping can also be costly, so this pattern is not inherently an efficient single-row lookup. If the table has no rows, the query returns no row; if its contents change between counting and selection, an offset based on the earlier count may no longer describe the current set.

Use a row estimate to select a random row

An estimate can help choose a threshold for a key, but it does not itself sample a row. The ::regclass cast in the statistics query identifies public.pets so PostgreSQL can find its pg_class entry. A threshold pattern such as WHERE id >= floor(random() * estimated_count) ORDER BY id LIMIT 1 only makes sense when id has suitable numeric values and an appropriate index. Sparse or noncontiguous keys can leave gaps, cause the threshold to match no row, or make some rows more likely than others. Key ranges that do not start near zero also violate the pattern’s assumptions. Validate the key distribution and handle a missing match before relying on this approach. For a general-purpose query, ORDER BY RANDOM() LIMIT 1 is easier to reason about, though it may do more work on a large table.