В ожидании 9.1 - Распознавание функциональной зависимости от первичных ключей

Перевод Waiting for 9.1 – Recognize functional dependency on primary keys с select * from depesz;

Вчера (7 августа) Tom Lane применил:

Распознавание функциональной зависимости от первичных ключей. Это позволяет
колонкам не присутствовать в GROUP BY, если там присутствует первичный ключ.

В дальнейшем нам стоит также разрешить функциональную зависимость от UNIQUE
ограничений при условии, что колонка помечена как NOT NULL, но это будет ждать пока
NOT NULL ограничения не будут представлены в pg_constraint, т.к. нам будут нужны
pg_constraint OID-ы для всех условий, где будет разрешаться функциональная
зависимость.

Peter Eisentraut, проверено Alex Hunsaker и Tom Lane

Одна из наиболее частых проблем, с которой люди сталкиваются при переходе с MySQL на PostgreSQL, заключается вот в таких запросах:

SELECT field_a, field_b, count(*)
FROM TABLE
GROUP BY field_a

Это нормально для MySQL, но не работало в PostgreSQL.

Теперь, с новым патчем, это подмножество запросов будет работать и в PostgreSQL. Во первых, что это за подмножество? Всё просто - если field_a первичный ключ данной таблицы и мы группируем по нему, то очевидно, что мы не получим более чем одной строки и значения field_b для каждого из значений field_a. Но если это не первичный ключ, то может получиться что для каких-то значений field_a будет несколько значений field_b, и в этом случае запрос уже не подходит.

Рассмотрим пример:

CREATE TABLE people (
id serial PRIMARY KEY,
firstname TEXT,
lastname TEXT
);

CREATE TABLE visits (
id serial PRIMARY KEY,
person_id INT4 NOT NULL REFERENCES people (id),
when_logged timestamptz
);

INSERT INTO people (firstname, lastname)
VALUES
('Bill', 'Hicks'),
('George', 'Carlin'),
('Louis', 'C.K.'),
('Robin', 'Williams'),
('Zach', 'Galifianakis');

INSERT INTO visits (person_id, when_logged)
SELECT
1 + floor(random() * 5),
now() - '1 year'::INTERVAL * random()
FROM
generate_series(1,1000);

Теперь положим что надо получить список людей со счётчиками посещений. Ранее надо было делать так:

SELECT
p.id,
count(*)
FROM
people p
JOIN visits v ON p.id = v.person_id
GROUP BY p.id;

Или так:

SELECT
p.id,
p.firstname,
p.lastname,
count(*)
FROM
people p
JOIN visits v ON p.id = v.person_id
GROUP BY p.id, p.firstname, p.lastname;

(или трюкачить с min(firstname))

Но теперь можно сделать так:

SELECT
p.id,
p.firstname,
p.lastname,
count(*)
FROM
people p
JOIN visits v ON p.id = v.person_id
GROUP BY p.id;

И всё будет прекрасно работать.