Postgresql generate_series 生成大量测试数据

生成数据方法 generate_series 介绍

generate_series ( start integer, stop integer [, step integer ] ) → setof integer

generate_series ( start bigint, stop bigint [, step bigint ] ) → setof bigint

generate_series ( start numeric, stop numeric [, step numeric ] ) → setof numeric

Generates a series of values from start to stop, with a step size of step. step defaults to 1.

generate_series ( start timestamp, stop timestamp, step interval ) → setof timestamp

generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval ) → setof timestamp with time zone

Generates a series of values from start to stop, with a step size of step.

generate_series 使用:

SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)

SELECT * FROM generate_series(5,1,-2);
 generate_series
-----------------
               5
               3
               1
(3 rows)

SELECT * FROM generate_series(4,3);
 generate_series
-----------------
(0 rows)

SELECT generate_series(1.1, 4, 1.3);
 generate_series
-----------------
             1.1
             2.4
             3.7
(3 rows)

-- this example relies on the date-plus-integer operator:
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-04 12:00', '10 hours');
   generate_series
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
 2008-03-02 16:00:00
 2008-03-03 02:00:00
 2008-03-03 12:00:00
 2008-03-03 22:00:00
 2008-03-04 08:00:00
(9 rows)

插入数据

新增一个测试表:

CREATE TABLE tests_table (
 user_id    bigint,
 building_id bigint,
 name  varchar(100),
 created_at   timestamp
);

SELECT n, floor(random() * 100 + 1)::int, n || '_abc', now() FROM generate_series(1,10) n;

 n  | floor | ?column? |              now
----+-------+----------+-------------------------------
  1 |     9 | 1_abc    | 2022-01-07 17:57:29.709105+08
  2 |    21 | 2_abc    | 2022-01-07 17:57:29.709105+08
  3 |    64 | 3_abc    | 2022-01-07 17:57:29.709105+08
  4 |    55 | 4_abc    | 2022-01-07 17:57:29.709105+08
  5 |   100 | 5_abc    | 2022-01-07 17:57:29.709105+08
  6 |    86 | 6_abc    | 2022-01-07 17:57:29.709105+08
  7 |    78 | 7_abc    | 2022-01-07 17:57:29.709105+08
  8 |    83 | 8_abc    | 2022-01-07 17:57:29.709105+08
  9 |    65 | 9_abc    | 2022-01-07 17:57:29.709105+08
 10 |    84 | 10_abc   | 2022-01-07 17:57:29.709105+08
(10 rows)

插入10条:

INSERT INTO tests_table(user_id, building_id, name, created_at) SELECT n, floor(random() * 100 + 1)::int, n || '_abc', now() FROM generate_series(1,10) n;

select * from tests_table;

 user_id | building_id |  name  |         created_at
---------+-------------+--------+----------------------------
       1 |          86 | 1_abc  | 2022-01-07 17:58:52.050247
       2 |          66 | 2_abc  | 2022-01-07 17:58:52.050247
       3 |          27 | 3_abc  | 2022-01-07 17:58:52.050247
       4 |          47 | 4_abc  | 2022-01-07 17:58:52.050247
       5 |          82 | 5_abc  | 2022-01-07 17:58:52.050247
       6 |          33 | 6_abc  | 2022-01-07 17:58:52.050247
       7 |          24 | 7_abc  | 2022-01-07 17:58:52.050247
       8 |          49 | 8_abc  | 2022-01-07 17:58:52.050247
       9 |          21 | 9_abc  | 2022-01-07 17:58:52.050247
      10 |          35 | 10_abc | 2022-01-07 17:58:52.050247
(10 rows)