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

postgresqlview

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?

Best Answer

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?

Related Solutions

Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

seharusnya seperti ini(supposed to be like this):

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;

PostgreSQL Group By – Why Select All Fields When Grouping by Primary Key but Not Another Column

id is a primary key.
As far as I remember, this is actually a legal query according to ANSI/ISO SQL.
Grouping by primary key results in a single record in each group which is logically the same as not grouping at all / grouping by all columns, therefore we can select all other columns.

create table t (id int primary key,c1 int,c2 int)
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 1  | 2  | 3  |
+----+----+----+
| 4  | 5  | 6  |
+----+----+----+

Reference given by @a_horse_with_no_name

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

While logically we would expect UNIQUE NOT NULL to follow the same behaviour, it applies only for PK (as described in the documentation)

create table t (id int unique not null,c1 int,c2 int);
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

[Code: 0, SQL State: 42803] ERROR: column "t.c1" must appear in the GROUP BY clause or be used in an aggregate function

Best Answer

Related Solutions

Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

PostgreSQL Group By – Why Select All Fields When Grouping by Primary Key but Not Another Column

Related Question