我有一个带有8列的CSV文件,但我的表具有与CSV文件相同的6列。以下是CSV和Table
的格式和模式示例.csv
"CH_ID","CH_NAME","ADDRESS_1","ADDRESS_2","POST_CODE","MDSC","UPDATE_TIMESTAMP","DELETED_IND"
403,"1182463","","10 St Pauls Court","SG2 8DN",1,2017-10-20 12:08:36,"N"
表结构: -
CREATE TABLE ts_ch (
id int8 NOT NULL DEFAULT nextval('ts_care_homes_seq'::regclass),
ch_name varchar(200) NULL DEFAULT NULL::character varying,
address_1 varchar(255) NULL DEFAULT NULL::character varying,
address_2 varchar(255) NULL DEFAULT NULL::character varying,
post_code varchar(10) NULL DEFAULT NULL::character varying,
mdsc_patient int2 NULL,
PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
) ;
我尝试了以下命令:
copy ts_care_homes(id, ch_name, address_1, address_2, post_code, mdsc_patient) FROM '/var/tmp/care_home_v.csv' csv HEADER
但是我遇到了这个错误:
上次预期列之后的额外数据
您不能仅使用普通COPY
命令加载CSV文件的一部分。
实现您想要的方法是使用COPY ... FROM PROGRAM
,例如这样(假设您已安装了sed
(:
COPY laurenz.ts_ch(id, ch_name, address_1, address_2, post_code, mdsc_patient)
FROM PROGRAM
'sed -e ''s/(,([^,]*)|(("[^"]*")*)){2,2}$//'' /var/tmp/care_home_v.csv'
(FORMAT csv, HEADER on);
sed
命令从CSV文件中剥离最后两列。