INSERT a SELECT GROUP BY : more target columns than expressions 错误 [英] INSERT a SELECT GROUP BY : more target columns than expressions error

问题描述

我有一个查询，我想查询，它是一个 INSERT FROM a 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.

解决办法是去掉括号.

这篇关于INSERT a SELECT GROUP BY : more target columns than expressions 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！