Re: Why can't I select un-grouped columns when grouping by a (non-primary) unique key? - Mailing list pgsql-general

On Wed, Sep 24, 2014 at 1:37 PM, Alberto Cabello Sánchez <alberto@unex.es> wrote:
>
> On Wed, 24 Sep 2014 09:04:21 -0700
> Daniel Lenski <dlenski@gmail.com> wrote:
>
> > If I include the primary key of a table in my GROUP BY clause, PG 9.3
> > allows me to refer to other columns of that table without explicit GROUP BY:
> >
> > Why doesn't the same thing work with a non-NULL unique constraint?
>
> At first sight, primary key means no grouping at all, as there are no
> duplicated A.primary_key values:
>
>   SELECT A.document
>     FROM A
>     GROUP BY A.primary_key
>
> is the same as
>
>   SELECT A.document
>     FROM A

I believe this blog post contains better examples of the feature he's referring to:

http://www.depesz.com/2010/08/08/waiting-for-9-1-recognize-functional-dependency-on-primary-keys/

For example:

SELECT
    p.id,
    p.firstname,
    p.lastname,
    count(*)
FROM
    people p
    JOIN visits v on p.id = v.person_id
GROUP BY p.id;