2

Postgresql version 9.4

I have a table with an integer column, which has a number of integers with some gaps, like the sample below; I'm trying to get an existing id from the column at random with the following query, but it returns NULL occasionally:

CREATE TABLE
IF NOT EXISTS test_tbl(
    id INTEGER);
INSERT INTO test_tbl
VALUES (10),
       (13),
       (14),
       (16),
       (18),
       (20);
-------------------------------    
SELECT * FROM test_tbl;

-------------------------------    
SELECT COALESCE(tmp.id, 20) AS classification_id
FROM (
       SELECT tt.id,
              row_number() over(
       ORDER BY tt.id) AS row_num
       FROM test_tbl tt
     ) tmp
WHERE tmp.row_num =floor(random() * 10);

Please let me know where I'm doing wrong.

Ms workaholic
  • 373
  • 2
  • 8
  • 21

2 Answers2

0

You could select one row and order by random(), this way you are ensured to hit an existing row

select id
from test_tbl
order by random()
LIMIT 1;
GuCier
  • 6,919
  • 1
  • 29
  • 36
0

but it returns NULL occasionally


and I must add to this that it sometimes returns more than 1 rows, right?
in your sample data there are 6 rows, so the column row_num will have a value from 1 to 6.
This:

floor(random() * 10)

creates a random number from 0 up to 0.9999...
You should use:

floor(random() * 6 + 1)::int

to get a random integer from 1 to 6.
But this would not solve the problem, because the WHERE clause is executed once for each row, so there is a case that row_num will never match the created random number, so it will return nothing, or it will match more than once so it will return more than 1 rows.
See the demo.

The proper (although sometimes not the most efficient) way to get a random row is:

SELECT id FROM test_tbl ORDER BY random() LIMIT 1

Also check other links from SO, like: quick random row selection in Postgres

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks for the helpful reply and correction, Actually, I tried to make the sample simple with removing some rows but forgot to update the floor number. BTW, you're right, it sometimes returns more than one row. – Ms workaholic May 13 '19 at 18:49
  • If you have a really large number of rows don't forget to search for other solutions too (like the link in my answer) because the *easy proper way* is not the most efficient way. – forpas May 13 '19 at 18:52