postgresql 将 csv 文件中存在的 NULL 值复制到 postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19034674/
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
Copy NULL values present in csv file to postgres
提问by Jannat Arora
I have csv file (y.csv) in the folowing format:
我有以下格式的 csv 文件（y.csv）：
's', '1999-10-10', '1999-12-12'
'b', '99-10-10 BC', '1-10-10 BC'
'c', 'NULL', 'NULL'
I have a few NULL values (for date) in it which I have indicated through the string 'NULL'.
我有一些 NULL 值（用于日期），我已经通过字符串 'NULL' 指示了这些值。
I am trying to copy the csv file into postgres. For doing so I have created a table:
我正在尝试将 csv 文件复制到 postgres 中。为此，我创建了一个表：
create table r (id character varying(255), timebegin date, timeend date);
Now I am trying to copy the above .csv file into postgres using the command
现在我正在尝试使用命令将上面的 .csv 文件复制到 postgres 中
copy r from '/home/y.csv' delimiter ',' csv;
ERROR: invalid input syntax for type date: " 'NULL'"
CONTEXT: COPY r, line 1, column timebegin: " 'NULL'"
On doing so I am getting an error with NULL. Can someone please help me figure out the error and correct it.
这样做时，我收到了 NULL 错误。有人可以帮我找出错误并纠正它。
回答by sqlint
Have you tried it?
你试过吗？
copy r from '/home/y.csv' delimiter ',' csv WITH NULL AS 'null';
回答by heretolearn
The error is because you are inserting NULLas string. Remove the quotes around NULLand it should work.
错误是因为您NULL以字符串形式插入。删除周围的引号NULL，它应该可以工作。
回答by Nidhi Mahajan
after giving the path of the file, we need to use 'With' before adding other paramters -
给出文件路径后，我们需要在添加其他参数之前使用'With' -
copy r from '/home/y.csv' with delimiter ',' NULL AS 'null' csv header;
从带有分隔符 ',' NULL AS 'null' csv 标头的 '/home/y.csv' 复制 r；
