V2CE – 将0000-00-00 00:00:00等非法时间戳写入Greenplum

说明

本文描述问题及解决方法同样适用于 腾讯云 云数据仓库 PostgreSQL（CDWPG）。

背景

由于Greenplum底层的限制，非法时间戳是不能被插入GP的，那么如何能忽略这些非法时间戳并保留该条记录呢，下面我们做一下测试。

模拟场景

1. 创建测试表

创建两张测试表，一张时间戳字段为varchar，模拟非法数据源，另一张时间戳字段为timestamp

dy_test=# CREATE TABLE varchar_timestamp(name varchar, time varchar);
CREATE TABLE
dy_test=# CREATE TABLE timestamp(name varchar, time timestamp);
CREATE TABLE

2. 模拟非法时间戳

对varchare表插入非法时间戳

dy_test=# INSERT INTO varchar_timestamp VALUES ('test', '0000-00-00 00:00:00');
INSERT 0 1

3. 尝试插入非法时间戳

使用常规方式，将非法的时间戳查询出来并插入到timestamp表

dy_test=# INSERT INTO timestamp
dy_test-# SELECT name, 
dy_test-#        time
dy_test-# FROM varchar_timestamp;
ERROR:  column "time" is of type timestamp without time zone but expression is of type character varying
HINT:  You will need to rewrite or cast the expression.

这里可以明确地看到，由于字段time是timestamp，不接受字符串‘0000-00-00 00:00:00’，所以需要以另一种方式插入。

解决方案

使用NULLIF()实现忽略非法时间戳

dy_test=# INSERT INTO timestamp
dy_test-# SELECT name, 
dy_test-#        NULLIF(time, '0000-00-00 00:00:00')::timestamp
dy_test-# FROM varchar_timestamp;
INSERT 0 1
dy_test=# SELECT * FROM timestamp;
 name | time 
------+------
 test | 
(1 row)

可以看到，非法时间戳已被替换成了NULL，并成功保留了该条记录。

正文完
2022-04-04