Select Random from Postgresql Table with Where clauses and without PK

Question

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?

you can use ctid instead of sequential key, but it will probably take same time as order by random() — Vao Tsun
– Vao Tsun, Commented Mar 15, 2017 at 8:24

Community · Accepted Answer · 2017-05-23 11:46:33Z

1

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)

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Mar 15, 2017 at 16:44

light souls

7381 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

silidragos Over a year ago

Wow, thanks! I'm gonna look into it and try to understand how it works better tomorrow. It might solve my problems.

silidragos Over a year ago

It works pretty good, but gets me grouped random elements, so if N is a random number I also get N+1, N+2. I think it's linked with pagination. It's faster, though.

raphael Over a year ago

@silidragos I know this is years later but the SYSTEM method will return random pages of data, so they'll be grouped together, whereas the slower BERNOULLI method will return random rows

Collectives™ on Stack Overflow

Select Random from Postgresql Table with Where clauses and without PK

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related