INSERT a SELECT GROUP BY : more target columns than expressions error [duplicate]

Question

I have a query, that I want to make, it is an INSERT FROM a SELECT GROUP BY, but I get the error:

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?

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;

Skip ( ) enclosing the select list... (Which makes the select return one column, a row type one.) — jarlh
– jarlh, Commented Sep 18, 2019 at 12:38

Gordon Linoff · Accepted Answer · 2019-09-18 12:40:43Z

3

When you enclose expressions in parentheses, Postgres interprets the result as a tuple -- essentially a struct or record.

So, your statement:

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.

The solution is to remove the parentheses.

answered Sep 18, 2019 at 12:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hamza Ince Over a year ago

It was way simpler, than everything I tried, I just had no idea, it worked like that, thanks

Islingre · Accepted Answer · 2019-09-18 12:41:46Z

As the hint says: Did you accidentally use extra parentheses?

Remove the parentheses around the selected values and the problem is solved.

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;

Collectives™ on Stack Overflow

INSERT a SELECT GROUP BY : more target columns than expressions error [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related