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?
