lundi 13 mai 2019

postgresql: How to grab an existing id from a not subsequent ids of a table

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.




Aucun commentaire:

Enregistrer un commentaire