Learning PostgreSQL 11 - Third Edition

Selecting a data sample

Suppose the car_portal database is huge and there is a task to collect some statistics about the data. The statistics don't have to be exactly accurate, estimated values would be good enough. It's important to collect the data quickly. PostgreSQL provides a way to query a random fraction of a table's records.

The syntax is as follows:

SELECT ... FROM <table>   TABLESAMPLE <sampling_method> ( <argument> [, ...] )   [ REPEATABLE ( <seed> ) ]

The sampling_method can be either BERNOULLI or SYSTEM—these are provided with a standard PostgreSQL installation. More sampling methods are available as extensions. Both the sampling methods take one argument, which is a probability that a row will be selected, as a percentage. The ...