Postgres GROUP BY id 直接在表上工作，但不在相同的視圖上

假設我有兩張桌子。其中一個以一對多的關係引用另一個：

CREATE TABLE t (
 id SERIAL PRIMARY KEY
, name text
);

INSERT INTO t (name) VALUES ('one'), ('two'), ('three');

CREATE TABLE y (
 id SERIAL PRIMARY KEY
, tid INTEGER REFERENCES t
, amount INTEGER
);

INSERT INTO y (tid, amount) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 1);

我可以從這些表中查詢數據，以獲取t與設置的特定條件匹配的行t和y：

SELECT row_to_json(t.*) as "t_nogroup" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1;

這產生

t_nogroup
=========
{"id":1,"name":"one"}
{"id":1,"name":"one"}

哪個有效，是的，但包含重複項。我可以使用以下方法過濾掉重複項GROUP BY：

SELECT row_to_json(t.*) as "t_group" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1
GROUP BY t.id;

製造：

t_group
=======
{"id":1,"name":"one"}

偉大的！但是，當我嘗試交換t和y非物化VIEWs 時：

CREATE VIEW vt AS SELECT t.* FROM t;
CREATE VIEW vy AS SELECT y.* FROM y;

VIEW並使用新的 s執行相同的查詢

SELECT row_to_json(vt.*) as "view_t_nogroup" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1;

SELECT row_to_json(vt.*) as "view_t_group" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1
GROUP BY vt.id;

然後我得到第二個查詢的錯誤：

ERROR: column "vt.*" must appear in the GROUP BY clause or be used in an aggregate function

為什麼會這樣？使用 table 時看到的任何邏輯*都應該適用於 view ，對嗎？id``t``vt

小提琴連結：http ://sqlfiddle.com/#!17/26b0e/5

我正在執行 PostgreSQL 9.6。我也知道這是一個人為的例子，但是我在一個非常相似的一對多關係中遇到了這個錯誤，其中GROUP BY需要多個語句，所以只使用DISTINCT不是一個選項，就像這個例子一樣。

出現問題是因為 Postgres 知道該表t具有(id)作為主鍵 - 所以我們可以在列表中使用GROUP BY t.id並擁有更多來自該表的SELECT列而不聚合它們 - 但它不能推斷出相同的 view vt。

對於提供的範例，您可以將它們重寫為根本不使用GROUP BY。由於您不需要選擇列表中第二個連接表中的任何列，您可以將其轉換JOIN為EXISTS子查詢，即將連接轉換為半連接。現在表和視圖半連接都可以正常工作：

SELECT row_to_json(t.*) as "t_group" FROM t
WHERE t.id = 1 
 AND EXISTS 
     ( SELECT 1 
       FROM y 
       WHERE t.id = y.tid
         AND y.amount > 1 
     ) ;

SELECT row_to_json(vt.*) as "t_group" FROM vt
WHERE vt.id = 1 
 AND EXISTS 
     ( SELECT 1 
       FROM vy 
       WHERE vt.id = vy.tid
         AND vy.amount > 1 
     ) ;

引用自：https://dba.stackexchange.com/questions/195104