PostgreSQL批量生成测试数据

最新推荐文章于 2024-07-18 07:45:44 发布

msdnchina

最新推荐文章于 2024-07-18 07:45:44 发布

阅读量1.3k

点赞数

分类专栏： PostgreSQL 文章标签： PostgreSQL 批量

原文链接：https://www.jianshu.com/p/d465a4c748e8

版权

PostgreSQL 专栏收录该内容

214 篇文章 1 订阅

订阅专栏

https://www.jianshu.com/p/d465a4c748e8

顺序值

atlasdb=# select id from generate_series(1,10) t(id);  
 id   
----  
  1  
  2  
  3  
  4  
  5  
  6  
  7  
  8  
  9  
 10  
(10 rows)

generate_series 可以指定最大值，最小值，递增值。也可以生成时间等类型

atlasdb=# \df generate_series
                                                              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
(8 rows)

随机数

atlasdb=# select random()  from generate_series(1,10);
       random       
--------------------
  0.799458883237094
  0.047981650128836
  0.211289744824171
  0.909943440463394
  0.795472401659936
  0.780012475326657
  0.568553290329874
  0.434846977703273
  0.192123426124454
  0.605796394404024
(10 rows)

random()生成值为双精度浮点数，范围 0 <= random() < 1

生成指定范围的整数：min+(random()*(max-min))::integer

atlasdb=# select 5+(random()*(7-5))::integer from generate_series(1,10);  
 ?column? 
----------
        5
        6
        5
        5
        7
        6
        6
        6
        6
        7
(10 rows)

随机字符串

方案1：

# 生成指定长度的字符串
create or replace function f_random_str(length INTEGER) 
returns character varying AS $$
DECLARE
    result varchar(50);
BEGIN
    SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
    FROM generate_series(1,length)), '') INTO result;
    
    return result;
END;
$$ LANGUAGE plpgsql;

方案2：利用md5函数

md5(random()::text)

例如：


atlasdb=# select md5(random()::text),f_random_str(5) from generate_series(1,10);
               md5                | f_random_str 
----------------------------------+--------------
 a56bcf9d7f81780019042a67064a9c0e | NDOGV
 dec8db00b3059650f7ab7f22d004069e | WQLBH
 d20997f727a1d567ee0705d54b305474 | KOWVJ
 0056c14bcd1455d6f53515cddcbfe1fa | NXRBH
 04fb3acae29234381b9b96611af8ad94 | QVBBN
 b933eb17c3deb2a4391873ca95cc9288 | QBJFH
 2d8361fd230cd34d0038c4340553d27d | BDVME
 925004f93aa3541b43e8168aff77fc62 | TOQQK
 a7f4a7e36792310f561bf861d09cc80d | MXYEZ
 d3d4f6b1777d3015be4fd9f38876da4f | EQCFR
(10 rows)

重复字符串

repeat('abc', 10)

例如：

atlasdb=# select repeat(f_random_str(5),3) from generate_series(1,10);
     repeat      
-----------------
 XDODDXDODDXDODD
 UHTWLUHTWLUHTWL
 LMWTDLMWTDLMWTD
 RHAQVRHAQVRHAQV
 BLWGJBLWGJBLWGJ
 RBXDFRBXDFRBXDF
 NBJCENBJCENBJCE
 LVKFSLVKFSLVKFS
 WQFTKWQFTKWQFTK
 ILRICILRICILRIC
(10 rows)

随机中文

