From: | Geoff Montee <geoff(dot)montee(at)gmail(dot)com> |
---|---|
To: | Alberto Cabello Sánchez <alberto(at)unex(dot)es> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Why can't I select un-grouped columns when grouping by a (non-primary) unique key? |
Date: | 2014-09-24 17:46:48 |
Message-ID: | CAA7biFMG79PnC6HzDnuChtC56fgaXfLKP8wgsJC_amqn8G1QYQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Sep 24, 2014 at 1:37 PM, Alberto Cabello Sánchez <alberto(at)unex(dot)es>
wrote:
>
> On Wed, 24 Sep 2014 09:04:21 -0700
> Daniel Lenski <dlenski(at)gmail(dot)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;
Geoff
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Dodd | 2014-09-24 17:52:17 | Transaction completion timing |
Previous Message | Alberto Cabello Sánchez | 2014-09-24 17:37:10 | Re: Why can't I select un-grouped columns when grouping by a (non-primary) unique key? |