sql - 为什么 Postgresql 不允许在 INSERT SELECT 查询中分组集？

Question

这里的问题就这么简单，Postgresql 不允许下面的查询结构:

-- 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.

我认为很明显 CUBE 在指示为分组集的每个字段上产生 null 结果(因为它计算每个可能的组合)，因此我不能插入那个我的 table_cube 表中的行，那么 Postgres 是否假设我正在尝试在具有 PK 字段的表中插入一行？即使table_cube表没有PK，也无法实现。

谢谢。

版本: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.