mysql 导入数据到postgresql

创建PG的表脚本

DROP TABLE IF EXISTS "public"."t_resource_info";
CREATE TABLE "public"."t_resource_info" (
  "area_code" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "id" int4 NOT NULL DEFAULT NULL,
  "resource_title" varchar(1024) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "resource_format" varchar(16) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "resource_size_int" int8 NOT NULL DEFAULT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT NULL::timestamp without time zone,
  "down_count" int4 NOT NULL DEFAULT 0,
  "file_id" char(36) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::bpchar,
  "thumb_id" char(36) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '1'::bpchar,
  "resource_type" int4 NOT NULL DEFAULT NULL,
  "structure_id" int4 NOT NULL DEFAULT NULL,
  "person_id" int4 NOT NULL DEFAULT NULL,
  "identity_id" int4 NOT NULL DEFAULT NULL
)
;
COMMENT ON COLUMN "public"."t_resource_info"."area_code" IS '地区码';
COMMENT ON COLUMN "public"."t_resource_info"."id" IS '地区中主键';
COMMENT ON COLUMN "public"."t_resource_info"."resource_title" IS '资源名称';
COMMENT ON COLUMN "public"."t_resource_info"."resource_format" IS '资源格式';
COMMENT ON COLUMN "public"."t_resource_info"."resource_size_int" IS '资源尺寸';
COMMENT ON COLUMN "public"."t_resource_info"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."t_resource_info"."down_count" IS '下载次数';
COMMENT ON COLUMN "public"."t_resource_info"."file_id" IS '文件ID';
COMMENT ON COLUMN "public"."t_resource_info"."thumb_id" IS '缩略图ID';
COMMENT ON COLUMN "public"."t_resource_info"."resource_type" IS '资源类型';
COMMENT ON COLUMN "public"."t_resource_info"."structure_id" IS '所在的结构目录';
COMMENT ON COLUMN "public"."t_resource_info"."person_id" IS '人员ID';
COMMENT ON COLUMN "public"."t_resource_info"."identity_id" IS '人员身份ID';

-- ----------------------------
-- Primary Key structure for table t_resource_info
-- ----------------------------
ALTER TABLE "public"."t_resource_info" ADD CONSTRAINT "t_resource_info_pkey" PRIMARY KEY ("id", "area_code");

 

-- 导出文件示例(mysql)
mysql -uroot -pD****** -Ne "use dsideal_db; select 'kp' as area_code,id,resource_title,resource_format,resource_size_int,create_time,down_count,file_id,thumb_id,resource_type,structure_id,person_id,identity_id from t_resource_info limit 1000;" > /usr/local/mysql/t_resource_info.csv

-- 导入csv(pg)
COPY t_resource_info(area_code,id,resource_title,resource_format,resource_size_int,create_time,down_count,file_id,thumb_id,resource_type,structure_id,person_id,identity_id)
from 'd:\t_resource_info.csv'

感受一下效果：