postgresql生成测试数据

postgresql_test.md

核心方法

generate_series(start,stop);生成序列
(random()*(2*10^9))::integer;生成随机数
substr('abcdefghijklmnopqrstuvwxyz',1,(random()*26)::integer);生成随机字符串

例如有一下的Test表

               Table "public.test"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+---------------------------------------------------
 id         | integer                     | not null default nextval('test_id_seq'::regclass)
 name       | character varying           |
 no         | integer                     |

举个例子

SELECT generate_series(1,5) as i;

 i
---
 1
 2
 3
 4
 5
(5 rows)

SELECT 'text:' || i from generate_series(1,5) as i;

 ?column?
----------
 text:1
 text:2
 text:3
 text:4
 text:5
(10 rows)

select 'txt:' || i, repeat('1',i) from generate_series(1,5) as i;
 ?column? |   repeat
----------+------------
 txt:1    | 1
 txt:2    | 11
 txt:3    | 111
 txt:4    | 1111
 txt:5    | 11111
(10 rows)

有了以上的基础，就可以随便生成数据了

insert into book(name,no) select 'txt:' || i, (random()*(2*10^9))::integer from generate_series(1,5) as i;

create table user_ini(
  id INTEGER ,
  user_id INTEGER, 
  user_name text,
  create_time timestamp with time zone default clock_timestamp()
);

insert into user_ini(id,user_id,user_name) select r,round(random()*2000000), r || '_francs' from generate_series(1,2000000) as r;

create table tbl_user_jsonb(
  id serial, 
  user_info jsonb
);

insert into tbl_user_jsonb(user_info) select row_to_json(user_ini)::jsonb from user_ini;

insert into jsonb_test(data) select json_build_object('age',r,'sex',0,'name','bin'||r) from generate_series(1,100) as r;