06.04.2016 04:27
varanio 28 23100 Источник


В мануале есть всё. Но чтобы его целиком прочитать и осознать, можно потратить годы. Поэтому один из самых эффективных методов обучения новым возможностям Postgres — это посмотреть, как делают коллеги. На конкретных примерах. Эта статья может быть интересна тем, кто хочет глубже использовать возможности postgres или рассматривает переход на эту СУБД.


Пример 1


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

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

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. Получить его из таблицы можно используя само название таблицы.

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


(Выведет в скобочках)

Теперь, наконец отфильтруем строки с идентичными полями

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


или чуть более читабельно:

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


Пример 2


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

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);


Если id много, то это слегка напрягает. Кроме того, у меня аллергия на дублирование кода.

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

Например 

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


Т.е. в нашем примере мы можем написать так

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);

Пример 3


Для схожих целей можно использовать еще один трюк. Мало кто знает, что синтаксис 
VALUES (1, 'one'), (2, 'two'), (3, 'three')

можно использовать не только в INSERT запросах, но и в SELECT, надо только в скобочки взять
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)


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

Пример 4


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

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 updated
UNION
SELECT id 
FROM inserted;

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

Пример 5



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

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


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

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 в одном запросе:
teasernet_maindb=> 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)

Пример n+1


Вообще, я пишу статьи на хабр, чтобы получить немного нового опыта из коментов )
Пожалуйста, напишите, что вы используете в повседневной работе. Что-нибудь такое, что возможно не для всех очевидно, особенно для людей, переехавших с других СУБД, например, с того же mysql?

Комментарии (28)


  1. printercu
    06.04.2016 08:03
    #8841830
    +1

    В первом примере почему сразу по PK не выбирать с NOT EXISTS? Такое и в других базах будет работать.


    1. varanio
      06.04.2016 08:11
      #8841846
      +4

      Если ситуация позволяет, то конечно можно. Но мне приходилось столкнуться на практике, когда надо было сравнить именно сами данные. Т.е. есть одна таблица, есть другая, новая, с той же структурой. И надо было сравнить, что именно изменилось, какие данные в каких строках. Т.е. с одинаковыми pk могли быть разные значения других полей


      1. ComodoHacker
        06.04.2016 11:44
        #8842388
        +1

        Эх, если бы Postgres поддерживал MINUS…

        UPD. О, оказывается поддерживает, только называется EXCEPT. Могли бы и алиас сделать.


        1. bolk
          06.04.2016 18:01
          #8843716
          +5

          UPD. О, оказывается поддерживает, только называется EXCEPT. Могли бы и алиас сделать.
          У всех, кроме «Оракла», это называется EXCEPT.


    1. myz0ne
      06.04.2016 10:42
      #8842166
      +4

      Есть же Except/intersect. www.postgresql.org/docs/7.4/static/sql-select.html#SQL-EXCEPT причём работает и в других базах.


      1. varanio
        06.04.2016 10:43
        #8842170

        Спасибо, не знал. Ну, значит не зря написал статью )


      1. AlterEgo20
        06.04.2016 13:06
        #8842662

        Если ссылаетесь на мануал постгрес, лучше использовать ссылку на текущуб версию:
        www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT

        Версия 7.4 слишком уж древняя.


  1. zloyreznic
    06.04.2016 09:27
    #8841934
    +1

    generate_series очень помогло


  1. bolk
    06.04.2016 09:39
    #8841958
    +7

    VALUES можно использовать везде, где и SELECT, очень помогает для работы с кучей значений. Например, для расшифровки численных значений. Что-то типа:

    WITH sv(sex, value) AS (
     VALUES(0, 'мужской'), (1, 'женский'), (2, 'не знаю')
)
SELECT fullname, sv.value FROM "user" INNER JOIN sv USING(sex)


    1. kshvakov
      06.04.2016 10:42
      #8842164
      +1

      Действительно, или так:

      with recursive hw(_array, i, r) as (

    values (array['H', 'e', 'l', 'l', 'o', ',', ' ', 'w', 'o', 'r', 'l', 'd', '!'], 1, '')
    union all
    select _array, i + 1, r || _array[i] from hw where i <= array_length(_array, 1)
)
select r as result from hw order by i desc limit 1;


      чтоб уж совсем прочувствовать всю мощь )


    1. kast218
      12.04.2016 11:39
      #8849684
      +1

      А чем это лучше CASE (который работает везде)?

      SELECT fullname
     , CASE WHEN value = 0 THEN 'мужской'
            WHEN value = 1 THEN 'женский'
            WHEN value = 2 THEN 'вы в толерантной стране'
       ELSE 1 -- что-то мы не все предусмотрели
       END sex_ru
