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; --查看個數據條數
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- 使用postgresql 模擬批量數據插入的案例
- PostgreSQL upsert(插入更新)數據的操作詳解
- postgreSQL數據庫 實現向表中快速插入1000000條數據
- Python隨機生成數據后插入到PostgreSQL
- postgres 使用存儲過程批量插入數據的操作