create or replace function gen_hanzi(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;

例如：

atlasdb=# select gen_hanzi(10) from generate_series(1,10);
      gen_hanzi       
----------------------
 狶用裻羾愪諮夛鞳店蓝
 譣鞇县跆硠銚巖眐韎盍
 訂锕短峂销淼椚煟閴篁
 尰椅元霫髃摮艳吹屼錊
 謖駟諬郖蠀控菋韖買篴
 滾眔煤飫藖暿杂佌訞嵑
 紌顎硖聏返獴隽犢禭厔
 昈攟齳兏噐蟍曶谚翾合
 桪佗紜诎驂捎勨懟撙輇
 焯鎥螰鮅瑐矟潕歨鲁鬂
(10 rows)

使用

atlasdb=# create table testdata(id integer,name varchar(20),course int,grade numeric(4,2),testtime date,note text);
CREATE TABLE
atlasdb=# insert into testdata 
atlasdb-# select generate_series(1,100) as id,
atlasdb-# f_random_str(3+(random()*5)::integer) as name,
atlasdb-# (random()*100)::integer as course,
atlasdb-# (random()*99)::numeric(4,2) as grade,
atlasdb-# now() - ((random()*1000)::integer||' day')::interval as testtime,
atlasdb-# gen_hanzi(3+(random()*5)::integer) as note; 
INSERT 0 100
atlasdb=# select * from testdata;
 id  |   name   | course | grade |  testtime  |       note       
-----+----------+--------+-------+------------+------------------
   1 | GGLHI    |     13 | 59.61 | 2019-03-07 | 蔱澪豇抨寓蛤矶
   2 | YUBJ     |     64 | 72.51 | 2018-07-18 | 戇覃璢廣
   3 | XMWITG   |     46 | 35.76 | 2018-07-24 | 儋襫鵆斳
   4 | RUDPPEY  |     24 | 87.59 | 2018-08-08 | 增偗酚榁蜷
   5 | UPBOVNX  |     14 | 71.70 | 2016-10-20 | 蛃戵貋殤刹雟鵇
   6 | BYWXGG   |      6 | 27.58 | 2019-04-03 | 鷵蹦帅紽鋖
   7 | KJROCQK  |     78 | 87.08 | 2018-12-05 | 騊叻圵紲
   8 | CMBI     |     70 | 10.63 | 2017-10-21 | 眦粚笈蜪娲渹猨
   9 | TDCVTN   |     62 | 63.53 | 2017-07-09 | 糋貵锗婗陙騺耮
  10 | GIBVCOKQ |     25 | 55.26 | 2019-05-11 | 圣越量彁聄繣褺
  11 | GLZU     |     19 | 85.19 | 2016-10-18 | 軖糏京
  12 | WBW      |     97 | 58.88 | 2018-08-21 | 銙錈肮稦鰫溓
  13 | EAYWG    |     20 | 31.12 | 2018-10-11 | 眉嗦鲽
  14 | WNPXLQ   |     81 | 38.91 | 2018-10-18 | 黈厰髿
  15 | PXAZCB   |     95 | 95.38 | 2018-09-07 | 撮睛娴趶
  16 | DJMQYL   |      8 | 57.14 | 2018-10-11 | 进椷玖開撐
  17 | MEC      |     47 | 24.36 | 2019-02-21 | 弛沃箂痉闞
  18 | HMVQYMO  |     40 | 54.48 | 2019-01-10 | 侼鮪鵤畓醐扏
  19 | ILQURS   |     20 | 87.24 | 2019-02-15 | 氖黃玕臰璘擳捑
  20 | TVVIY    |     49 | 32.18 | 2016-11-22 | 远譣衉歋卤
  21 | ZVUS     |      4 | 68.55 | 2017-02-19 | 勺酃挖蛫昵稉鰼
  22 | HUPPTCX  |     68 | 55.27 | 2017-06-06 | 携唛眀澞呕
  23 | GUKXQ    |     19 | 96.67 | 2018-02-27 | 蓾诪黢膕眿
  24 | KDWE     |     20 | 79.37 | 2017-01-22 | 祄晦偧聞轥爅蚴
  25 | RMOO     |     14 | 73.23 | 2017-11-25 | 嶘悾欐峕鑓鑏
  26 | HYJLE    |     15 | 31.78 | 2016-11-23 | 肚鮿旼灛渟麰
  27 | BMDQQV   |     19 | 21.73 | 2019-04-15 | 系惣攲熥蛝
  28 | KBBOFJ   |     49 | 85.68 | 2016-10-14 | 嬀浖鉢娋鰑
  29 | QCNGXSM  |     98 | 14.68 | 2019-04-15 | 煍痂顧齘
  30 | YALEJYA  |     32 | 39.14 | 2018-12-20 | 愄枡葈塵魜詫菫
  31 | RKRQ     |     53 | 72.10 | 2017-01-13 | 従跷鲝嚟訡鸢簠閳
  32 | NWRXB    |     38 | 14.30 | 2018-06-03 | 摀枒跑
  33 | NLIFDVXZ |     82 | 13.62 | 2017-04-27 | 懛眸豞迻浅囁桻
  34 | TGIE     |     38 | 67.90 | 2018-11-22 | 龑鶣緑砐焭韙
  35 | OTQNOTH  |     36 |  1.23 | 2017-03-24 | 郿崖惔姕
  36 | AKTET    |     46 | 34.83 | 2018-04-16 | 梸龚鵸该韎
  37 | IRIVGC   |     13 | 59.21 | 2019-02-28 | 裧飔均鮻匄籆鰷爚
  38 | DFUMP    |     26 | 78.73 | 2017-09-26 | 笭硾颐鏐
  39 | GRLI     |     81 |  1.83 | 2018-04-23 | 詪桩鏏蚀浭灰茓
  40 | TTBOGQU  |      3 | 26.11 | 2019-04-16 | 踇鲍熾鳡嵼堌
  41 | NXKXR    |     15 | 22.88 | 2017-12-19 | 聊鯞症爬衬抯璥
  42 | MDCN     |     38 | 14.21 | 2019-02-24 | 嚡秞壿昜
  43 | NUPXSH   |      7 | 92.53 | 2017-03-21 | 竵误湒鼡璶茂琡證
  44 | PVQY     |     99 | 74.56 | 2019-01-15 | 斷摸淔
  45 | UFOSWUUV |     62 | 69.62 | 2018-05-25 | 嘗氓鏻謘鈴
  46 | WLLNKL   |     29 | 53.00 | 2017-12-14 | 逮馣禅羐圩咄
  47 | AWDVN    |     83 | 19.84 | 2016-12-09 | 籯豣蔟滾汜箼醆酛
  48 | GHK      |     76 | 79.55 | 2016-10-04 | 覗睋屉鋁総眺
  49 | MQRALWX  |     38 | 43.41 | 2017-07-24 | 鋌刽肐
  50 | WSWEDQ   |     95 |  8.04 | 2018-10-30 | 緭添硦尗霶殤
  51 | NBRZXP   |     37 | 33.73 | 2018-09-14 | 峸枭净鎥嶟
  52 | TIWKHYP  |     96 | 53.54 | 2019-06-02 | 蠾靟銿汉炰
  53 | BKTQTDW  |     20 | 29.77 | 2018-11-19 | 媌涷伀靗
  54 | WHAVXYJ  |     90 | 44.03 | 2019-03-03 | 敩珚悕嬨盫自骑
  55 | KBAP     |     36 | 19.90 | 2017-01-23 | 縟镥潌枎诼蜞
  56 | PPHYN    |     73 |  6.90 | 2018-07-31 | 禋秂屽
  57 | EDE      |     58 | 16.87 | 2018-12-26 | 磷歰募僫
  58 | XLHREPHT |     87 | 26.55 | 2018-09-27 | 榖管耆锡喾躂
  59 | HXCVCGBP |     58 | 10.61 | 2017-09-04 | 兞咣軝觺恔沽
  60 | YGH      |     22 | 83.14 | 2017-09-10 | 狱眐镐懎潝奰娔
  61 | ZGRBV    |     27 | 14.33 | 2018-02-26 | 崰穹绠餪賍鶝魃
  62 | FGYBWSN  |     37 | 60.42 | 2017-05-12 | 譅鞔抲詷堲鬞越
  63 | FXKBDYPB |     73 | 58.91 | 2016-10-01 | 遬徤炪锭唯
  64 | KMTD     |     25 | 51.71 | 2019-04-28 | 捶嬶牄兛圬
  65 | YNRCMI   |     10 | 21.07 | 2016-12-18 | 蕲蠑昐
  66 | PKHY     |     85 |  3.67 | 2019-02-23 | 箪奇繇
  67 | HBWKRVX  |     38 | 89.14 | 2018-04-07 | 乂臲絖咭枿旂
  68 | KWTRV    |     60 | 71.40 | 2016-11-05 | 擨呮敻噣泪桢
  69 | UAUSKREC |     70 | 78.12 | 2017-08-12 | 嚈鯁媬碿酷颧廷
  70 | NXPFFR   |     50 | 30.92 | 2019-04-01 | 撺鏈錒叜綱戋貁
  71 | IMNZGQY  |     40 | 15.27 | 2017-04-28 | 殎煙釀旃儶
  72 | NSO      |     81 | 78.04 | 2018-05-26 | 艾念孑
  73 | MXYU     |     39 | 48.53 | 2017-04-14 | 域裰停摵痮榼
  74 | XEXYFL   |     69 | 75.49 | 2018-10-05 | 夥椱垍欚皂
  75 | VLIPU    |     80 | 39.14 | 2018-03-13 | 垓瓷巜羀邳釠礢麊
  76 | MFFEYLQ  |     10 | 75.52 | 2017-04-29 | 挤圃撹藓瀪
  77 | MFHYDKL  |     33 |  1.15 | 2018-10-05 | 稔悮餱偨
  78 | DFKNV    |     49 | 29.87 | 2017-09-21 | 篇覰悡懵娶埝諁毩
  79 | RNUDV    |     83 | 35.63 | 2016-09-13 | 縐颷湃侃俺
  80 | KOLXWC   |     86 | 42.12 | 2017-03-20 | 蒲鰆庪
  81 | IPDVL    |     22 | 65.03 | 2018-09-24 | 茦肂尔簸伡嶗
  82 | PPECNA   |     14 | 35.96 | 2018-03-17 | 牳坈韥茝礖悊掄臃
  83 | RVS      |     94 | 43.71 | 2018-06-10 | 耡馌紉辸砙嶂
  84 | RGXRK    |     28 | 12.97 | 2018-06-18 | 懺拤河贏畮舶漭
  85 | IHGHSP   |     84 | 31.65 | 2017-12-24 | 堮兼缩祸蠜
  86 | LKHSNPL  |     77 | 85.98 | 2017-02-25 | 檱琺鯖限輯忂
  87 | CXSX     |     24 | 25.45 | 2018-07-19 | 智饔靻仰跬
  88 | KBCYQ    |     53 | 70.80 | 2017-12-25 | 捧閜酟徙豇
  89 | LWRJQP   |     60 | 86.94 | 2016-11-21 | 岇錬铄嵷腳徎绺蔘
  90 | OIUG     |     86 | 17.40 | 2018-01-02 | 且觜皟胾宒涺栨
  91 | AXDWVC   |      7 | 68.94 | 2016-10-22 | 梨寥鏏丛
  92 | KIHQF    |     46 | 16.69 | 2016-11-20 | 鞟炍咘厌遆
  93 | PVISS    |     17 | 79.24 | 2017-04-20 | 讯側帢馔陃帽涍
  94 | NRXS     |     12 |  9.89 | 2017-08-24 | 丣叟莽厮阥勘
  95 | SKJKOE   |     19 | 42.65 | 2016-11-18 | 腎鎱嚂醋
  96 | KJXI     |      8 |  2.69 | 2018-04-14 | 罞瀘辒捶症蘒桏
  97 | KSNU     |     27 | 68.46 | 2016-09-12 | 肒彾梞璞柿媄
  98 | SNPAPQL  |     33 | 24.84 | 2017-02-01 | 石榾邧鉁
  99 | FOAAU    |     70 |  0.92 | 2018-01-12 | 怿鈑蹾稿麔
 100 | GMDHCT   |     70 | 41.70 | 2019-05-16 | 竹禃雉毺氟泦
(100 rows)

作者：hemny
链接：https://www.jianshu.com/p/d465a4c748e8
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。