PostgreSQL 生成随机整数

首先 random() 函数用于生成 0-1 之间的随机数

postgres=# SELECT random() as rand;
        rand
--------------------
 0.6296923727161818
(1 row)

取整函数有 ceil() floor() trunc()

postgres=# SELECT ceil(1.5) as ceil, floor(1.5) as floor, trunc(1.5) as trunc;
 ceil | floor | trunc
------+-------+-------
    2 |     1 |     1
(1 row)

将以上两个函数结合起来就可以生成随机整数

-- 生成 1-10 之间的整数
postgres=# SELECT ceil(random() * 10) as randint;
 randint
---------
       4
(1 row)

generate_series() 用于生成一连串整数

postgres=# SELECT generate_series(1,3);
 generate_series
-----------------
               1
               2
               3
(3 rows)

postgres=# SELECT num FROM generate_series(1,3) AS series(num);
 num
-----
   1
   2
   3
(3 rows)

可用于重复查询

-- 重复 3 次打印当前时间(generate_series 步长为2)
postgres=# SELECT current_timestamp FROM generate_series(1,5,2);
       current_timestamp
-------------------------------
 2021-07-20 10:31:13.037552+08
 2021-07-20 10:31:13.037552+08
 2021-07-20 10:31:13.037552+08
(3 rows)

生成多个随机整数

10 个 1-10 之间的整数(有重复)

postgres=# SELECT ceil(random() * 10) AS num FROM generate_series(1,10);
 num
-----
   4
   2
   6
   5
   2
   6
   8
   1
   5
   8
(10 rows)

5 个 1-10 之间的整数(无重复)

postgres=# SELECT num FROM generate_series(1,10) AS series(num) ORDER BY random() LIMIT 5;
 num
-----
   6
   5
   3
   7
   8
(5 rows)