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

Поиск
Список
Период
Сортировка
От Daniel Lenski
Тема Why can't I select un-grouped columns when grouping by a (non-primary) unique key?
Дата
Msg-id CAOw_LSEzXc_pFk7W5bTRQ+6qy0wG8g0yX2s+i8TY1HF-LbZP3Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Why can't I select un-grouped columns when grouping by a (non-primary) unique key?
Список 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

В списке pgsql-general по дате отправления: