Do random sort with seed values return same set again even some rows are deleted?

user_9708616 0
Viewed 147

I'm writing a pagination API to give random result by setting random seed value. unless the user wants to shuffle the result, the result will be same but if the user wants to shuffle I will recreate seed values so it will return a different set of record.

My problem here is if I delete a record from the DB, even if the seed is same the result is coming different because of the change in elements length. Is there any way to make it persistent even after the record is deleted?

Example query:

set seed to 0.1; select * from table order by random();
random-seed postgresql
1 Answers

I would not order by random(), because that cannot be controlled properly.

If you want a dependable, but seemingly random order, you could use a hash function:

SELECT * FROM atable ORDER BY hashint4(id);

That would be stable over DELETEs, because the hash values do not change.

If you need different "random" orders, generate a random integer in a repeatable fashion and use

... ORDER BY hashint4(id + 42)
user_64643080
Related