在Postgres中使用复制命令时出错（错误：类型为date的无效输入语法：“”） [英] Error on using copy Command in Postgres (ERROR: invalid input syntax for type date: "")

问题描述

我有一个CSV文件，试图从中使用Postgres COPY 命令从该CSV文件填充表格。 NEXT_VISIT 的表列之一是日期数据类型。

I have a CSV file from which I am trying to use Postgres COPY command in order to populate a table from that CSV file. One of the table columns NEXT_VISIT is of a date data_type. Some of the corresponding fields in the CSV file which are supposed to go into this date column have null values.

复制命令正在运行，就像这样：

The Copy command am running is like so:

COPY "VISIT_STAGING_TABLE" from E'C:\\Users\\Sir Codealot\\Desktop\\rufijihdss-2007-2010\\rufijihdss\\VISIT_TEST.CSV' CSV HEADER 

当我运行此命令，我得到以下错误：

When I run this command I get the error:

ERROR:  invalid input syntax for type date: ""
CONTEXT:  COPY VISIT_STAGING_TABLE, line 2, column NEXT_VISIT: ""

********** Error **********
ERROR: invalid input syntax for type date: ""
SQL state: 22007
Context: COPY VISIT_STAGING_TABLE, line 2, column NEXT_VISIT: ""

如何运行复制命令并使Postgres接受CSV文件中与 NEXT_VISIT 相对应的某些字段的值为 ？

How can I run the copy command and get Postgres to accept that some of the fields in the CSV file corresponding to NEXT_VISIT have values ""?

推荐答案

我很开心g完全相同的问题，为我解决的是使用语句 WITH NULL’’。重要的是在撇号之间不要留空格。

I was having the exact same problem, and what solved it for me was to use the statement WITH NULL ''. It is important not to have a space between the apostrophes.

我最初使用语句 WITH NULL''并得到与您相同的错误消息（错误：语法错误

I originally used the statement WITH NULL ' ' and got the same error message you did (ERROR: syntax error at or near "WITH NULL").

但是当我消除撇号之间的空格时，它起作用了。

But when I eliminated the space between the apostrophes it worked.

这篇关于在Postgres中使用复制命令时出错（错误：类型为date的无效输入语法：“”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！