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
