postgresql批量插入数据脚本_postgresql – 批量插入数亿条记录

将2.37亿条记录插入到具有规则的表中(用于跨子表分发数据)的最快方法是什么？

我试过或考虑过：

>插入语句.

>事务性插入(BEGIN和COMMIT).

> COPY FROM命令.

> http://pgbulkload.projects.postgresql.org/

插入太慢(四天)而COPY FROM忽略规则(并有其他问题).

示例数据：

station_id,taken,amount,category_id,flag

1,'1984-07-1',0,4,

1,'1984-07-2',0,4,

1,'1984-07-3',0,4,

1,'1984-07-4',0,4,T

表结构(包含一个规则)：

CREATE TABLE climate.measurement

(

id bigserial NOT NULL,

station_id integer NOT NULL,

taken date NOT NULL,

amount numeric(8,2) NOT NULL,

category_id smallint NOT NULL,

flag character varying(1) NOT NULL DEFAULT ' '::character varying

)

WITH (

OIDS=FALSE

);

ALTER TABLE climate.measurement OWNER TO postgres;

CREATE OR REPLACE RULE i_measurement_01_001 AS

ON INSERT TO climate.measurement

WHERE date_part('month'::text, new.taken)::integer = 1 AND new.category_id = 1 DO INSTEAD INSERT INTO climate.measurement_01_001 (id, station_id, taken, amount, category_id, flag)

VALUES (new.id, new.station_id, new.taken, new.amount, new.category_id, new.flag);

数据最初是在MySQL中,但出于性能原因(并利用PL / R扩展)必须切换到PostgreSQL.

谢谢！