PG构建测试数据
👉 本文共约562个字，系统预计阅读时间或需3分钟。
为了测试或验证需要，通常需要快速的构建测试数据。
PostgreSQL提供了一些非常有用的功能，可以帮助用户快速的构建测试数据。
数据获取
默认函数
返回多条记录的函数。例如
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | postgres=# \df generate* List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------------------------+-----------------------------------+--------------------------------------------------------------------+------ pg_catalog | generate_series | SETOF bigint | bigint, bigint | func pg_catalog | generate_series | SETOF bigint | bigint, bigint, bigint | func pg_catalog | generate_series | SETOF integer | integer, integer | func pg_catalog | generate_series | SETOF integer | integer, integer, integer | func pg_catalog | generate_series | SETOF numeric | numeric, numeric | func pg_catalog | generate_series | SETOF numeric | numeric, numeric, numeric | func pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | func pg_catalog | generate_series | SETOF timestamp with time zone | timestamp with time zone, timestamp with time zone, interval | func pg_catalog | generate_series_int4_support | internal | internal | func pg_catalog | generate_series_int8_support | internal | internal | func pg_catalog | generate_subscripts | SETOF integer | anyarray, integer | func pg_catalog | generate_subscripts | SETOF integer | anyarray, integer, boolean | func (12 rows) postgres=# |
顺序值
1 2 | --生成1-10的顺序值 select id from generate_series(1,10) t(id); |
随机数
1 2 3 4 5 6 | --10内的随机整数 select (random()*10)::int from generate_series(1,10); --100内的随机整数 select (random()*100)::int from generate_series(1,10); --100内的随机两位小数 select (random()*100)::real(4,2) from generate_series(1,10); |
生成时间
1 2 3 4 5 6 7 8 9 10 11 12 | --间隔5秒 select generate_series(to_date('20220314','yyyymmdd'),to_date('20220315','yyyymmdd'),'5s'); --间隔5分 select generate_series(to_date('20220314','yyyymmdd'),to_date('20220315','yyyymmdd'),'5m'); --间隔5小时 select generate_series(to_date('20220314','yyyymmdd'),to_date('20220315','yyyymmdd'),'5h'); --间隔5天 select generate_series(to_date('20220214','yyyymmdd'),to_date('20220315','yyyymmdd'),'5d'); --间隔5个月 select generate_series(to_date('20200314','yyyymmdd'),to_date('20220315','yyyymmdd'),'5mon'); --间隔5年 select generate_series(to_date('20000314','yyyymmdd'),to_date('20220315','yyyymmdd'),'5y'); |
随机字符串
1 2 3 4 5 6 7 8 | --随机生成32位md5字符串10行 select md5(random()::text) from generate_series(1,10); --随机生成2个32位md5字符串连接值 select repeat(md5(random()::text),2); --随机生成2个32位md5字符串连接值10行 select repeat(md5(random()::text),2) from generate_series(1,10); --指定字符串10次以内的随机重复次数 select repeat('abcd',(random()*10)::integer); |
拼接字符串
1 2 3 | select concat('ab','cd'); select concat('ab',' ','cb'); select concat('ab',' ','cb',' ','ef'); |
自定义函数
随机整数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | --创建函数 create or replace function get_rand_int (m int ,n int) returns int as $body$ begin return cast((random() *(n - m) + m) as int); end ; $body$ language plpgsql; --随机生成10次100以内的整数 postgres=# select get_rand_int (1,100) from generate_series(1,10); get_rand_int -------------- 10 99 27 98 70 75 78 26 59 58 (10 rows) postgres=# |
随机中文
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | --创建函数 create or replace function get_ch(int) returns text as $$ declare res text; begin if $1 >=1 then select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1); return res; end if; return null; end; $$ language plpgsql strict; --随机生成5次10字 postgres=# select get_ch(10) from generate_series(1,5); get_ch ---------------------- 氷碢话锢訄奃笛嗮丵繁 舠竫彽蛧岰钕廭陑鑌牊 苼汣誣貲罅溞翡亣鰀嗪 惺椘叡瀨甚尦畑秨暌遐 搝奓歒濝萲骃査僮焸鷬 (5 rows) postgres=# |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
随机身份证号
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | --创建函数 create or replace function gen_id( a date, b date ) returns text as $$ select lpad((random()*99)::int::text, 2, '0') || lpad((random()*99)::int::text, 2, '0') || lpad((random()*99)::int::text, 2, '0') || to_char(a + (random()*(b-a))::int, 'yyyymmdd') || lpad((random()*99)::int::text, 2, '0') || random()::int || (case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ; $$ language sql strict; --随机生成10个身份证号码 postgres=# select gen_id('2000-01-01', '2022-03-14') from generate_series(1,10); gen_id -------------------- 294632201101137115 465691202108174108 389754200806257003 258027202001265704 050494200406248903 447255201704061103 745762201910221609 423167201604026611 36750620151220570X 933073202102129412 (10 rows) postgres=# |
随机电话号码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | --创建函数 create or replace function get_tel() returns varchar(300) as $body$ declare startlength int default 11 ; endlength int default 11 ; first_no varchar(100) default '1'; chars_str varchar(100) default '0123456789'; return_str varchar(300) default substring('3578' , cast((1 + random()*3 ) as int),1); i int ; end1 int; begin end1 :=cast((random()*(endlength - startlength)) as int)+startlength; for i in 1 .. end1-2 loop return_str = concat(return_str,substring(chars_str , cast((1 + random()*9 ) as int),1)); end loop; return concat(first_no,return_str); end; $body$ language 'plpgsql' ; --随机生成10个电话号码 postgres=# select get_tel() from generate_series(1,10); get_tel ------------- 15628243240 18126071439 17961852417 18458602282 17257123493 15458615966 17477818545 13928781112 18741769811 17450810283 (10 rows) postgres=# |
随机日期
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | --创建函数 create or replace function get_rand_date(start_date date,end_date date) returns date as $body$ declare interval_days integer ; random_days integer ; random_date date ; begin interval_days := end_date - start_date ; random_days := get_rand_int (0, interval_days) ; random_date := start_date + random_days ; return random_date; end ; $body$ language plpgsql; --生成指定时间段的随机日期 postgres=# select get_rand_date('2022-01-01', '2022-03-14') from generate_series(1,10); get_rand_date --------------- 2022-01-22 2022-01-18 2022-03-08 2022-01-24 2022-02-13 2022-02-15 2022-02-19 2022-01-05 2022-03-01 2022-01-24 (10 rows) postgres=# |
随机时间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | --创建函数 create or replace function get_rand_datetime(start_date date, end_date date) returns timestamp as $body$ declare interval_days integer; random_seconds integer; random_dates integer; random_date date; random_time time; begin interval_days := end_date - start_date; random_dates:= trunc(random()*interval_days); random_date := start_date + random_dates; random_seconds:= trunc(random()*3600*24); random_time:=' 00:00:00'::time+(random_seconds || ' second')::interval; return random_date +random_time; end; $body$ language plpgsql; --生成指定时间段的随机时间 postgres=# select get_rand_datetime('2022/01/01', '2022/03/14') from generate_series(1,10); get_rand_datetime --------------------- 2022-03-11 22:05:16 2022-01-02 04:39:12 2022-01-28 13:21:57 2022-02-21 14:28:11 2022-01-31 09:38:48 2022-02-21 14:21:05 2022-02-04 05:02:28 2022-01-22 18:21:30 2022-01-15 17:04:37 2022-02-14 21:29:15 (10 rows) postgres=# |
参考：
https://github.com/digoal/blog/blob/master/201711/20171121_01.md
