postgresql - 如何使用 pgloader 从 CSV 文件导入空字符串作为空值？

Question

我正在使用 pgloader 从 .csv 文件导入，该文件在双引号中包含空字符串。示例行是

12334,0,"MAIL","CA","","Sanfransisco","TX","","",""

导入成功后，带有双引号("")的字段在postgres数据库中显示为两个单引号('')。

有没有办法可以插入一个 null 甚至空字符串来代替两个单引号 ('')？

我正在使用参数 -

WITH truncate,
           fields optionally enclosed by '"',
           fields escaped by double-quote,
           fields terminated by ','
SET client_encoding to 'UTF-8',
           work_mem to '12MB',
           standard_conforming_strings to 'on'

我试过像这样使用文档中提到的 'empty-string-to-null' -

CAST column enumerate.fax using empty-string-to-null

但它给了我一个错误说-

pgloader nph_opr_addr.test.load An unhandled error condition has been signalled: At LOAD CSV

^ (Line 1, Column 0, Position 0) Could not parse subexpression ";" when parsing

Answer 1

使用字段选项:

null if blanks

像这样:

...
having fields foo, bar, mynullcol null if blanks, baz

来自documentation :

null if

This option takes an argument which is either the keyword blanks or a double-quoted string.

When blanks is used and the field value that is read contains only space characters, then it's automatically converted to an SQL NULL value.

When a double-quoted string is used and that string is read as the field value, then the field value is automatically converted to an SQL NULL value