postgres csv日期为空导入错误 [英] postgres csv date null import error
问题描述
我正在将数据导入Postgres数据库.我要导入的表包括带有日期的几列.
I am importing data into a Postgres database. The table I am importing to includes a couple of columns with dates.
但是,我正在上传的CSV文件在某些日期字段中具有空值.
The CSV file I am uploading, however, has empty values for some of the date fields.
该表如下所示:
dot_number bigint,
legal_name character varying,
dba_name character varying,
carrier_operation character varying,
hm_flag character varying,
pc_flag character varying,
...
mcs150_date date,
mcs150_mileage bigint,
数据如下:
1000045,"GLENN M HINES","","C","N","N","317 BURNT BROW RD","HAMMOND","ME","04730","US","317 BURNT BROW RD","HAMMOND","ME","04730","US","(207) 532-4141","","","19-NOV-13","20000","2012","23-JAN-02","ME","1","2"
1000050,"ROGER L BUNCH","","C","N","N","108 ST CHARLES CT","GLASGOW","KY","42141","US","108 ST CHARLES CT","GLASGOW","KY","42141","US","(270) 651-3940","","","","72000","2001","23-JAN-02","KY","1","1"
我尝试这样做:
COPY CC FROM 'C:\Users\Owner\Documents\FMCSA Data\FMCSA_CENSUS1_2016Sep.txt' DELIMITER ',' CSV HEADER NULL '';
但是我得到这个错误:
错误:类型为date的输入语法无效:"上下文:COPY cc,行24,列mcs150_date:"**********错误**********
ERROR: invalid input syntax for type date: "" CONTEXT: COPY cc, line 24, column mcs150_date: "" ********** Error **********
错误:类型为date的输入语法无效:" SQL状态:22007内容:COPY cc,第24行,mcs150_date列:"
ERROR: invalid input syntax for type date: "" SQL state: 22007 Context: COPY cc, line 24, column mcs150_date: ""
这可能很简单,但是我在网上找到的所有解决方案都无效.
This is probably pretty simple, but none of the solutions I've found online did not work.
推荐答案
您需要指定 QUOTE
字符,以便将"
解释为 NULL
,就像这样:
You need to specify the QUOTE
character so that ""
would be interpreted as NULL
, like so:
COPY CC FROM 'C:\Users\Owner\Documents\FMCSA Data\FMCSA_CENSUS1_2016Sep.txt' DELIMITER ',' CSV HEADER QUOTE '"' NULL '';
QUOTE''
是添加的内容.
文档: https://www.postgresql.org/docs/current/static/sql-copy.html
这篇关于postgres csv日期为空导入错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!