CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Dmitriy Apollonin
Dmitriy Apollonin

Reputation: 1478

PostgreSQL group by without aggregate function. Why does it work?

there is a strange behavior. I know that when using GROUP BY, all columns from SELECT should either be in group by clause, or have aggregate function. However, the next code shows something controversial:

// id is a primary key
select id, name from user group by name; <-- error because of group by (expected)
select id, name from user group by id; <-- works (unexpected!)

What am I missing? Can not find documentation that says about some potential special case for primary key in group by.

Upvotes: 6

Views: 2616

Answers (2)

Thomas B
Thomas B

Reputation: 311

Your primary key can guaranty unicity for the id column.

I tried your statement on PostgreSQL 11.6 and 12.1 and I got the same error

SELECT id, name from unicity group by id;
ERROR:  column "unicity.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT id, name from unicity group by id;

Which version of PostgreSQL are you using?

Upvotes: 2

Jeremy
Jeremy

Reputation: 6723

This is covered, but not especially obvious, in the docs:

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.

In this case, I'm guessing that id is the primary key of the table user which would make name functionally dependent on id.

Upvotes: 10

Related Questions