postgresql - 如何将 NULL 值插入 PostgreSQL 表

Question

我在 CSV 文件中有数据，我正尝试使用 pgloader 将其插入到 postgresSQL 表中。输入文件来自 MS SQL 服务器导出，NULL 值已明确转换为 NULL。

我的 pgloader 脚本似乎无法处理关键字 NULL，尤其是整数和时间戳字段。

我真的不知道我错过了什么。非常感谢您的帮助。

我可以从 psql 控制台成功插入到表中:

insert into raw.a2
(NUM , F_FILENO , F_FOLIONO , F_DOC_TYPE , F_DOCDATE , F_BATCH , F_BOX , F_BLUCPY , F_ROUTOPOST , F_ROUTOUSR , F_WFCREATE , LINKEDFILE , DATECREATE , USERCREATE , DATEUPDATE , USERUPDATE , MEDIA , PGCOUNT , GROUPNUM , SUBJECT , PRI , F_FILECAT)
values
(
16,'18',3,'Nomination Details',NULL,NULL,NULL,1,NULL,NULL,1,'00000016.TIF','2011-02-08 13:02:11.000','isaac','2012-01-12 08:52:31.000','henrey','Multi',4,1.0,0,'-',NULL
);

插入 0 1

文件样本

1,'6',1,'Details',2011-02-22 00:00:00.000,NULL,NULL,1,NULL,NULL,2,'00000001.TIF',2011-02-08 09:42:24.000,'kevin',2011-10-27 09:08:42.000,'james','Multi',1,1.0,0,'-',NULL
2,'6',2,'Bio data',NULL,NULL,NULL,1,NULL,NULL,2,'00000002.TIF',2011-02-08 10:25:11.000,'kevin',2012-11-19 16:20:49.000,'pattie','Multi',4,1.0,0,'-',NULL
4,'10',1,'Details',2011-02-22 00:00:00.000,NULL,NULL,1,NULL,NULL,2,'00000004.TIF',2011-02-08 10:43:38.000,'kevin',2014-07-18 10:46:06.000,'brian','Multi',1,1.0,0,'-',NULL

pgloader 命令

pgloader --type csv --with truncate --with "fields optionally enclosed by '''" --with "fields terminated by ','" --set "search_path to 'raw'" - "postgresql://postgres:postgres@localhost/doc_db?a2" < null_test

表格

                               Table "raw.a2"
   Column    |            Type             | Collation | Nullable | Default 
-------------+-----------------------------+-----------+----------+---------
 num         | integer                     |           | not null | 
 f_fileno    | character varying(15)       |           |          | 
 f_foliono   | integer                     |           |          | 
 f_doc_type  | character varying(50)       |           |          | 
 f_docdate   | timestamp without time zone |           |          | 
 f_batch     | integer                     |           |          | 
 f_box       | integer                     |           |          | 
 f_blucpy    | integer                     |           |          | 
 f_routopost | integer                     |           |          | 
 f_routousr  | character varying(49)       |           |          | 
 f_wfcreate  | integer                     |           |          | 
 linkedfile  | character varying(255)      |           |          | 
 datecreate  | timestamp without time zone |           |          | 
 usercreate  | character varying(50)       |           |          | 
 dateupdate  | timestamp without time zone |           |          | 
 userupdate  | character varying(50)       |           |          | 
 media       | character varying(5)        |           |          | 
 pgcount     | smallint                    |           |          | 
 groupnum    | double precision            |           |          | 
 subject     | smallint                    |           |          | 
 pri         | character varying(1)        |           |          | 
 f_filecat   | character varying(50)       |           |          | 

索引:

"a2_pkey"主键，btree (num)

输出/错误

2019-07-24T05:55:24.231000Z WARNING Target table "\"raw\".\"a2\"" has 1 indexes defined against it.
2019-07-24T05:55:24.237000Z WARNING That could impact loading performance badly.
2019-07-24T05:55:24.237000Z WARNING Consider the option 'drop indexes'.
2019-07-24T05:55:24.460000Z ERROR PostgreSQL ["\"raw\".\"a2\""] Database error 22P02: invalid input syntax for integer: "NULL"
CONTEXT: COPY a2, line 1, column f_batch: "NULL"
2019-07-24T05:55:24.461000Z ERROR PostgreSQL ["\"raw\".\"a2\""] Database error 22007: invalid input syntax for type timestamp: "NULL"
CONTEXT: COPY a2, line 1, column f_docdate: "NULL"

Answer 1

来自docs pgloader 的:

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.

看起来您缺少 --在您的命令中使用 'null if "NULL"'。

否则，您应该能够直接从 psql 加载 CSV 数据:

\copy raw.a2 (NUM, F_FILENO, F_FOLIONO, F_DOC_TYPE, F_DOCDATE, F_BATCH, F_BOX, F_BLUCPY, F_ROUTOPOST, F_ROUTOUSR, F_WFCREATE, LINKEDFILE, DATECREATE, USERCREATE, DATEUPDATE, USERUPDATE, MEDIA, PGCOUNT, GROUPNUM, SUBJECT, PRI, F_FILECAT) FROM 'file.csv' WITH (FORMAT csv, NULL 'NULL')