0

I tried the approach suggested here: Best way to select random rows PostgreSQL:

CREATE OR REPLACE FUNCTION any_test_user() RETURNS JSONB AS $$
  WITH j_ret AS (
    SELECT * FROM "User"
    WHERE "nickname" LIKE 'test%' AND random() < 0.2 LIMIT 1
  )
  SELECT to_jsonb(j_ret) FROM j_ret;
$$ LANGUAGE 'sql';

However, when the table is small, it occasionally returns null. What's the best way to return just 1 random single row?

zcaudate
  • 13,998
  • 7
  • 64
  • 124
  • What is best depends on what compromises you are willing to make for the sake of performance. S-mans answer will not be fast, but it is pretty much foolproof. – jjanes Feb 26 '21 at 15:56

1 Answers1

1

This is independent from the table size:

SELECT * FROM "User"
WHERE "nickname" LIKE 'test%'
ORDER BY random()
LIMIT 1
S-Man
  • 22,521
  • 7
  • 40
  • 63