quick random row selection in Postgres

by

You might want to experiment with OFFSET, as in

SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;

The N is the number of rows in mytable. You may need to first do a SELECT COUNT(*) to figure out the value of N.

Update (by Antony Hatchkins)

You must use floor here:

SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;

Consider a table of 2 rows; random()*N generates 0 <= x < 2 and for example SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; returns 0 rows because of implicit rounding to nearest int.

More Related Contents:

  1. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  2. Postgres FOR LOOP
  3. What is PostgreSQL equivalent to autoincrement?
  4. INSERT with dynamic table name in trigger function
  5. psql: FATAL: Ident authentication failed for user “postgres”
  6. SQLAlchemy support of Postgres Schemas
  7. Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)
  8. How to increase the max connections in postgres?
  9. Run a PostgreSQL .sql file using command line arguments
  10. How to thoroughly purge and reinstall postgresql on ubuntu? [closed]
  11. Creating a copy of a database in PostgreSQL
  12. PostgreSQL function returning multiple result sets
  13. How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?
  14. Can’t connect the postgreSQL with psycopg2
  15. How do you change the character encoding of a postgres database?
  16. Difference between GiST and GIN index
  17. Test for null in function with varying parameters
  18. PostgreSQL: Why psql can’t connect to server?
  19. Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00
  20. Can I use return value of INSERT…RETURNING in another INSERT?
  21. Changing a postgres containers server port in Docker Compose
  22. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  23. no pg_hba.conf entry for host
  24. PostgreSQL – disabling constraints
  25. PostgreSQL authentication method 10 not supported
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment