postgresql 列“statement_date”是日期类型，但表达式是整数类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9209872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 23:18:47 来源:igfitidea点击:
column "statement_date" is of type date but expression is of type integer
提问by Neil Middleton
Consider the following query:
考虑以下查询：
INSERT INTO statement_line_items
SELECT count(*)::integer as clicks, sum(amount_cents)::integer as amount_cents, imps.user_id, imps.created_at::date as statement_date
FROM impression_events imps
INNER JOIN transactions t ON t.event_id = imps.id
AND t.event_type = 'ImpressionEvent'
AND amount_cents >= 0
WHERE imps.created_at >= (now() - interval '8 days')::date
AND imps.created_at < (now() - interval '7 day')::date
AND imps.clicked = true
GROUP BY imps.user_id, imps.created_at::date;
This is returning:
这是返回：
ERROR: column "statement_date" is of type date but expression is of type integer
LINE 2: ...icks, sum(amount_cents)::integer as amount_cents, imps.user_...
^
HINT: You will need to rewrite or cast the expression.
********** Error **********
ERROR: column "statement_date" is of type date but expression is of type integer
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 117
My table structure for statement_line_items is:
我的 statement_line_items 表结构是：
"id"; "integer"
"user_id"; "integer"
"statement_date"; "date"
"description"; "character varying(255)"
"clicks"; "integer"
"amount_cents"; "integer"
"created_at"; "timestamp without time zone"
"updated_at"; "timestamp without time zone"
采纳答案by juergen d
You are putting the imps.useridin your statement_datecolumn. That has to fail.
你把 放在imps.userid你的statement_date专栏里。那必须失败。
count(*)::integer as clicks goes into id
sum(amount_cents)::integer as amount_cents goes into userid
imps.user_id goes into statement_date
To specify the order you are inserting you can do this:
要指定您插入的顺序，您可以执行以下操作：
INSERT INTO statement_line_items (col1, col2, ...)
values (select data_for_col1, data_for_col2, ...)
