Курс молодого бойца PostgreSQL

Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).

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

Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin'e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.

Поехали!

Использование временных таблиц

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

Такие таблицы создаются как обычные, но с ключевым словом TEMP, и автоматически удаляются после завершения сессии.

Ключ ON COMMIT DROP автоматически удаляет таблицу (и все связанные с ней объекты) при завершении транзакции.

Пример:

//-------------------------------------------------------------------------------------//

ROLLBACK;

BEGIN;

CREATE TEMP TABLE my_fist_temp_table -- стоит использовать наиболее уникальное имя

ON COMMIT DROP -- удаляем таблицу при завершении транзакции

AS

SELECT 1 AS id, CAST ('какие-то значения' AS TEXT) AS val;

------------ Дополнительные манипуляции с таблицей: ------------------

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

ALTER TABLE my_fist_temp_table

ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE;

-- для тех, кто не в курсе, чаще всего данные в таблицах не удаляются, а помечаются как удаленные подобным флагом

CREATE UNIQUE INDEX ON my_fist_temp_table (lower(val))

WHERE is_deleted = FALSE; -- можно даже создать индекс/ограничение, если это необходимо

-- данный индекс не позволит вставить дубликат(не зависимо от регистра) для столбца VAL, для не удаленных строк

-- манипулируем данными таблицы

UPDATE my_fist_temp_table

SET id=id+3;

-- проверяем/используем содержание таблицы

SELECT * FROM my_fist_temp_table;

--COMMIT;

//-------------------------------------------------------------------------------------//

Часто используемый сокращенный синтаксис Postgres

Выражение:

SELECT CAST ('365' AS INT);

можно записать менее громоздко:

SELECT '365'::INT;

//-------------------------------------------------------------------------------------//

Сокращенная запись конструкции (I)LIKE '%text%'

~ ‘text’ Проверяет соответствие регулярному выражению с учётом регистра LIKE '%text%'

~* ‘text’ Проверяет соответствие регулярному выражению без учёта регистра ILIKE '%text%'

!~ ‘text’ Проверяет несоответствие регулярному выражению с учётом регистра NOT LIKE '%text%'

!~* ‘text’ Проверяет несоответствие регулярному выражению без учёта регистра NOT ILIKE '%text%'

//-------------------------------------------------------------------------------------//

Общие табличные выражения (CTE). Конструкция WITH

Очень удобная конструкция, позволяет поместить результат запроса во временную таблицу и тут же использовать ее.

Примеры будут примитивны, чтобы уловить суть.

a) Простой SELECT

//-------------------------------------------------------------------------------------//

WITH cte_table_name AS ( -- задаем удобное нам имя таблицы

SELECT schemaname, tablename -- наш любой запрос

FROM pg_catalog.pg_tables -- к примеру, системная таблица с таблицами базы

ORDER BY 1,2

)

SELECT * FROM cte_table_name; -- указываем нашу таблицу

--по факту получим результат выполнения запроса в скобках

//-------------------------------------------------------------------------------------//

Таким способом можно 'оборачивать' какие-либо запросы (даже UPDATE, DELETE и INSERT, об этом будет ниже) и использовать их результаты в дальнейшем.

b) Можно создать несколько таблиц, перечисляя их нижеописанным способом

//-------------------------------------------------------------------------------------//

WITH

table_1 (col,b) AS (SELECT 1,1), -- первая таблица

table_2 (col,c) AS (SELECT 2,2) -- вторая таблица

--,table_3 (cool,yah) AS (SELECT 2,2 from table_2) -- совсем недавно узнал, что можно обращаться к вышестоящей таблице

SELECT * FROM table_1 FULL JOIN table_2 USING (col);

//-------------------------------------------------------------------------------------//

c) Можно даже вложить вышеуказанную конструкцию в еще один (и более) WITH

//-------------------------------------------------------------------------------------//

WITH super_with (col,b,c) AS ( / можем задать имена столбцов в скобках после имени таблицы /

WITH

table_1 (col,b) AS (SELECT 1,1),

table_2 (col,c) AS (SELECT 2,2)

SELECT * FROM table_1 FULL JOIN table_2 USING (col)-- указываем нашу таблицу

)

