百万级数据生成

  1. test=# create table t001(id int,name varchar(10),tt date);
  2. CREATE TABLE
  3. test=# 
  4. test=# create table t002(id int);
  5. 错误:  关系 "t002" 已经存在
  6. test=# 
  7. test=# create table t002(id int);      
  8. CREATE TABLE
  9. test=# 
  10. test=# insert into t001 select generate_series(1,300000000)+300000000,'abc'||'-pgsql',now();                                
  11. INSERT 0 30000000
  12. test=# insert into t002 select id from t001 where rownum<=10;
  13. INSERT 0 10
  14. test=#

使用generate_series函数生成大量数据

1，准备工作

首先我们创建一张 people 表用于插入测试数据，具体如下：

  1. create table people
  2. (
  3.     id        integer,
  4.     name      varchar(32),
  5.     age       integer,
  6.     grade     numeric(4, 2),
  7.     birthday  date,
  8.     logintime timestamp
  9. );


2，插入数据

（1）我们执行如下命令插入 1 万条随机测试数据：

  1. insert into people
  2. select generate_series(1,10000) as id,
  3. md5(random()::text) as name,
  4. (random()*100)::integer as age,
  5. (random()*99)::numeric(4,2) as grade,
  6. now() - ((random()*1000)::integer||' day')::interval as birthday,
  7. clock_timestamp() as logintime;

Postgresql生成大量数据

顺序数

生成一批顺序值

  1. test=# select id from generate_series(1,10) t(id);
  2.  id 
  3. ----
  4.   1
  5.   2
  6.   3
  7.   4
  8.   5
  9.   6
  10.   7
  11.   8
  12.   9
  13.  10
  14. (10 行记录)
  16. test=#

随机数

生成一批随机数和一批随机整型

  1. test=# select random() from generate_series(1,10);                                                                              
  2.        random        
  3. ---------------------
  4.  0.07317435359435365
  5.  0.07021080705302651
  6.   0.7770894698524771
  7.  0.24267738539348116
  8.   0.5254066820277785
  9.   0.6802059926792872
  10.  0.17583122578569288
  11.   0.7945787905273285
  12.    0.632657725132745
  13.   0.5699608870422672
  14. (10 行记录)
  16. test=# select (random()*10)::int from generate_series(1,10);
  17.  int4 
  18. ------
  19.     2
  20.     0
  21.     5
  22.     7
  23.     8
  24.     1
  25.     9
  26.     6
  27.     9
  28.     9
  29. (10 行记录)
  31. test=#

随机字符串——md5(random()::text)

生成一批随机字符串

  1. test=# select md5(random()::text) from generate_series(1,10);                                                                   
  2.                md5                
  3. ----------------------------------
  4.  26f060e79777502c5941411ac8e1dafa
  5.  1db41aa4624660a57dcc8077e2f7776f
  6.  2c8991249f1e42dedfae0645781bfdc6
  7.  a7e51f904fb34b2e2230a980d1164139
  8.  39a6e0b8ba75b51cee4fd21b8ae1dd66
  9.  bfe0882619786790d1cc85efdd1f96ef
  10.  951b740fe2441666f2786d1ef5db1681
  11.  2e0f845c449813379c57eb6646a9e25c
  12.  2885a2e998a7ad5ff15db0414c9346cc
  13.  9e43245ea2f959c8c6ab3a75c489be96
  14. (10 行记录)
  16. test=#

重复字符串 repeat(‘abc’,10)

生成重复两次的随机字符串

  1. test=# select repeat(md5(random()::text),2) from generate_series(1,10);
  2.                               repeat                              
  3. ------------------------------------------------------------------
  4.  336171d91cdc4de6157c1ac50611fb0e336171d91cdc4de6157c1ac50611fb0e
  5.  cb19868acef2561cc3a4a1562934ea48cb19868acef2561cc3a4a1562934ea48
  6.  dd9f43b9fc1d7fba3447c3652d1ea9a1dd9f43b9fc1d7fba3447c3652d1ea9a1
  7.  b6e79961dd8f61ee3c43904c5787508cb6e79961dd8f61ee3c43904c5787508c
  8.  9f739ebff857597bca54c97a6b442b329f739ebff857597bca54c97a6b442b32
  9.  7d28c03b80de5c5314a9f563896bc3837d28c03b80de5c5314a9f563896bc383
  10.  d7fd4834da564f93ca97b413d7a51100d7fd4834da564f93ca97b413d7a51100
  11.  7027c2fa3ad75625a5659f945bf007817027c2fa3ad75625a5659f945bf00781
  12.  88d2b5c470c22ee960c80a2b8dd1816288d2b5c470c22ee960c80a2b8dd18162
  13.  568a6b2a571404c0488d58414d496da0568a6b2a571404c0488d58414d496da0
  14. (10 行记录)
  16. test=#

随机中文

  1. create or replace function gen_hanzi(int) returns text as $$    
  2. declare    
  3.   res text;    
  4. begin    
  5.   if $1 >=1 then    
  6.     select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);    
  7.     return res;    
  8.   end if;    
  9.   return null;    
  10. end;    
  11. $$ language plpgsql strict; 
  12. test=# select gen_hanzi(10) from generate_series(1,10);                                                                         
  13.       gen_hanzi       
  14. ----------------------
  15.  哗籜澉蒆貼禝躠齜偟湾
  16.  頶赏飣覤娩庴魨陁嚛薶
  17.  屾懴承蓁慖愉跐欘玈獑
  18.  泱叽桗夠抛枧荠诐氍熐
  19.  于矔骭鉗紁黖占僔淥軙
  20.  纸劅隦櫌漅灁燐椾卛獝
  21.  鎭碿碌庡淚圎焕釯媯濻
  22.  捐鉗寘詰盛砹鄕崆醎乵
  23.  萺蟇聻嚬娣仼曣草憏陗
  24.  铋烮隨蹳撒镅姴臙馼蟉
  25. (10 行记录)
  27. test=#

自定义函数

随机身份证号

  1. create or replace function gen_id(    
  2.   a date,    
  3.   b date    
  4. )     
  5. returns text as $$    
  6. select lpad((random()*99)::int::text, 2, '0') ||     
  7.        lpad((random()*99)::int::text, 2, '0') ||     
  8.        lpad((random()*99)::int::text, 2, '0') ||     
  9.        to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||     
  10.        lpad((random()*99)::int::text, 2, '0') ||     
  11.        random()::int ||     
  12.        (case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;    
  13. $$ language sql strict; 
  15. test=# select gen_id('1900-01-01','2022-01-01') from generate_series(1,10);
  16.        gen_id       
  17. --------------------
  18.  47967719110128530X
  19.  308381194710120011
  20.  326369194309187205
  21.  658641192708265206
  22.  817516198211231608
  23.  925753200707097300
  24.  813975200808184602
  25.  05255119520106170X
  26.  432005202007165514
  27.  571443199003016713
  28. (10 行记录)
  30. test=#

学习参考

https://blog.csdn.net/pg_hgdb/article/details/79227924