英文:
inserting null date in postgres
问题
以下是你要的中文翻译：
我在SQL中使用INSERT ... SELECT FROM语句插入了一个空日期，但出现了错误。
CREATE TABLE null_date (
id bigserial PRIMARY KEY
, some_date date
);
WITH date_data (some_date) AS (
VALUES (null)
)
INSERT INTO null_date (some_date)
SELECT some_date
FROM date_data;
出现了以下错误：
ERROR: 列"some_date"的类型为date，但表达式的类型为text
LINE 5: SELECT some_date
^
HINT: 您需要重新编写或转换表达式。
然而，如果我尝试直接插入它，它可以正常工作。
INSERT INTO null_date (some_date)
VALUES (null)
可以有人帮我理解这里发生了什么吗？这是db<>fiddle的链接。谢谢。
英文:
I am inserting a null date with a INSERT ... SELECT FROM statement in sql
CREATE TABLE null_date (
id bigserial PRIMARY KEY
, some_date date
);
WITH date_data (some_date) AS (
VALUES (null)
)
INSERT INTO null_date (some_date)
SELECT some_date
FROM date_data;
and it fails with
ERROR: column "some_date" is of type date but expression is of type text
LINE 5: SELECT some_date
^
HINT: You will need to rewrite or cast the expression.
However, if I try to insert it directly, it works
INSERT INTO null_date (some_date)
VALUES (null)
can somebody please help me understand what's happening here? Here is the link to db<>fiddle. Thanks
答案1
得分: 3
问题在于 VALUES 语句以及随之而来的 WITH 子句会将 NULL 值视为类型 text，因为 PostgreSQL 不知道 NULL 应该是哪种数据类型。在 INSERT INTO ... VALUES (...) 中，你不会遇到这个问题，因为在这里 PostgreSQL 立刻知道 NULL 值的类型是 unknown，将被插入到特定列中，因此它会解析为目标数据类型。
在 PostgreSQL 无法从上下文中猜测数据类型的情况下，最好使用显式类型转换：
WITH date_data (some_date) AS (
VALUES (CAST(null AS date))
)
INSERT INTO null_date (some_date)
SELECT some_date
FROM date_data;
PostgreSQL 在像这样的情况下以前的行为是不同的，但提交1e7c4bb0049 在2017年改变了这一点。阅读提交信息以获取解释。
英文:
The problem is that the VALUES statement and consequently the WITH clause will treat the NULL value as type text, because PostgreSQL doesn't know which data type the NULL should be. You don't have that problem with INSERT INTO ... VALUES (...), because here PostgreSQL knows right away that the NULL value with unknown type will be inserted into a certain column, so it will resolve it to the target data type.
In cases where PostgreSQL cannot guess the data type from context, you had better use an explicit type cast:
WITH date_data (some_date) AS (
VALUES (CAST(null AS date))
)
INSERT INTO null_date (some_date)
SELECT some_date
FROM date_data;
PostgreSQL used to behave differently in cases like this, but commit 1e7c4bb0049 changed that in 2017. Read the commit message for an explanation.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论