Postgresql COPY 空字符串作为 NULL 不起作用 [英] Postgresql COPY empty string as NULL not work

问题描述

我有一个包含整数列的 CSV 文件，现在它保存为"(空字符串).

I have a CSV file with some integer column, now it 's saved as "" (empty string).

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

I want to COPY them to a table as NULL value.

使用 JAVA 代码，我尝试了这些:

With JAVA code, I have try these:

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

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

I get: PSQLException: ERROR: invalid input syntax for type numeric: ""

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

我得到:PSQLException:错误:CSV 引号字符不能出现在 NULL 规范中

I get: PSQLException: ERROR: CSV quote character must not appear in the NULL specification

以前有人做过吗?

推荐答案

我假设你知道数字数据类型没有空字符串"的概念.('') .它是一个数字或 NULL(对于 numeric 或 'NaN' - 但对于 integer 等不是)

I assume you are aware that numeric data types have no concept of "empty string" ('') . It's either a number or NULL (or 'NaN' for numeric - but not for integer et al.)

看起来您是从 text 之类的字符串数据类型导出的，并且其中有一些实际的空字符串 - 现在表示为 "" - " 是 CSV 格式的默认 QUOTE 字符.

Looks like you exported from a string data type like text and had some actual empty string in there - which are now represented as "" - " being the default QUOTE character in CSV format.

NULL 将由 nothing 表示，甚至不是引号.手册:

NULL would be represented by nothing, not even quotes. The manual:

NULL

指定表示空值的字符串.默认为 N(反斜杠-N) 为文本格式，一个不带引号的空字符串为 CSV 格式.

Specifies the string that represents a null value. The default is N (backslash-N) in text format, and an unquoted empty string in CSV format.

您不能定义 "" 通常表示 NULL，因为它已经表示一个空字符串.会模棱两可.

You cannot define "" to generally represent NULL since that already represents an empty string. Would be ambiguous.

要修复，我看到两个选项:

To fix, I see two options:

1. 在输入 COPY 之前编辑 CSV 文件/流并替换"什么都没有.如果你也有实际的空字符串可能会很棘手 - 或者 "" 在字符串中转义文字 ".

1. Edit the CSV file / stream before feeding to COPY and replace "" with nothing. Might be tricky if you have actual empty string in there as well - or "" escaping literal " inside strings.

(我会做什么.)导入到具有相同结构的辅助临时表，除了将 integer 列转换为 text.然后 INSERT(或 UPSERT?)从那里到目标表，即时正确转换 integer 值:

(What I would do.) Import to an auxiliary temporary table with identical structure except for the integer column converted to text. Then INSERT (or UPSERT?) to the target table from there, converting the integer value properly on the fly:

-- 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;

临时表会在会话结束时自动删除.如果您在同一会话中多次运行此操作，则只需截断现有临时表或在每次事务后将其删除.

Temporary tables are dropped at the end of the session automatically. If you run this multiple times in the same session, either just truncate the existing temp table or drop it after each transaction.

相关:

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

这篇关于Postgresql COPY 空字符串作为 NULL 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！