在 Postgres 中使用复制命令时出错（错误：日期类型的输入语法无效：“”）
我有一个 CSV 文件，我尝试使用 Postgres COPY 命令来从该 CSV 文件填充表。表列之一 NEXT_VISIT 属于日期数据类型。 CSV 文件中应进入此日期列的一些相应字段具有空值。
正在运行的复制命令如下所示：
COPY "VISIT_STAGING_TABLE" from E'C:\\Users\\Sir Codealot\\Desktop\\rufijihdss-2007-2010\\rufijihdss\\VISIT_TEST.CSV' CSV HEADER
当我运行此命令时，出现错误：
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 的某些字段有值""吗？
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: ""
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 ""?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将
WITH NULL AS ''添加到您的命令中（COPY 期望 NULL 默认表示为“\N”（反斜杠-N））。更多详细信息请参见：postgresql COPY
Add
WITH NULL AS ''to your command (COPY expects NULLs to be represented as "\N" (backslash-N) by default).More details here: postgresql COPY
我遇到了完全相同的问题，为我解决的方法是使用语句
WITH NULL ''。重要的是撇号之间不要有空格。我最初使用了语句
WITH NULL ' '并得到了与您相同的错误消息（错误：“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.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.