BigQuery中如何将datetime类型的列设置为null

前提说明 #

BigQuery的datetime类型本身支持存储null值，操作失败通常是因为列设置了NOT NULL非空约束，或者操作语句不符合规范。 #

常见操作场景及示例 #

1. 更新表中已有datetime字段值为null #

直接赋值NULL即可，注意UPDATE语句必须携带WHERE条件：

UPDATE `你的项目ID.你的数据集名.你的表名`
-- 要修改的datetime列名
SET datetime_col = NULL 
-- 按需指定要更新的行范围，全表更新可写WHERE 1=1
WHERE id = 100;

2. 新增数据时指定datetime字段为null #

插入数据时直接传入NULL作为对应字段的值即可：

INSERT INTO `你的项目ID.你的数据集名.你的表名` (id, datetime_col)
VALUES 
  (1, DATETIME "2024-05-20 14:30:00"),
  (2, NULL);

3. 查询时将不符合要求的datetime值转换为null #

可以配合NULLIF、CASE等函数实现转换，比如把默认的'1900-01-01 00:00:00'转为null：

SELECT
  id,
  NULLIF(datetime_col, DATETIME "1900-01-01 00:00:00") AS processed_datetime
FROM `你的项目ID.你的数据集名.你的表名`;

异常处理 #

如果执行赋值null操作时报错，首先检查列是否设置了NOT NULL约束，可先修改表结构移除约束后再操作：

ALTER TABLE `你的项目ID.你的数据集名.你的表名`
ALTER COLUMN datetime_col DROP NOT NULL;

内容的提问来源于stack exchange，提问作者tandem