不能将CSV复制到postgreSQL表中：timestamp列将不接受空字符串 [英] cannot copy CSV into postgreSQL table : timestamp column won't accept the empty string

问题描述

我要将CSV文件导入9.2版，但CSV文件在最终列位置中具有双引号双引号以表示NULL值：

I want to import a CSV file into version 9.2 but the CSV file has double-quote double-quote in the final column position to represent a NULL value:

2，1001，9，2，0，0，130，，2012-10-22 09：33：07.073000000

，它映射到类型为Timestamp的列。 postgreSQL不喜欢。我试图设置NULL选项，但也许我不是做正确吗？我尝试了 NULL as'和 NULL''和 NULL as' 和 NULL但未成功;这是我的命令：

which is mapped to a column of type Timestamp. postgreSQL doesn't like the "". I've tried to set the NULL option but maybe I'm not doing it correctly? I've tried NULL as '"" and NULL '' and NULL as '' and NULL "" but without success; here's my command:

COPY SCH.DEPTS 
FROM 'H:/backups/DEPTS.csv' 
WITH (
 FORMAT CSV,
 DELIMITER ',' ,
 NULL  '',
 HEADER TRUE,
 QUOTE   '"' 
 )

，但失败并显示错误：

错误：类型为timestamp的输入语法无效：

ERROR: invalid input syntax for type timestamp: ""

CONTEXT：COPY depts，line 2，column expirydate：

CONTEXT: COPY depts, line 2, column expirydate: ""

PS有一种方法可以指定布尔值到COPY命令的字符串表示形式？生成CSV（其中有很多）的实用程序使用false 和true。

P.S. Is there a way to specify the string representation of Booleans to the COPY command? The utility that produced the CSVs (of which there are many) used "false" and "true".

推荐答案

空字符串（）不是有效的时间戳， COPY 似乎不提供 FORCE NULL 或 FORCE EMPTY TO NULL 模式;它有相反的， FORCE NOT NULL ，但这不会做你想要的。

The empty string ("") isn't a valid timestamp, and COPY doesn't appear to offer a FORCE NULL or FORCE EMPTY TO NULL mode; it has the reverse, FORCE NOT NULL, but that won't do what you want.

可能需要将 COPY 数据导入具有文本字段的表格，可能是 UNLOGGED 或 TEMPORARY 表，然后使用 INSERT INTO real_table SELECT col1，col，col3，NULLIF（tscol， '）FROM temp_table; 。

You probably need to COPY the data into a table with a text field for the timestamp, probably an UNLOGGED or TEMPORARY table, then use an INSERT INTO real_table SELECT col1, col, col3, NULLIF(tscol,'') FROM temp_table;.

COPY true 和 false 作为布尔值，因此您不应该有任何问题。

COPY should accept true and false as booleans, so you shouldn't have any issues there.

或者，使用简单的Python脚本和 csv 模块读取CSV，然后使用 psycopg2 到 COPY 行插入Pg。或者只需写入新的清理过的CSV并将其送入 COPY 。或使用执行数据变换（如Pentaho Kettle或Talend）的ETL工具。

Alternately, read the CSV with a simple Python script and the csv module, and then use psycopg2 to COPY rows into Pg. Or just write new cleaned up CSV out and feed that into COPY. Or use an ETL tool that does data transforms like Pentaho Kettle or Talend.

这篇关于不能将CSV复制到postgreSQL表中：timestamp列将不接受空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！