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:
