Using PostgreSQL 11.18
It's possible to select rows from a table with a group by that only includes the primary key even if all of the columns appear in the select clause:
CREATE TABLE table1( id int NOT null PRIMARY KEY, column2 int, column3 int ); SELECT id, column2, SUM(column3) FROM table1 GROUP BY id;
That's because column2 has a functional dependency on id so it does not need to be included and column3 is used as a parameter to an aggregate function.
If you instead replace querying against the table with a view that's simply a select of all of the columns in the table:
CREATE VIEW view1 AS SELECT id, column2, column3 FROM table1; SELECT id, column2, SUM(column3) FROM view1 GROUP BY id;
It'll fail with:
ERROR: column "view1.column2" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT id, column2, SUM(column3) ^ SQL state: 42803 Character: 12
So if you're replacing direct access to tables with views and there's code that omits the additional columns in the group by - it'll fail. Why is this? Is this a bug or does the notion of a primary not apply to views?