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

Question

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

Answer 1

You have define table_cube(id) as Primary Key. So, If Cube contains null values, it can't be inserted. I have checked without having id as Primary Key, It works fine and when define id as primary key I got error:

"ERROR:id contains null values"  SQL state: 23502   

As suggested by Haleemur Ali,

  "If a constraint is required, use a unique index with all the grouping 
  set columns: CREATE UNIQUE INDEX unq_table_cube_id_description_time ON 
  table_cube(id, description, time); Please update your question with more 
  information on database & version."

is a good option. But you have to remove Primary Key On "Id" and assign only Unique Key as suggested above as with having primary key and unique key again get this error:

   ERROR:  null value in column "id" violates not-null constraint
   DETAIL:  Failing row contains (null, null, 1300, 1522, null).
   SQL state: 23502

So, the conclusion is, with unique index there is no need of Primary Key or with cube there is no need of unique index or Primary Key.