Полезные трюки PostgreSQL

generate_series postgresql unnest CTE

Отбор идентичных записей

Предположим, надо получить строки из таблицы, которых нет в другой точно такой же таблице, причем с проверкой всех полей на идентичность.
Традиционно можно было бы написать так (предположим, в таблице 3 поля):

Copy
SELECT t1.* 
FROM table1 t1
    LEFT JOIN table2 t2
        ON t1.field1 = t2.field1 
            AND t1.field2 = t2.field2
            AND t1.field3 = t2.field3
WHERE 
    t2.field1 IS NULL;

Слишком многословно и зависит от конкретных полей.
В посгресе же можно использовать тип Record. Получить его из таблицы можно используя само название таблицы. 

Copy
postgres=# SELECT table1 FROM table1;
 table1  
---------
 (1,2,3)
 (2,3,4):q

Теперь, можем выбрать строки с идентичными полями:

Copy
SELECT table1.* 
    FROM table1 
        LEFT JOIN table2
            ON table1 = table2
WHERE 
    table2 Is NULL;

Или такая, более понятная версия запроса:

Copy
SELECT * 
FROM table1 
WHERE NOT EXISTS (
    SELECT * 
    FROM table2 
    WHERE
        table2 = table1
);

Тип данных - массив

Очень жизненная задача. Приходит письмо “Вставь, пожалуйста, для юзеров 100, 110, 153, 100500 такие-то данные”.
Т.е. надо вставить несколько строк, где id разные, а остальное одинаковое.

Делаем традиционным подходом:

Copy
INSERT INTO important_user_table
(id, date_added, status_id)
VALUES
(100, '2015-01-01', 3),
(110, '2015-01-01', 3),
(153, '2015-01-01', 3),
(100500, '2015-01-01', 3);

Если строк много, то это становится слишком сложно.
Для решения подобных проблем в посгресе есть тип данных “массив”, а также функция unnest, которая из массива делает строки с данными.

Например:

Copy
postgres=# select unnest(array[1,2,3]) as id;
 id 
----
  1
  2
  3
(3 rows)

С помощью этого механизма мы можем написать так:

Copy
INSERT INTO important_user_table 
(id, date_added, status_id)
SELECT
     unnest(array[100, 110, 153, 100500]), '2015-01-01', 3;

т.е. список id идет одной строкой, что гораздо удобнее.

Если вам наоборот нужен массив из запроса, то для этого есть функция, которая так и называется — array(). Например, select array(select id from important_user_table);

Обработка ключей в запросах

Для схожих целей можно использовать еще один трюк. Мало кто знает, что синтаксис

Copy
VALUES (1, 'one'), (2, 'two'), (3, 'three')

можно использовать не только в INSERT запросах, но и в SELECT, надо только в скобочки взять:

Copy
SELECT * FROM (
    VALUES (1, 'one'), (2, 'two'), (3, 'three')
) as t (digit_number, string_number);
 digit_number | string_number 
--------------+---------------
            1 | one
            2 | two
            3 | three
(3 rows)

Очень удобно для обработки пар значений.

CTE

Допустим, вам нужно что-то вставить, изменить и получить id затронутых записей. Чтобы сделать это, не обязательно делать много запросов и создавать временные таблицы. Достаточно всё это запихать в CTE.

Copy
WITH 
updated AS (
       UPDATE table1 
        SET x = 5, y = 6
        WHERE z > 7
        RETURNING id
),
inserted AS (
       INSERT INTO table2
        (x, y, z)
        VALUES 
        (5, 7, 10)
        RETURNING id
)
SELECT id 
FROM updatedUNIONSELECT id 
FROM inserted;

Но будьте очень внимательны. Все подвыражения CTE выполняеются параллельно друг с другом, и их последовательность никак не определена. Более того, они используют одну и ту же версию (snapshot), т.е. если в одном выражении вы прибавили что-то к полю таблицы, а в другом вычли, то вы можете получить непредсказуемый результат.

Серии generate_series

Допустим в таблице stats есть данные только за один день:

Copy
postgres=# select * from stats;
  added_at  | money  
------------+--------
 2016-04-04 | 100.00
(1 row)

А вам надо вывести стату за какой-то период, заменив отсутствующие данные нулями. Это можно сделать с помощью generate_series

Copy
SELECT gs.added_at, coalesce(stats.money, 0.00) as money
FROM
    generate_series('2016-04-01'::date, '2016-04-07'::date , interval '1 day') as gs(added_at) 
    LEFT JOIN stats 
        ON stats.added_at = gs.added_at;

        added_at        | money  
------------------------+--------
 2016-04-01 00:00:00+03 |   0.00
 2016-04-02 00:00:00+03 |   0.00
 2016-04-03 00:00:00+03 |   0.00
 2016-04-04 00:00:00+03 | 100.00
 2016-04-05 00:00:00+03 |   0.00
 2016-04-06 00:00:00+03 |   0.00
 2016-04-07 00:00:00+03 |   0.00
(7 rows)

Разумеется, этот трюк работает не только с датами, но и с числами. Причем можно использовать несколько generate_series в одном запросе:

Copy
select  generate_series (1,10), generate_series(1,2);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
               3 |               1
               4 |               2
               5 |               1
               6 |               2
               7 |               1
               8 |               2
               9 |               1
              10 |               2
(10 rows)

Источник на habrahabr.ru