英文:
Postgre, is of type date but expression is of type text
问题
I have error
错误: 列 "portfolio_end_date" 的类型为日期，但表达式的类型为文本
第 5 行: portfolio_end_date = tmp.portfolio_end_date,
^
提示: 您需要重新编写或转换表达式。
SQL 状态: 42804
字符: 186
为什么PostgreSQL返回错误
UPDATE ods2.project_portfolios as tblset 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_timefrom (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';
请求失败，尽管我看不到任何错误。
英文:
i have error
ERROR: column "portfolio_end_date" is of type date but expression is of type text
LINE 5: portfolio_end_date = tmp.portfolio_end_date,
^
HINT: You will need to rewrite or cast the expression.
SQL state: 42804
Character: 186
Why potgre retrun error
UPDATE ods2.project_portfolios as tblset 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_timefrom (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';
Request fails although I don't see any errors in it
答案1
得分: 0
If an element in a VALUES statement is not a number and is not explicitly cast to a different data type, it will resolve as type text:
SELECT pg_typeof(col)FROM (VALUES (NULL)) AS v(col);pg_typeof═══════════text(1 row)
Now there is no assignment cast between text and date:
\dC dateList of castsSource type │ Target type │ Function │ Implicit?═════════════════════════════╪═════════════════════════════╪═════════════╪═══════════════date │ timestamp with time zone │ timestamptz │ yesdate │ timestamp without time zone │ timestamp │ yestimestamp with time zone │ date │ date │ in assignmenttimestamp without time zone │ date │ date │ in assignment(4 rows)
Therefore you get the error you observe.
Add an explicit type cast:
UPDATE ods2.project_portfoliosSET portfolio_end_date = tmp.portfolio_end_date, ...FROM (VALUES (CAST (NULL AS date), ...)) AS tmp (portfolio_end_date, ...)WHERE ...;
英文:
If an element in a VALUES statement is not a number and is not explicitly cast to a different data type, it will resolve as type text:
SELECT pg_typeof(col)FROM (VALUES (NULL)) AS v(col);pg_typeof═══════════text(1 row)
Now there is no assignment cast between text and date:
\dC dateList of castsSource type │ Target type │ Function │ Implicit?═════════════════════════════╪═════════════════════════════╪═════════════╪═══════════════date │ timestamp with time zone │ timestamptz │ yesdate │ timestamp without time zone │ timestamp │ yestimestamp with time zone │ date │ date │ in assignmenttimestamp without time zone │ date │ date │ in assignment(4 rows)
Therefore you get the error you observe.
Add an explicit type cast:
UPDATE ods2.project_portfoliosSET portfolio_end_date = tmp.portfolio_end_date, ...FROM (VALUES (CAST (NULL AS date), ...)) AS tmp (portfolio_end_date, ...)WHERE ...;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论