Why Postgresql doesn't allow grouping sets in INSERT SELECT queries?

Viewed 267

The issue here is simple as that, Postgresql doesn't allow the following query structure:

-- TABLE OF FACTS
CREATE TABLE facts_table (
    id integer NOT NULL,
    description CHARACTER VARYING(50),
    amount NUMERIC(12,2) DEFAULT 0,
    quantity INTEGER,
    detail_1 CHARACTER VARYING(50),
    detail_2 CHARACTER VARYING(50),
    detail_3 CHARACTER VARYING(50),
    time TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT LOCALTIMESTAMP(0)
);
ALTER TABLE facts_table ADD PRIMARY KEY(id);

-- SUMMARIZED TABLE
CREATE TABLE table_cube (
    id INTEGER,
    description CHARACTER VARYING(50),
    amount NUMERIC(12,2) DEFAULT 0,
    quantity INTEGER,
    time TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT LOCALTIMESTAMP(0)
);
ALTER TABLE table_cube ADD PRIMARY KEY(id);

INSERT INTO table_cube(id, description, amount, quantity, time)
    SELECT
        id,
        description,
        SUM(amount) AS amount,
        SUM(quantity) AS quantity,
        time
    FROM facts_table
    GROUP BY CUBE(id, description, time);
----------------------------------------------------------------
ERROR: grouping sets are not allowed in INSERT SELECT queries.

I think it's pretty obvious that CUBE produces null results on every field indicated as a grouping set (as it computes every possible combination), therefore I can not insert that row in my table_cube table, so , does Postgres just assume, that I'm trying to insert a row in a table with a PK field? Even if the table_cube table doesn't have a PK, this cannot be accomplished.

Thanks.

Version: PostgreSQL 9.6

1 Answers
Related