SELECT col, b20, c30 FROM super_with;

//-------------------------------------------------------------------------------------//

По производительности следует сказать, что не стоит помещать в секцию WITH данные, которые будут в значительной степени фильтроваться последующими внешними условиями (за пределами скобок запроса), ибо оптимизатор не сможет построить эффективный запрос. Удобнее всего положить в CTE результаты, к которым требуется несколько раз обращаться.

Функция array_agg(MyColumn).

Значения в реляционной базе хранятся разрозненно (атрибуты по одному объекту могут быть представлены в нескольких строках). Для передачи данных какому-либо приложению часто возникает необходимость собрать данные в одну строку (ячейку) или массив.

В PostgreSQL для этого существует функция array_agg(), она позволяет собрать в массив данные всего столбца (если выборка из одного столбца).

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

Сразу опишу еще одну функцию и перейдем к примеру.

array_to_string(array[], ';') позволяет преобразовать массив в строку: первым параметром указывается массив, вторым — удобный нам разделитель в одинарных кавычках (апострофах).

//-------------------------------------------------------------------------------------//

-- создадим и наполним данными таблицу вышеописанным способом

WITH my_table (ID, year, any_val) AS

(

VALUES (1, 2017,56)

,(2, 2017,67)

,(3, 2017,12)

,(4, 2017,30)

,(5, 2020,8)

,(6, 2030,17)

,(7, 2030,50)

)

SELECT year

,array_agg(any_val) -- собираю данные (по каждому году) в массив

,array_agg(any_val ORDER BY any_val) AS sort_array_agg -- порядок элементов можно отсортировать (с 9+ версии Postgres)

,array_to_string(array_agg(any_val),';') -- преобразовываю массив в строку

,ARRAY['This', 'is', 'my' , 'array'] AS my_simple_array -- способ создания массива

FROM my_table

GROUP BY year; -- группируем данные по каждому году

//-------------------------------------------------------------------------------------//

Ключевое слово RETURNIG *

указанное после запросов INSERT, UPDATE или DELETE позволяет увидеть строки, которых коснулась модификация (обычно сервер сообщает лишь количество модифицированных строк).

Удобно в связке с BEGIN посмотреть на что именно повлияет запрос, в случае неуверенности в результате или для передачи каких либо id на следующий шаг.

Пример:

//-------------------------------------------------------------------------------------//

--1

DROP TABLE IF EXISTS for_del_tmp; / IF EXISTS не вызовет ошибки, если таблицы для удаления не существует /

CREATE TABLE for_del_tmp -- Создаем таблицу

AS --Наполняем сгенерированными данными из запроса ниже

SELECT generate_series(1,1000) AS id, -- Генерируем 1000 пронумерованных строк

random() AS values; -- Наполняем случайными числами

--2

DELETE FROM for_del_tmp

WHERE id > 500

RETURNING *;

/*Покажет все удаленные строки данной командой,

RETURNING * - вернет все столбцы таблицы test,

так же можно перечислить столбцы как в SELECT (прим. RETURNING id,name)*/

//-------------------------------------------------------------------------------------//

Сохранение результата запроса в файл.

У команды COPY много разных параметров и назначений, опишу самое простое применение для ознакомления.

//-------------------------------------------------------------------------------------//

COPY (

SELECT FROM pg_stat_activity / Наш запрос. Для примера: системная таблица выполняемых процессов БД */

--) TO 'C:/TEMP/my_proc_tst.csv' -- Запись результата запроса в файл. Пример для Windows

) TO '/tmp/my_proc_tst.csv' -- Запись результата запроса в файл. Пример для LINUX

--) TO STDOUT -- выведет данные в консоль или лог pgAdmin

WITH CSV HEADER -- Необязательная строка. Передает название столбцов таблицы в файл

//-------------------------------------------------------------------------------------//

Выполнение запроса на другой базе

Не так давно узнал, что можно адресовать запрос к другой базе, для этого есть функция dblink:

//-------------------------------------------------------------------------------------//