FROM "user"


      1. bolk
        12.04.2016 11:41
        #8849688

        Тем, что в запросе может быть несколько таких мест, а указать можно только в одном.


        1. kast218
          12.04.2016 11:44
          #8849696

          Так в исходном запросе для этого и используется CTE, что мешает перенести CASE в него?


          1. bolk
            12.04.2016 11:46
            #8849698

            Исходный запрос — это всего лишь упрощённые пример, чтобы показать суть. Даже странно такие вещи растолковывать.


  1. tamakio
    06.04.2016 10:08
    #8842012
    +2

    Оператор Except имхо более подходящий для примера 1.
    И работать должен во всех БД


    1. varanio
      06.04.2016 10:44
      #8842172

      Ага, спасибо


      1. tamakio
        06.04.2016 10:47
        #8842190
        -2

        Я счас после пива и БД нет под рукой, но
        Select * from table1
        Where f1, f2, f3 not in ( select * from table2)
        Тоже должно работать, если во второй таблице соответствующие поля


    1. potapuff
      06.04.2016 10:53
      #8842230

      В Oracle вместо него используется MINUS. Работает так же.


  1. ALHIMIK1992
    06.04.2016 10:42
    #8842162

    В функциях если входной параметр может быть NULL
    CREATE OR REPLACE FUNCTION get_text (date)
    RETURNS text AS
    $BODY$
    DECLARE
    on_date alias for $1;
    rec text;
    BEGIN
    IF on_date IS NULL THEN
    FOR rec IN SELECT some_text
    FROM table_with_text
    LOOP
    RETURN NEXT rec;
    END LOOP;
    ELSE
    FOR rec IN SELECT some_text
    FROM table_with_text
    WHERE date_text = on_date
    LOOP
    RETURN NEXT rec;
    END LOOP;
    END IF;
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE;
    Можно проверить на NULL сразу в WHERE
    CREATE OR REPLACE FUNCTION get_text (date)
    RETURNS text AS
    $BODY$
    DECLARE
    on_date alias for $1;
    rec text;
    BEGIN
    FOR rec IN SELECT some_text
    FROM table_with_text
    WHERE (on_date IS NULL OR date_text = on_date)
    LOOP
    RETURN NEXT rec;
    END LOOP;
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE;


    1. ALHIMIK1992
      06.04.2016 13:15
      #8842674
      +1

      Теги не сработали(


  1. Uranic2
    06.04.2016 11:15
    #8842292

    Часто бывает нужно сравнить данные на двух разных базах по id. Делаю через json и функции row_to_json и json_populate_recordset. Сначала получаю JSON на мастер базе:

    SELECT json_agg(row_to_json(e.*))::json
    FROM mm.action e
    WHERE e.id IN (7, 8)
    — А потом на другой базе

    UPDATE
    mm.action nnn
    SET
    name = fff.name,
    def_rght_id = fff.def_rght_id,
    action_type_id = fff.action_type_id,
    img16_index = fff.img16_index,
    img32_index = fff.img32_index,
    param = fff.param
    FROM json_populate_recordset(NULL::mm.action,
    /* здесь JSON, полученный на первом шаге */
    ) fff
    WHERE nnn.id=fff.id

    На любителя можно через hstore организовать то же самое


  1. shorokhovs
    06.04.2016 11:15
    #8842296
    +2

    Еще очень мощная вещь оконные функции. Во была хорошая статейка на тему habrahabr.ru/post/268983


    1. varanio
      06.04.2016 11:16
      #8842302
      +4

      Это моя же статья. Решил здесь не повторяться


  1. AlexLeonov
    06.04.2016 11:35
    #8842364

    Пожалуйста, напишите, что вы используете в повседневной работе. Что-нибудь такое, что возможно не для всех очевидно

    Оконные функции

    UPD. Я буду читать все комменты, прежде чем оставить свой…


  1. myz0ne
    06.04.2016 14:34
    #8842900
    +1

    Выбрать дублирующиеся значения f2 вместе c pk этих записей

    select f2, count(id) cnt, array_agg(id) from table
    group by f2
    having count(id) > 1
    order by cnt desc;


  1. myz0ne
    06.04.2016 14:48
    #8842968
    +1

    Выбрать день из поля с типом timestamp. Удобно для быстрого подсчета статистики по дням
    to_char(time, 'YYYY-MM-DD') as day.
    Например:
    select now(), to_char(now(), 'YYYY-MM-DD') as day;

    Немного админской магии:

    Показать привелегии пользователей
    SELECT grantee, table_schema || '.' || table_name AS relname, string_agg(privilege_type, ', ') AS privileges
    FROM information_schema.role_table_grants
    --WHERE grantee = 'user'
    GROUP BY grantee, relname,table_name;

    Показать размер таблицы/индекса.
    select pg_size_pretty(pg_relation_size('schema.table|index'));

    Размер всех таблиц по убыванию. external_table_usage — размер индексов, итд.
    SELECT
    schemaname||'.'||tablename AS full_tname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_usage,
    pg_size_pretty((pg_total_relation_size(schemaname||'.'||tablename) — pg_relation_size(schemaname||'.'||tablename))) AS external_table_usage
    FROM pg_catalog.pg_tables
    ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC

    Размер всех индексов по убыванию
    SELECT
    schemaname||'.'||indexname AS full_tname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||indexname)) AS usage
    FROM pg_catalog.pg_indexes
    ORDER BY pg_total_relation_size(schemaname||'.'||indexname) DESC;


  1. restop
    07.04.2016 09:43
    #8844574

    Последнее время часто использую composite types

    create type my_type as (
    id integer,
    can_be_updated boolean
    );

    использовать можно, например, в функциях:
    CREATE OR REPLACE FUNCTION find_pattern(… )
    RETURNS my_type AS
    $body$
    declare
    my_record my_type;
    begin

    SELECT id, case when state = 0 then 'Y' else 'N' end
    INTO my_record
    FROM my_table
    WHERE type = 'my_type'

    if not found then
    my_record.id = null;
    end if;

    return my_record;

    end;

    Еще недавно оказалась полезна функция split_part(string text, delimiter text, field int) — делает сплит строки по разделителю и возвращает указанный элемент массива(индекс от 1).


  1. lokks
    07.04.2016 16:13
    #8845268
    +3

    Удаление геометрических дублей

    DELETE FROM data.table
    WHERE EXISTS(SELECT id FROM data.table c WHERE c.id < table.id AND c.geom && table.geom AND ST_Equals(table.geom, c.geom));