PostgreSQL中的COPY命令空字符串转换为NULL无效。

Question

我有一个带整数列的CSV文件，现在以“”（空字符串）的形式保存。

我想将它们复制到表中作为NULL值。

使用JAVA代码，我尝试了以下方法：

String sql = "COPY " + tableName + " FROM STDIN (FORMAT csv,DELIMITER ',',  HEADER true)";
String sql = "COPY " + tableName + " FROM STDIN (FORMAT csv,DELIMITER ',', NULL ''  HEADER true)";

我得到的是：PSQLException错误：类型数字的输入语法无效：""

（说明：该错误是由于在尝试将空字符串转换为数字时引起的）

String sql = "COPY " + tableName + " FROM STDIN (FORMAT csv,DELIMITER ',', NULL '\"\"'  HEADER true)";

我得到的是：PSQLException: 错误：CSV引用字符不能出现在NULL规范中。

有人以前做过这个吗？

- Hieudien

---

你能尝试去掉反斜杠，让它变成 NULL '""' 吗？ - Fokko Driesprong

请展示一些来自您的CSV文件的实际行。NULL是由一个真正的空字符串（即nothing）还是由两个双引号表示？并且请注明您使用的Postgres版本。 - Erwin Brandstetter

Answer 1

我假设你知道数字数据类型没有“空字符串”的概念（''）。它只能是一个数字或者NULL（numeric可以是'NaN'，但不适用于integer之类的类型）。

看起来你从像text这样的字符串数据类型进行了导出，并且其中有一些实际为空的字符串 - 现在这些被表示为"" - 因为在CSV格式中""是默认的QUOTE字符。

NULL将被表示为什么都没有，甚至没有引号。请参见手册:

NULL

指定表示null值的字符串。在文本格式中，默认为\N（反斜杠-N），在CSV格式中为未加引号的空字符串。

由于""已经表示为空字符串，因此不能将其定义为通常表示NULL的值。这将会产生歧义。

要解决问题，我看到两个选择：

1. 在将文件/流传送到COPY之前编辑CSV文件/流，并将""替换为什么都没有。如果其中有实际的空字符串，或者在字符串内部使用""转义字面上的"，则可能会比较棘手。

2. （我会这样做。）导入到一个辅助临时表中，其结构与目标表完全相同，除了integer列被转换为text。然后从那里INSERT（或UPSET？）到目标表中，在此过程中即时正确地转换integer值：

-- empty temp table with identical structure
CREATE TEMP TABLE tbl_tmp AS TABLE tbl LIMIT 0;

-- ... except for the int / text column
ALTER TABLE tbl_tmp ALTER col_int TYPE text;

COPY tbl_tmp ...;

INSERT INTO tbl  -- identical number and names of columns guaranteed
SELECT col1, col2, NULLIF(col_int, '')::int  -- list all columns in order here
FROM   tbl_tmp;

临时表将在会话结束时自动删除。如果您在同一会话中多次运行此操作，则可以仅截断现有的临时表或在每个事务后删除它。

相关链接:

• 如何在Postgres中使用CSV文件中的值更新选定的行？
• Rails迁移：尝试将列类型从字符串更改为整数
• PostgreSQL临时表的线程安全性

- Erwin Brandstetter

---

起初我不相信选项2。然后，我看到它是Erwin。 - Zachary Ryan Smith

Answer 2

从Postgres 9.4版本开始，你现在可以使用FORCE_NULL。这将导致空字符串被转换为NULL。非常方便，特别是在处理CSV文件时（实际上只有在使用CSV格式时才允许这样做）。

语法如下：

COPY table FROM '/path/to/file.csv' 
WITH (FORMAT CSV, DELIMITER ';', FORCE_NULL (columnname));

进一步的细节在文档中有详细说明：https://www.postgresql.org/docs/current/sql-copy.html

- moojen

---

选项 FORCE_NULL 应该带有下划线，并且应该在“WITH（...）”子句中指定。例如： COPY table FROM '/path/to/file.csv' WITH (FORMAT CSV, DELIMITER ';', FORCE_NULL (field1, field2, field3)); - spatar

@spatar 你说得对，这确实是当前首选的语法。我使用的语法仍然受到支持，即使在13版本中也是如此，但使用“标准语法”更有意义。我已经纠正了我的例子，谢谢！ - moojen

有没有一种方法可以告诉force_null将所有字段强制为空，而不需要逐个列出它们？类似于FORCE_NULL (*)这样的东西？ - Zachary Ryan Smith

@ZacharyRyanSmith 看起来这只适用于FORCE_QUOTE，但你当然可以尝试看看它是否有效。 - moojen

Answer 3

如果我们想要将所有的空白行和空行替换为 null，则只需在复制命令中加入 emptyasnull blanksasnull 即可。

语法：

    copy Table_name (columns_list)
    from 's3://{bucket}/{s3_bucket_directory_name + manifest_filename}'
    iam_role '{REDSHIFT_COPY_COMMAND_ROLE}' emptyasnull blanksasnull 
    manifest DELIMITER ',' IGNOREHEADER 1 compupdate off csv gzip;

注意：它将适用于所有包含空/空白值的记录。

- saiyam