SELECT * FROM dblink(

'host=localhost user=postgres dbname=postgres', / host и user можно не указывать, если вы хотите использовать текущие /

'SELECT ''Удаленная база: '' || current_database()' / есть свои нюансы и ограничения. Как пример, запрос передается в одинарных кавычках, поэтому кавычки внутри запроса должны быть экранированы (в данном примере для экранирования использую две одинарных кавычки подряд). /

)

RETURNS (col_name TEXT)

UNION ALL

SELECT 'Текущая база: ' || current_database();

//-------------------------------------------------------------------------------------//

Если возникает ошибка необходимо выполнить установку расширения следующей командой: CREATE EXTENSION dblink;

Функция similarity

Функция определения схожести одного значения к другому.

Использовал для сопоставления текстовых данных, которые были похожи, но не равны друг другу (имелись опечатки). Сэкономил уйму времени и нервов, сведя к минимуму ручную привязку. similarity(a, b) выдает дробное число от 0 до 1, чем ближе к 1, тем точнее совпадение. Перейдем к примеру. С помощью WITH организуем временную таблицу с вымышленными данными (и специально исковерканными для демонстрации функции), и будем сравнивать каждую строку с нашим текстом. В примере ниже будем искать то, что больше похоже на ООО «РОМАШКА» (подставим во второй параметр функции).

//-------------------------------------------------------------------------------------//

WITH company (id,c_name) AS (

VALUES (1, 'ООО РОМАШка')

UNION ALL

/ P.S. UNION ALL работает быстрее, чем UNION, т.к. отсутствует принудительная сортировка для устранения дубликатов, которая нам не требуется в данном случае /

VALUES (2, 'ООО "РОМАШКА"')

UNION ALL

VALUES (3, 'ООО РаМАШКА')

UNION ALL

VALUES (4, 'ОАО "РОМАКША"')

UNION ALL

VALUES (5, 'ЗАО РОМАШКА')

UNION ALL

VALUES (6, 'ООО РО МАШКА')

UNION ALL

VALUES (7, 'ООО РОГА И КОПЫТА')

UNION ALL

VALUES (8, 'ZAO РОМАШКА')

UNION ALL

VALUES (9, 'Как это сюда попало?')

UNION ALL

VALUES (10, 'Ромашка 33')

UNION ALL

VALUES (11, 'ИП "РомаШкович"')

UNION ALL

VALUES (12, 'ООО "Рома Шкович"')

UNION ALL

VALUES (13, 'ИП "Рома Шкович"')

)

SELECT *, similarity(c_name, 'ООО "РОМАШКА"')

,dense_rank() OVER (ORDER BY similarity(c_name, 'ООО "РОМАШКА"') DESC)

AS "Ранжирование результатов" -- оконная функций, о ней будет сказано ниже

FROM company

WHERE similarity(c_name, 'ООО "РОМАШКА"') >0.25 -- значения от 0 до 1, чем ближе к 1, тем точнее совпадение

ORDER BY similarity DESC;

//-------------------------------------------------------------------------------------//

Если возникает ошибка необходимо выполнить установку расширения следующей командой: CREATE EXTENSION pg_trgm;

Множественный шаблон для LIKE.

Задача. Необходимо отфильтровать список пользователей, имена которых должны соответствовать определенным шаблонам.

Как всегда, представлю простейший пример:

//-------------------------------------------------------------------------------------//

-- Создаем таблицу с данными

CREATE TEMP TABLE users_tst (id, u_name)

AS (VALUES (1::INT, NULL::VARCHAR(50))

,(2, 'Ульяна Х.')

,(3, 'Семён И.')

,(4, 'Виктория Т.')

,(5, 'Ольга С.')

,(6, 'Елизавета И.')

,(7, 'Николай Х.')

,(8, 'Исаак Р.')

,(9, 'Елисей А.')

);

//-------------------------------------------------------------------------------------//

Имеем запрос, который выполняет свою функцию, но становится громоздким при большом количестве фильтров.

//-------------------------------------------------------------------------------------//

SELECT * FROM users_tst

WHERE u_name LIKE 'В%'

OR u_name LIKE '%аа%'

OR u_name LIKE 'Ульяна Х.'

