How is this a valid statement (where id is the primary key of the table):
select * from table group by id ;
and this is not:
select * from table group by name ;
ERROR: column "pgluser.id" must appear in the GROUP BY clause or be used in an aggregate function
The question is why is the first a legal query, ie why grouping by primary key is valid?
Best Answer
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.
Reference given by @a_horse_with_no_name
https://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY
While logically we would expect UNIQUE NOT NULL to follow the same behaviour, it applies only for PK (as described in the documentation)