CODEWITHSUNDEEP
sqldatabasepostgresql
silidragos
silidragos

Reputation: 161

Select Random from Postgresql Table with Where clauses and without PK

I'm having a table from which I want to select N random rows in PostgreSQL.

However, I don't want to select the whole table, but I want to select random from the rows that respect some constraints (e.g. Price in range, Color = "red" etc.)

I have already seen some ways to do this when id's are generated in a continuous manner. However, in this case, there will be a lot of gaps.

Is there any way of doing this without using order by random() which is to expensive?

Upvotes: 0

Views: 933

Answers (1)

light souls
light souls

Reputation: 728

With postgresql 9.5 you can use

TABLESAMPLE with methods BERNOULLI and SYSTEM

Hier is some good examples and explanations which is better:

How to select random rows

Compare different random methods

More examples here

First install the extension:

CREATE EXTENSION tsm_system_rows;

Try with this:

Select * from your_table tablesample   system_rows(1000)

Upvotes: 1

Related Questions