Postgres cannot simulate table with a view: column must appear in the GROUP BY clause or be used in an aggregate function

Question

I am trying to replace a table in our application by using a View. It works well for the most part, but I can't get past this error: "column must appear in the GROUP BY clause or be used in an aggregate function"

Steps to reproduce:

CREATE TABLE example_t (
    did serial PRIMARY KEY,
    a text,
    b text
);
INSERT INTO example_t(a, b) VALUES ('a', 'b');

CREATE VIEW example_t_v AS 
    SELECT t.did as did, t.a as a, t.b as b
    FROM example_t t;

Now this query works fine:

SELECT t.a, t.b FROM example_t t GROUP BY (t.did);

But an identical query from the view fails:

SELECT t.a, t.b FROM example_t_v t GROUP BY (t.did);

ERROR:  column "t.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1:   SELECT t.a, t.b FROM example_t_v t GROUP BY (t.did);

It would be pretty hard to rewrite the application layer (since Django is generating the queries and it likes to use GROUP BY a lot) so is there a solution on the db layer?

Answer 1

Postgres is smart enough to know in the TABLE that your group-by column is UNIQUE (PRIMARY KEY) ... grouping by a column that is UNIQUE is pointless, so postgres just gives you back what you ask for (column values a and b)..

However, Postgres does NOT know that the group-by column in the VIEW is UNIQUE.. Thus, it's confused by your query (as well as most humans would be confused by your query - including myself).

FYI - your query makes no sense. Why would you group by a column when you apparently have no interest in performing an aggregate? What would you expect a and b to give you?