GrAndSE's blog

GROUP_CONCAT та інші дрібнички в PostgreSQL

Не вперше я хочу написати про БД PostgreSQL. Цього разу хочу розповісти про декілька дрібничок, що можуть стати неприємною несподіванкою для людей, що довгий час працювали з MySQL та зіпсувати враження від цієї чудової БД. І ось такі дрібнички часто є причиною того, що PostgreSQL вважається деякими програмістами системою значно складнішою в роботі за MySQL. Особисто я з цим не можу погодитись - просто треба знати, як і що зробити саме тут.

Ну що ж. Розпочнемо з простого, а там буде видно

Auto increment

Всі хто працював з MySQL звикли до того, що всі цілочислені поля можуть заповнуватись автоматично по порядку, якщо до поля додати атрибут auto_increment. Наприклад так:

CREATE TABLE items (
id INTEGER AUTO_INCREMENT;
);


В PostgreSQL для створення таких полів присутній спеціальний тип serial.

CREATE TABLE items (
id SERIAL
);


Насправді така дія для PostgreSQL означає:

1. Створити послідовність (sequence) items_id_seq.
2. Створити поля id з типом integer.
3. Встановити значення поля id за замовчуванням рівним nextval('items_id_seq'::regclass)

Що можна записати у вигляді sql-коду:

CREATE SEQUENCE items_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

CREATE TABLE items (
id integer DEFAULT nextval('items_id_seq'::regclass)
);


Доречі, PostgreSQL створює sequences не лише для типу serial і користуватись можна не лише для того, щоб створити auto increment поле.

Id останнього доданого елемента

Програмісти, що довгий час працюють з MySQL звикли до того, що отримати значення поля з атрибутом auto_increment можна одним запитом: SELECT LAST_INSERT_ID();. А через через php за допомогою функції mysql_insert_id(). Аналогічна функція в PostgreSQL відсутня (так само як і функція для того ж в php).

Однак тут на допомогу прийдуть знову ж таки послідовності:

SELECT currval('items_id_seq'::regclass);


чи вибравси поле last_value з таблиці, що выдповідає за послідовність:

SELECT last_value FROM items_id_seq


Дата та час

Робота з датою та часом в PostgreSQL - досить серйозна тема, якій можна присвятити окрему статтю. Тому я зверну увагу лише на тому, чим відрізняється робота з датою та часом від роботи в MySQL. Почну з типів. Тут все практично однаково: є три типи основні типи DATE, TIME та збірний тип TIMESTAMP (аналогом йому в MySQL слугує DATETIME). Однак будь-який програміст, що досить довго працював з MySQL спитає, а як зробити поле, яке при будь-якій зміні данних автоматично прийматиме значення дати та часу останньої модифікації?

Для таких цілей слугують трігери, що з'явився тільки в MySQL 5, чим і можна пояснити наявність в ньому типу TIMESTAMP. В PostgreSQL аналогічного ефекту можна досягнути створивши трігер-функцію:

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS trigger AS
$BODY$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;


Тепер можна створити таблицю з полем modified та трігером на update (що буде виконуватись після внесення змін до таблиці), який буде викликати описану вище функцію:

CREATE TABLE items (
id SERIAL,
created TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
modified TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
)

CREATE TRIGGER update_items
AFTER UPDATE
ON items
FOR EACH ROW
EXECUTE PROCEDURE update_modified_column();


І знову ж дозволю собі нагадати, що обсласт ьвикористання трігерів значно ширша за таку просту задачу, як оновлення полів для роботи з датою.

GROUP_CONCAT

Дуже популярна тема. Всі програмісти, що приходять з MySQL раніше чи пізніше зтикаються з необхідністю згрупувати декілька елементів за якимось полем. Наприклад є така таблиця:

CREATE TABLE items (
id SERIAL,
title VARCHAR(255),
user_id INTEGER
)


В MySQL можна було б записати запит для агрегації всих полів за user_id так:

SELECT user_id, GROUP_CONCAT(id), GROUP_CONCAT(title) FROM items GROUP BY user_id;


Однак, одразу в PostgreSQL такий результат отримати не можна. В документації можна знайти таке рішення, що реалізуэться за допомогою агрегаторів:

create aggregate array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);


Однак такий агрегатор повертає результат у вигляді масиву, що лякає тиххто раніше не працював з таким різноманіттям типів в рамках БД. Тому можна створити іншу функцію-агрегатор, що буде збирати значення в один рядок та матиме можливість видозмінювати розділювач між елементами:

CREATE FUNCTION _group_concat(text, text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) $3 operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat(text, text) (
SFUNC = _group_concat,
STYPE = text
);


Вище описано функцію _group_concat(), що вионує конкатенацю двох елементів, що були передані у вигляді параметрів. Причому символом-розділювачем є значеня вказане у якості третього параметру. Цю функцію використовує агрегатор group_concat. Єдиним, що може викликати дискомфорт при роботі є необхідність дотримуватись типів полів, а отже для нормальної агрегації потрібно використовувати зведення типів:

SELECT user_id, GROUP_CONCAT(CAST(id AS text), ','), GROUP_CONCAT(title, ';') FROM items GROUP BY user_id;


Синтаксис зовсім трошки відрізняється від аналогічного в MySQL. Отже не такий страшний PostgreSQL про нього думають. :) Хоча для любителів використовувати SQL запити без агрегаторів та додаткових функцій є інший шлях.

Строгість та широкі можливості

PostgreSQL як можна помітити вимагає трошки більше уваги та дій для роботи. Однак це не є обузою, оскільки можливості він має ширші, структура є логічною, прозорою та уніфікованою. Мені здається, що строгість дозволяє зменшити кількість помилок ціною незначного збільшення часу на написання запитів. Сподіваюсь, що цим невеличким матеріалом я зможу зберегти комусь час та нерви.

Хочу ще неодноразово повернутись до теми обговорення PostgreSQL та його порівняння з іншими БД. Дякую за увагу!