Postgresql – Perform TABLESAMPLE with WHERE clause in PostgreSQL

postgresqlqueryrandomtablewhere

I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.

This runs fine:

select * from customers tablesample bernoulli (1);

But I cannot figure out how to embed the condition in the script. This for example 

select * from customers where last_name = 'powell' tablesample bernoulli (1);

throws this error:

SQL Error [42601]: ERROR: syntax error at or near "tablesample"
Position: 71

Best Answer

tablesample is an "attribute" of a table, not a query. So you need to write it write after the table name:

select * 
from customers tablesample system (1)
where last_name = 'powell';

Note that the where clause will be applied after the table has been sampled. It will not return 1% of all customers that have the last name 'powell'. But instead it will apply the filter on the sampled table.

Related Question