[PostgreSQL] 使用 generate_series 生成测试数据

1. 产生连续数据 (可作为自查询语句使用)
=# select generate_series(1, 10);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

=# select generate_series(-1, 1);
 generate_series
-----------------
              -1
               0
               1
(3 rows)

=# select generate_series(1, 10, 2);
 generate_series
-----------------
               1
               3
               5
               7
               9
(5 rows)

2. 一天内每小时间隔的数据
=# select h from generate_series('2010-01-01'::timestamp, '2010-01-02'::timestamp, '1 hours') as h;
          h        
---------------------
 2010-01-01 00:00:00
 2010-01-01 01:00:00
 2010-01-01 02:00:00
 :
 2010-01-02 00:00:00

3. 一年间每周一行数据
=# select d from generate_series('2010-01-01'::timestamp, '2010-02-01'::timestamp, '7 days') as d; (8.4 之后)
=# select '2010-01-01'::date + (d - 1 || ' days')::interval from generate_series(1, 365, 7) as d; (8.4 之前)
          d         
---------------------
 2010-01-01 00:00:00
 2010-01-08 00:00:00
 2010-01-15 00:00:00
 2010-01-22 00:00:00
 2010-01-29 00:00:00