时间戳

首页 > 解决方案 > 如何将 NULL 值插入 PostgreSQL 表

postgresql - 如何将 NULL 值插入 PostgreSQL 表

问题描述

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

对于关键字 NULL，我的 pgloader 脚本似乎失败了，尤其是整数和时间戳字段。

我真的不知道我错过了什么。您的帮助将不胜感激。

我可以从 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"

标签： postgresqlpgloader

解决方案

来自pgloader的文档：

null if

此选项采用关键字blanks或双引号字符串的参数。

使用时blanks且读取的字段值仅包含空格字符，则它会自动转换为 SQLNULL 值。

当使用双引号字符串并将该字符串作为字段值读取时，字段值会自动转换为 SQL NULL值。

看起来你--with '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')

推荐阅读