Why can't I select un-grouped columns when grouping by a (non-primary) unique key?

From: Daniel Lenski <dlenski(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Why can't I select un-grouped columns when grouping by a (non-primary) unique key?
Date: 2014-09-24 16:04:21
Message-ID: CAOw_LSEzXc_pFk7W5bTRQ+6qy0wG8g0yX2s+i8TY1HF-LbZP3Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

CREATE TABLE A (id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL,
document JSON);

-- this works fine
SELECT A.document
FROM A
GROUP BY A.primary_key

Why doesn't the same thing work with a non-NULL unique constraint?

-- ERROR: column "A.document" must appear in the GROUP BY clause or be
used in an aggregate function
SELECT A.document
FROM A
GROUP BY A.name

I got thinking about this distinction because I wrote some very ugly SQL in
a few cases, to get around the lack of JSON comparison operators in PG 9.3,
before I discovered that it would work if I used the PRIMARY KEY instead:

-- this works but it's ugly
SELECT A.document::text::json
FROM table
GROUP BY A.non_null_unique_key, A.document::text

The manual refers to this situation (
http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-GROUPBY), but
I don't understand whether there's a specific reason to distinguish primary
keys from non-NULL unique constraints.

Thanks,
Dan Lenski

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ilya I. Ashchepkov 2014-09-24 16:23:48 Re: JSONB spaces in text presentation
Previous Message Geoff Winkless 2014-09-24 15:25:08 Re: [ADMIN] readonly user