postgresql 在 Postgres 中使用复制命令时出错（错误：日期类型的输入语法无效：“”）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6045402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error on using copy Command in Postgres (ERROR: invalid input syntax for type date: "")
提问by BlakkPhoenixx
I have a CSV file from which I am trying to use Postgres COPYcommand in order to populate a table from that CSV file. One of the table columns NEXT_VISITis 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.
我有一个 CSV 文件，我试图从中使用 PostgresCOPY命令来从该 CSV 文件填充表。表列NEXT_VISIT之一是日期数据类型。CSV 文件中应该进入此日期列的一些相应字段具有空值。
The Copy command am running is like so:
正在运行的 Copy 命令如下所示：
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: ""
How can I run the copy command and get Postgres to accept that some of the fields in the CSV file corresponding to NEXT_VISIThave values ""?
如何运行复制命令并让 Postgres 接受 CSV 文件中对应的某些字段NEXT_VISIT具有值""？
采纳答案by user3431474
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").
我最初使用该语句WITH NULL ' '并收到与您相同的错误消息（错误：“WITH NULL”处或附近的语法错误）。
But when I eliminated the space between the apostrophes it worked.
但是当我消除撇号之间的空格时，它起作用了。
回答by hamzus
Add WITH NULL AS ''to your command (COPY expects NULLs to be represented as "\N" (backslash-N) by default).
添加WITH NULL AS ''到您的命令中（COPY 期望默认情况下将 NULL 表示为“\N”（反斜杠-N））。
COPY "VISIT_STAGING_TABLE" from E'C:\Users\Sir Codealot\Desktop\rufijihdss-2007-2010\rufijihdss\VISIT_TEST.CSV' WITH CSV HEADER NULL AS ''
More details here: postgresql COPY
更多细节在这里：postgresql COPY
