插入SELECT GROUP BY:目标列多于表达式错误 [英] INSERT a SELECT GROUP BY : more target columns than expressions error
问题描述
我有一个要查询的查询,它是从SELECT GROUP BY插入的,但出现错误:
I have a query, that I want to make, it is an INSERT FROM a SELECT GROUP BY, but I get the error:
错误:INSERT具有比表达式更多的目标列
第15行:插入"KPI_MEASURE"(id,created_at,kpi_project_id,k ...
_____________________________________ ^
提示:插入源是一个行表达式,包含与INSERT期望的列数相同的列.您是否不小心使用了多余的括号?
ERROR: INSERT has more target columns than expressions
LINE 15: INSERT INTO "KPI_MEASURE" (id, created_at, kpi_project_id, k...
_____________________________________^
HINT: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?
我已经搜索了此错误,但是我发现的是,如果行数不匹配,则会发生此错误,但是对于下面的查询,行数是相同的.
Postgres SQL查询:
I've searched this error, but what I found is that, this error happen, if the number of rows doesn't match, but for the query below, there is the same number of row.
Postgres SQL query:
INSERT INTO "KPI_MEASURE" (
id,
created_at,
kpi_project_id,
kpi_frequency_id,
kpi_metric_id,
branch,
value
)
SELECT (
nextval('"KPI_MEASURE_ID_seq"'::regclass),
now(),
kpi_project.id,
kpi_measure.kpi_frequency_id,
kpi_metric.id ,
kpi_measure.branch ,
sum(kpi_measure.value)
)
FROM "KPI_MEASURE" kpi_measure
INNER JOIN "KPI_METRIC" kpi_metric ON kpi_measure.kpi_metric_id = kpi_metric.id
INNER JOIN "KPI_PROJECT" kpi_project ON kpi_measure.kpi_project_id = kpi_project.id
INNER JOIN "KPI_AGGREGATION_PROJECT" kpi_agg_project ON kpi_project.name = kpi_agg_project.child_project_name
WHERE kpi_metric.aggregated = false
GROUP BY kpi_measure.branch, kpi_metric.id, kpi_project.id, kpi_project.name, kpi_frequency_id;
推荐答案
将表达式括在括号中时,Postgres会将结果解释为元组-本质上是结构或记录.
When you enclose expressions in parentheses, Postgres interprets the result as a tuple -- essentially a struct or record.
因此,您的声明:
SELECT (
nextval('"KPI_MEASURE_ID_seq"'::regclass),
now(),
kpi_project.id,
kpi_measure.kpi_frequency_id,
kpi_metric.id ,
kpi_measure.branch ,
sum(kpi_measure.value)
)
返回一个值.该值是一条记录.
is returning one value. That value is a record.
不支持元组的数据库将返回错误.
Databases that do not support tuples would return an error.
解决方案是删除括号.
这篇关于插入SELECT GROUP BY:目标列多于表达式错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!