本文共 2133 字，大约阅读时间需要 7 分钟。
作者：瀚高PG实验室 （Highgo PG Lab）- 天蝎座
[highgo@hgdb ~]$ pg_config |grep CONFIGCONFIGURE = '--prefix=/opt/HighGo/db/hgdb4_enterprise' '--enable-nls=zh_CN zh_TW' '--with-perl' '--with-python' '--without-tcl' '--without-gssapi' '--without-pam' '--with-ldap' '--without-bonjour' '--with-openssl' '--with-libxml' '--with-libxslt' '--enable-thread-safety' '--with-zlib' '--without-selinux' '--with-ossp-uuid' '--with-pgport=5866' '--with-hgdb-extra-version=Enterprise Edition' 'LDFLAGS=-llber'highgo=# \d myt Table "public.myt" Column | Type | Modifiers --------+-----------------------+----------- id | integer | nm | character varying(20) | highgo=# \! head /tmp/file.csv1,Fa,Shighgo=# truncate myt;TRUNCATE TABLEhighgo=# copy myt from '/tmp/file.csv' with csv;ERROR: 22P02: invalid input syntax for integer: "a"CONTEXT: COPY myt, line 2, column id: "a"highgo=# select * from myt; id | nm ----+----(0 rows)
报错以后发现，第二行出错了，之前的行也看不到数据。
如果在大量的数据copy时，假如出现问题，那么整个copy过程都是失败的， 并且数据实际占用磁盘空间，但是无法访问。 如果我们想继续加载过程而忽略错误，可以使用pg_bulkload工具。highgo=# create extension pg_bulkload;CREATE EXTENSIONhighgo=# select * from pg_available_extensions where name='pg_bulkload'; name | default_version | installed_version | comment -------------+-----------------+-------------------+------------------------------------------------ pg_bulkload | 1.0 | | pg_bulkload is a high speed data loading utility for PostgreSQL(1 row)highgo=# create extension pg_bulkload;CREATE EXTENSION[highgo@hgdb ~]$ cat /tmp/file.ctl TYPE = CSVINPUT = /tmp/file.csv DELIMITER="," #分隔符TABLE = myt LOGFILE = /tmp/blk.log #日志文件PARSE_BADFILE = /tmp/parse.csv #坏数据记录[highgo@hgdb ~]$ pg_bulkload /tmp/file.ctl -d highgoNOTICE: BULK LOAD STARTNOTICE: BULK LOAD END 0 Rows skipped. 1 Rows successfully loaded. 1 Rows not loaded due to parse errors. 0 Rows not loaded due to duplicate errors. 0 Rows replaced with new rows.WARNING: some rows were not loaded due to errors.[highgo@hgdb ~]$ more /tmp/parse.csva,S
转载地址：http://oiowz.baihongyu.com/