使用Postgresql 实现快速插入测试数据

1.创建常规的企业信息表

create table t_centerprises(
 objectid bigint not null, /*唯一编号(6位行政区号+6位sn)*/
 divid uuid not null, /*行政区唯一代码*/
 name text not null, /*企业名称*/
 address text not null, /*企业地址*/
 post text, /*企业邮编*/
 contacts text, /*联系人*/
 tel text, /*联系电话*/
 fax text, /*传真*/
 describe text, /*企业备注*/
 date timestamp default now() not null, /*创建日期*/
 constraint pk_centerprisess_objectid primary key (objectid),
 constraint fk_centerprises_divid foreign key(divid) references ts_divisions(objectid) on delete cascade
);
create index idx_centerprises_divid on t_centerprises(divid);

2.需要使用的函数

/*转换16进制到字符*/
drop function if exists hex_to_string(text);
create or replace function hex_to_string( text)
 returns text as
$$
 declare
 result text;
 begin
 execute 'select U&''\' || $1 || '''' INTO result;
 return result;
 end;
$$ language plpgsql;

/*随机生成汉字
 汉字范围U+4E00..U+9FA5
*/
drop function if exists gen_random_zh(int,int);
create or replace function gen_random_zh(imin int,imax int)
 returns text as
$$
 declare
 vlen integer;
 result text;
 begin
 result := '';
 vlen = floor(random()*(imax-imin)+imin);
 for i in 1..vlen loop
  result := result || hex_to_string(to_hex(floor(random()*(42191-19968)+19968)::integer));
 end loop;
 return result;
 end;
$$ language plpgsql;

3.常规测试数据插入(5000000条)

insert into t_centerprises(objectid,divid,name,address,post,contacts,tel,fax,describe)
  select (vdivid|| lpad(id::text,6,'0'))::bigint as objectid,'110101',
  gen_random_zh(5,25) as name,gen_random_zh(10,50) as address,
  floor(random()*(699999-600000)+600000) as post,gen_random_zh(2,8) as contacts,
  floor(random()*(69999999-60000000)+60000000) as tel,floor(random()*(69999999-60000000)+60000000) as fax,
  gen_random_zh(32,128) as describe
 from generate_series(1,5000000) as id;

在普通pc机上插入,大概完成时间约8小时,过程不可监控,并且cpu/内存占用率高,磁盘基本满负荷动作,读写率基本上都是100%.

4.改进后的方法, 插入(10000000条)

do $$
 declare vStart bigint;
 declare vEnd bigint;
 declare MAXVALE bigint;
 declare INTERVAL bigint;
 declare vprovince integer;
 declare vprefecture integer;
 declare vcounty integer;
 declare vdivid text;
 declare vdividex uuid;
begin
 vprovince := 10;vprefecture := 1;vcounty := 1;

 MAXVALE := 1000000;
 INTERVAL := 1000; vStart := 1 ;vEnd := INTERVAL;
 vdivid := (lpad(vprovince::text,2,'0') || lpad(vprefecture::text,2,'0') || lpad(vcounty::text,2,'0'))::text;
 vdividex := (select objectid from ts_divisions where province=vprovince and prefecture=vprefecture and county=vcounty);
 loop
 insert into t_centerprises(objectid,divid,name,address,post,contacts,tel,fax,describe)
  select (vdivid|| lpad(id::text,6,'0'))::bigint as objectid,vdividex as divid,
  gen_random_zh(5,25) as name,gen_random_zh(10,50) as address,
  floor(random()*(699999-600000)+600000) as post,gen_random_zh(2,8) as contacts,
  floor(random()*(69999999-60000000)+60000000) as tel,floor(random()*(69999999-60000000)+60000000) as fax,
  gen_random_zh(32,128) as describe
 from generate_series(vStart,vEnd) as id;

 raise notice '%', vEnd;
 vStart := vEnd + 1; vEnd := vEnd + INTERVAL;
 if( vEnd > MAXVALE ) then
  return;
 elsif(vEnd = MAXVALE) then
  vEnd := vEnd - 1;
 end if;
 end loop;
end$$;

因为运算原因, cpu/内存占用率仍然很高, 硬盘负荷较小,读写率也比较低,大概完成时间约1.5小时.

补充:postgreSQL数据库 向表中快速插入1000000条数据

不用创建函数,直接向表中快速插入1000000条数据

create table tbl_test (id int, info text, c_time timestamp);
insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp();
select count(id) from tbl_test; --查看个数据条数

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • 浅谈PostgreSQL消耗的内存计算方法

    wal_buffers默认值为-1,此时wal_buffers使用的是shared_buffers,wal_buffers大小为shared_buffers的1/32 autovacuum_work_mem默认值为-1,此时使用maintenance_work_mem的值 1 不使用wal_buffers.autovacuum_work_mem 计算公式为: max_connections*work_mem + max_connections*temp_buffers +shared_buffe

  • PostgreSQL的generate_series()函数的用法说明

    我就废话不多说了,大家还是直接看代码吧~ SELECT generate_series(1,12) AS month; SELECT generate_series(1,12,1) AS month; SELECT generate_series('2020-01-01'::DATE,'2020-12-31'::DATE,'1 month'::INTERVAL) AS first_of_month; 补充:PostgreSQL使用generate_series函数 填充数据(插入数据) sele

  • PostgreSQL查看版本信息的操作

    1.查看客户端版本 psql --version 2.查看服务器端版本 2.1 查看详细信息 select version(); 2.2 查看版本信息 show server_version; 2.2 查看数字版本信息包括小版号 SHOW server_version_num; 或 SELECT current_setting('server_version_num'); 3.注意事项 SELECT current_setting('server_version_num');返回类型为text,

  • 查看postgresql系统信息的常用命令操作

    1.查看当前数据库实例版本. postgres=# select version(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Ha

  • 快速解决PostgreSQL中的Permission denied问题

    想开始学习SQL和Excel那本书,觉得自己亲手去输入才是正道.发现程序后续会用到窗口函数,可是我的mysql没有窗口函数,这本书所提供的数据脚本分别是MS SQL Sever和PostreSQL. 上午我先安装的sql sever,可是由于比较大且在安装时出现了一些小的问题(安装缓慢,服务启动不了).无奈选择了PostreSQL,体积小,安装顺利. 导入数据比较特别,先建一个表,然后把同名txt导入进去.一定要用unix方式的路径. copy这个语句先前在mysql上没有遇到过.学习下....

  • postgresql 启动与停止操作

    启动和停止数据库服务器 service 方式 service postgresql-10 start service postgresql-10 stop service postgresql-10 status pg_ctl 方式 pg_ctl start -D [ data 所在路径 ] pg_ctl stop -D [ data 所在路径 ] 三种形式:-m 指定模式 smart 模式:会等待活动的事务提交结束,并等待客户端主动断开连接之后关闭数据库服务 fast 模式:会回滚所有的活动的

  • 查询PostgreSQL占多大内存的操作

    我就废话不多说了,大家还是直接看代码吧~ select pg_size_pretty(pg_relation_size('cuiyonghua.top_iqiyi_info')); select pg_size_pretty(pg_relation_size('cuiyonghua.top_mgtv_info')); select pg_size_pretty(pg_relation_size('cuiyonghua.top_tencent_info')); select pg_size_pre

  • 基于PostgreSQL 权限解读

    1 public权限解读 用户默认情况下具有public权限,public默认具有创建和使用schema的权限,因此意味着可以在schema中创建对象(包括表).列出schema中的对象,并在其权限允许时访问它们. 所以创建数据库或schema完成后做的第一件事是 1.1 创建数据库完成后 --回收schema的public权限 --revoke all on schema public from public; --正常情况使用这个 revoke all on schema public,ti

  • 使用Postgresql 实现快速插入测试数据

    1.创建常规的企业信息表 create table t_centerprises( objectid bigint not null, /*唯一编号(6位行政区号+6位sn)*/ divid uuid not null, /*行政区唯一代码*/ name text not null, /*企业名称*/ address text not null, /*企业地址*/ post text, /*企业邮编*/ contacts text, /*联系人*/ tel text, /*联系电话*/ fax

  • postgreSQL数据库 实现向表中快速插入1000000条数据

    不用创建函数,直接向表中快速插入1000000条数据 create table tbl_test (id int, info text, c_time timestamp); insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp(); select count(id) from tbl_test; --查看个数据条数 补充:postgreSQL 批量插入10000条数据

  • MySQL快速插入一亿测试数据

    目录 1.建表 1.1 建立测试表 t_user 1.2 创建临时表 2.生成数据 2.1 用 python生成 [一亿] 记录的数据文件(这个确实稍微花点时间) 2.2 将生成的文件导入到临时表tmp_table中 3.以临时表为基础数据,插入数据到t_user中 4.参考 1.建表 1.1 建立测试表 t_user CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT

  • 使用python读取csv文件快速插入数据库的实例

    如下所示: # -*- coding:utf-8 -*- # auth:ckf # date:20170703 import pandas as pd import cStringIO import warnings from sqlalchemy import create_engine import sys reload(sys) sys.setdefaultencoding('utf8') warnings.filterwarnings('ignore') engine = create_

  • Mysql快速插入千万条数据的实战教程

    一.创建数据库 二.创建表 1.创建 dept表 CREATE TABLE `dept` ( `id` int(11) NOT NULL, `deptno` mediumint(9) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(13) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2.创建emp表 CREATE TABLE

  • PostgreSQL数据库事务插入删除及更新操作示例

    目录 INSERT DELETE UPDATE 事务 INSERT 使用INSERT语句可以向表中插入数据. 创建一个表: CREATE TABLE ProductIns (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_d

  • MySQL存储过程的创建使用以及实现数据快速插入

    目录 一,存储过程介绍 二,存储过程的优缺点 三,存储过程的创建与调用 3.1,存储过程中的常用语法及参数 3.2,存储过程的使用 四,存储过程中的变量及使用细则 4.1,变量定义 4.2,变量赋值 4.3,用户变量的使用 4.4,存储过程的一些常用查看命令 五,常用的存储过程的控制语句 5.1,条件语句 5.1.1,if-then-else 5.1.2,case语句 5.2,循环语句 5.2.1,while ……end while语句 5.2.2,repeat……end repeat语句 5.

  • C#/.Net 中快速批量给SQLite数据库插入测试数据

    使用transaction: var stopwatch = new Stopwatch(); using (var cmd = new SQLiteCommand(db_con)) using (var transaction = db_con.BeginTransaction()) { stopwatch.Reset(); stopwatch.Start(); foreach (var item in sorted) { sql = string.Format("insert into db

  • PostgreSQL实现批量插入、更新与合并操作的方法

    前言 就在 2019 年 1 月份微软收购了 PostgreSQL 数据库的初创公司 CitusData, 在云数据库方面可以增强与 AWS 的竟争.AWS 的 RDS 两大开源数据库就是 MySQL(Aurora 和 MariaDB 是它的变种) 和 PostgreSQL. 而 PostgreSQL 跳出了普通关系型数据库的类型约束,它灵活的支持 JSON, JSONB, XML, 数组等类型.比如说字段类型可以是各种形式的数组,一维或多维. create table t1( address

  • MySql 快速插入千万级大数据的方法示例

    在数据分析领域,数据库是我们的好帮手.不仅可以接受我们的查询时间,还可以在这基础上做进一步分析.所以,我们必然要在数据库插入数据.在实际应用中,我们经常遇到千万级,甚至更大的数据量.如果没有一个快速的插入方法,则会事倍功半,花费大量的时间. 在参加阿里的天池大数据算法竞赛中(流行音乐趋势预测),我遇到了这样的问题,在没有优化数据库查询及插入之前,我花了不少冤枉时间,没有优化之前,1500万条数据,光插入操作就花费了不可思议的12个小时以上(使用最基本的逐条插入).这也促使我思考怎样优化数据库插入

随机推荐