PostgreSQL - How to quickly select a random row from a table?

August 1, 2018

There's an easy way to show a random record in a table:

SELECT
	*
FROM
	table_name
ORDER BY
	RANDOM()
LIMIT 1;

But this query might take a while to finish as it reads the whole table first then takes out a random record from it.

To do it quick, you can try this query:

SELECT
	*
FROM
	table_name OFFSET floor(random() * (
		SELECT
			COUNT(*)
			FROM table_name))
LIMIT 1;

*Need a good GUI Tool for PostgreSQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. *

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.