pgsql插100W数据

最新推荐文章于 2024-07-01 07:30:00 发布

fffefw

最新推荐文章于 2024-07-01 07:30:00 发布

阅读量1.3k

点赞数 1

分类专栏：杂文章标签： postgresql

本文链接：https://blog.youkuaiyun.com/qq_40642465/article/details/119920149

版权

杂专栏收录该内容

23 篇文章

订阅专栏

这篇博客介绍了如何在PostgreSQL中创建一个随机模块ID的函数，并使用这个函数来填充一个包含100万条数据的`operation_100w`表。示例中展示了建表语句、插入语句，以及一条具体的插入记录，涉及用户ID、用户名、角色、源、IP、模块ID等多个字段。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以下命令直接复制粘贴即可运行，在pgsql插入100w条数

建随机函数
Create or replace function random_module_id() returns text as
$$
declare
  chars text[] := '{1000,10001000,10001001,100010011000,100010011001,100010011002,100010011003,100010011004,100010011005,100010011006,100010011007,10001002,100010021000,100010021001,100010021002,100010021003,100010021004,100010021005,10001003,100010031000,100010031001,100010031002,100010031003,100010031004,100010031005,100010031006,100010031007,10001004,100010041000,100010041001,100010041002,10001005,100010051000,100010051001,1001,1002,10021000,10021001,10021002,10021003,1003,10031000,100310001000,100310001001,10031001,10031002,100310021000,100310021001,10031003,1004,10041000,10041001,1005,10051000,10051001,100510011000,1005100110001000,1005100110001001,1005100110001002,1005100110001003,100510011001,10051002,100510021000,100510021001,100510021002,100510021003,10051003,1006,1007,10071000,1008,10081000,100810001000,100810001001,1008100010011000,1008100010011001,1008100010011002,100810001002,1008100010021000,1008100010021001,100810001003,1008100010031000,1008100010031001,100810001004,1008100010041000,1008100010041001,100810001005,1008100010051000,1008100010051001,100810001006,1008100010061000,1008100010061001,100810001007,1008100010071000,1008100010071001,100810001008,1008100010081000,1008100010081001,100810001009,100810001010,1008100010101000,1008100010101000,100810001011}';
  result text := '';
begin
  result := result || chars[1+random()*(array_length(chars, 1)-1)];
  return result;
end;
$$ language plpgsql;

建表
create table operation_100w (id integer, user_id character varying,  user_name text, role text,source text,ip text,module_id text,module_name text,action text,result text,operation_title text,operation_detail text,operation_time timestamp without time zone,trace_id text,created_at timestamp without time zone,parent_id text);

插入
insert into operation_100w select generate_series(1,1000000),'11','张三','测试','API','127.0.0.1',random_module_id(),'规则管理','QUERY','SUCC','{"data":{"username":"张三"},"te
mplate":"{{username}}使用admin账号查询了规则"}','      ','2021-08-12 16:00:00','  ','2021-08-23 15:55:45.108761','1005';