4

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?

1 Answer 1

8

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?

5
  • 1
    Is there any way to tell Postgres "look this column really is a PK of this view"? The examples here are convoluted, sure, but I am in the same position now and the problem is that the source table has a lot of columns that I'd have to explicitly list in the GROUP BY...
    – vektor
    Feb 8, 2018 at 13:51
  • @vektor Not that I'm aware of, unless Postgres has something analogous to Oracle's Materialized Views. (I haven't used postgres in a few years, now - I think it does, but I'm not sure what they're called) Feb 8, 2018 at 15:31
  • Materialized view would bring a ton of other problems. I was able to rewrite my view around this problem after all.
    – vektor
    Feb 8, 2018 at 19:53
  • @vektor did you find a way to tell the Postgres "look this column really is a PK of this view"? If yes, can you please share the findings? Dec 2, 2018 at 18:11
  • Even a materialized view with a unique index still needs to add all columns in group by, sadly
    – talski
    Oct 2 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.