顺序值
postgres@[local]:5432=# select id from generate_series(1,5) t(id);
id
----
1
2
3
4
5
(5 rows)
generate_series 可以指定最大值,最小值,递增值。也可以生成时间等类型
随机数
select random() from generate_series(1,10);
random()生成值为双精度浮点数,范围 0 <= random() < 1
生成指定范围的整数:min+(random()*(max-min))::integer
随机字符串
方案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)
例如: select md5(random()::text),f_random_str(5) from generate_series(1,10);
重复字符串
repeat('abc', 10)
例如:
select repeat(f_random_str(5),3) from generate_series(1,10);
随机中文
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;
例如:
select gen_hanzi(10) from generate_series(1,10);
使用
create table testdata(id integer,name varchar(20),course int,grade numeric(4,2),testtime date,note text);
insert into testdata
select generate_series(1,1000000) as id,
f_random_str(3+(random()*5)::integer) as name,
(random()*100)::integer as course,
(random()*99)::numeric(4,2) as grade,
now() - ((random()*1000)::integer||' day')::interval as testtime,
gen_hanzi(3+(random()*5)::integer) as note;
select * from testdata;