OR u_name LIKE 'Елисей%'

-- и т.д.

//-------------------------------------------------------------------------------------//

Продемонстрирую, как сделать его более компактным:

//-------------------------------------------------------------------------------------//

SELECT * FROM users_tst

WHERE u_name LIKE ANY (ARRAY['В%', '%аа%', 'Ульяна Х.', 'Елисей%'])

//-------------------------------------------------------------------------------------//

Можно проделать интересные трюки, используя подобный подход. Напишите в комментариях, если есть мысли, как еще можно переписать исходный запрос.

Несколько полезных, но редко используемых функций.

NULLIF(a,b) Возникают ситуации, когда определенное значение нужно трактовать как NULL. Например, строки нулевой длины ( '' — пустые строки) или ноль(0). Можно написать CASE, но лаконичнее использовать функцию NULLIF, которая имеет 2 параметра, при равенстве которых возвращается NULL, иначе выводит исходное значение.

Пример:

//-------------------------------------------------------------------------------------//

SELECT id

,param

,CASE WHEN param = 0 THEN NULL ELSE param END -- решение через CASE

,NULLIF(param,0) -- решение через NULLIF

,val FROM(

VALUES( 1, 0, 'В столбце слева был 0' )

) AS tst (id,param,val);

//-------------------------------------------------------------------------------------//

GREATEST выбирает наибольшее значение из перечисленных

//-------------------------------------------------------------------------------------//

SELECT GREATEST(2,1,5,7,4); --выберет 7

//-------------------------------------------------------------------------------------//

LEAST выбирает наименьшее значение из перечисленных

//-------------------------------------------------------------------------------------//

SELECT LEAST(2,1,5,7,4) -- выберет 1;

//-------------------------------------------------------------------------------------//

Экранирование символов

Начну с основ. В SQL строковые значения обрамляются ' апострофом (одинарной кавычкой). Числовые значения можно не обрамлять апострофами, а для разделения дробной части нужно использовать точку, т.к. запятая будет воспринята как разделитель

//-------------------------------------------------------------------------------------//

SELECT 'Мой текст', 365, 567.6, 567,6

//-------------------------------------------------------------------------------------//

Все хорошо, до тех пор пока не требуется выводить сам знак апострофа ' Для этого существуют два способа экранирования (известных мне)

//-------------------------------------------------------------------------------------//

SELECT 1, 'Апостроф '' и два апострофа подряд '''' ' -- Экранирование двойным написанием ''

UNION ALL

SELECT 2, E'Апостроф \' и два апострофа подряд \'\' ' -- экранирование обратным слешем, , английская буква E перед первой кавычкой необходима, чтобы символ \ воспринимался как символ экранирования

//-------------------------------------------------------------------------------------//

В PostgreSQL существуют более удобный способ использовать данные, без экранирования символов. В обрамленной двумя знаками доллара $$ строке можно использовать практически любые символы.

Пример:

//-------------------------------------------------------------------------------------//

select $$необязательно писать '' чтобы просто вывести апостроф ', или заморачиваться с E'\' $$

//-------------------------------------------------------------------------------------//

Если этого мало, и внутри требуется использовать два символа доллара подряд $$, то Postgres позволяет задать свой «ограничитель». Стоит лишь между двумя долларами написать свой текст, например:

//-------------------------------------------------------------------------------------//

select $uniq_tAg$ необязательно писать '' чтобы просто вывести апостроф ', или заморачиваться с E'\', обрамляйте в $$ или $any_text$ $uniq_tAg$

//-------------------------------------------------------------------------------------//

Для себя этот способ открыл не так давно, когда начал изучать написание функций.

Заключение

Надеюсь, данный материал поможет узнать много нового начинающим и «средничкам». Сам я не являюсь разработчиком, а могу лишь назвать себя любителем SQL, поэтому то, как использовать описанные приемы — решать Вам.

https://habrahabr.ru/post/340460/

Материал опубликован 25 октября 2017

в разделе: "Статьи".


Team PERMSITE
develop, support and security of your site.

Команда ПЕРМСАЙТ
© 2009-2017, автор: Юрий Токарев.