postgresql Postgre，类型为date，但表达式类型为text [重复]

mpbci0fu 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(766)

此问题已在此处有答案：

Postgres (ERROR: column "date_deadline" is of type date but expression is of type text)（1个答案）
9天前关闭
我有错误
错误：列“portfolio_end_date”的类型为date，但表达式的类型为text行5：portfolio_end_date = tmp.portfolio_end_date，^提示：您需要重写或强制转换表达式。SQL状态：42804字符：186
为什么potgre返回错误

UPDATE ods2.project_portfolios as tbl 
set id_portfolio = tmp.id_portfolio,
id_project_link = tmp.id_project_link,
portfolio_start_date = tmp.portfolio_start_date,
portfolio_end_date = tmp.portfolio_end_date, 
id_employee = tmp.id_employee,
portfolio_changed_date = tmp.portfolio_changed_date,
portfolio_quarter = tmp.portfolio_quarter,
end_month = tmp.end_month,
historicity_time = tmp.historicity_time
from (
values (
    491533570142,
    25, 
    '2022-07-01'::date,
    NULL,
    51,
    '2023-02-28 14:27:24'::timestamp,

    NULL,
        '2022-07-01'::date,
    '2023-03-20 23:00:16'::timestamp)
) as tmp(
    id_portfolio, id_project_link, portfolio_start_date, portfolio_end_date, id_employee,
    portfolio_changed_date, portfolio_quarter, end_month, historicity_time)
    where tbl.id_portfolio = tmp.id_portfolio and tbl.historicity_time between 
    '2023-05-25 00:00:00' and '2023-05-25 23:59:59';

请求失败，尽管我没有看到任何错误

postgresql

来源：https://stackoverflow.com/questions/76334234/postgre-is-of-type-date-but-expression-is-of-type-text

1条答案

按热度按时间

ars1skjm1#

如果VALUES语句中的元素不是数字，并且没有显式转换为其他数据类型，则它将解析为text类型：

SELECT pg_typeof(col)
FROM (VALUES (NULL)) AS v(col);

 pg_typeof 
═══════════
 text
(1 row)

现在text和date之间没有赋值转换：

\dC date

                                      List of casts
         Source type         │         Target type         │  Function   │   Implicit?   
═════════════════════════════╪═════════════════════════════╪═════════════╪═══════════════
 date                        │ timestamp with time zone    │ timestamptz │ yes
 date                        │ timestamp without time zone │ timestamp   │ yes
 timestamp with time zone    │ date                        │ date        │ in assignment
 timestamp without time zone │ date                        │ date        │ in assignment
(4 rows)

因此，你得到你观察到的错误。
添加显式类型强制转换：

UPDATE ods2.project_portfolios 
SET portfolio_end_date = tmp.portfolio_end_date, ...
FROM (VALUES (CAST (NULL AS date), ...)) AS tmp (portfolio_end_date, ...)
WHERE ...;

赞(0）回复(0）举报 2023-06-05

我来回答

postgresql Postgre，类型为date，但表达式类型为text [重复]

1条答案

相关问题

热门标签

最新问答