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

问题描述

我有一个带有一些整数列的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：错误：数字类型的输入语法无效：

 字符串sql = COPY + tableName + FROM STDIN（FORMAT csv，DELIMITER'，'，NULL'\ \'HEADER真正）; 
  

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

有人曾做过此事吗？

解决方案

我假设您知道数字数据类型没有空字符串（''）的概念。它可以是数字或NULL（对于数值为'NaN'-但对于整数等而言则不是）。

看起来像是从类似 text 这样的字符串数据类型导出的，里面有一些实际的空字符串-现在可以表示出来如 - 是默认的 QUOTE 字符CSV格式。

NULL将由不表示，甚至不包括引号。手册：

NULL

指定代表空值的字符串，默认为 \N
（反斜杠-N）为文本格式，未引用的空字符串为CSV格式。

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

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




1. 在馈入<$ c之前编辑CSV文件/流$ c> COPY 并将替换为 nothing 。如果您在其中也有实际的空字符串，或者在字符串中使用 转义文字 ，则可能会比较棘手。 / p>




2. （我会怎么做。）导入具有相同结构的辅助临时表，除了 integer 列转换为文本，然后 INSERT （或UPSERT？）从那里到目标表，转换整数随时可以正确设置：

 -具有相同结构的空临时表
创建表tbl_tmp AS TABLE tbl LIMIT 0; 
 
-...除了int / text列
 ALTER TABLE tbl_tmp ALTER col_int TYPE文本; 
 
 COPY tbl_tmp ...; 
 
插入tbl -保证列数和列名相同
 SELECT col1，col2，NULLIF（col_int，''）:: int-在此处按顺序列出所有列
 FROM tbl_tmp; 
  

临时表会在末尾删除会话自动。如果您在同一会话中多次运行此命令，则只需截断现有的临时表或在每次事务处理后将其删除。

相关：

• 如何使用Postgres中的CSV文件中的值更新所选行？


• Rails迁移：尝试将列的类型从字符串到整数


• 临时表的Postgresql线程安全性

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

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

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

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

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

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

Any one has done this before ?

解决方案

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.)

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 would be represented by nothing, not even quotes. The manual:

NULL

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.

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

To fix, I see two options:

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.

2. (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 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.

Related:

• How to update selected rows with values from a CSV file in Postgres?
• Rails Migrations: tried to change the type of column from string to integer
• postgresql thread safety for temporary tables

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