Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).
Постараюсь охватить множество тем и приемов, которые помогут при работе с данными, стараясь не углубляться в подробное описание того или иного функционала. Я любил подобные статьи, когда обучался самостоятельно. Пришло время отдать должное бесплатному интернет самообразованию и написать собственную статью.
Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin'e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.
Поехали!
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;
SELECT CAST ('365' AS INT);
SELECT '365'::INT;
Cокращенный синтаксис | Описание | Аналог (I)LIKE |
---|---|---|
~ ‘text’ or ~~ ‘%text%’ |
Проверяет соответствие выражению с учётом регистра | LIKE '%text%' |
~* ‘text’ ~~* ‘%text%’ |
Проверяет соответствие выражению без учёта регистра | ILIKE '%text%' |
!~ ‘text’ !~~ ‘%text%’ |
Проверяет несоответствие выражению с учётом регистра | NOT LIKE '%text%' |
!~* ‘text’ !~~* ‘%text%’ |
Проверяет несоответствие выражению без учёта регистра | NOT ILIKE '%text%' |
WITH cte_table_name AS ( -- задаем удобное нам имя таблицы
SELECT schemaname, tablename -- наш любой запрос
FROM pg_catalog.pg_tables -- к примеру, системная таблица с таблицами базы
ORDER BY 1,2
)
SELECT * FROM cte_table_name; -- указываем нашу таблицу
--по факту получим результат выполнения запроса в скобках
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);
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, b*20, c*30 FROM super_with;
-- создадим и наполним данными таблицу вышеописанным способом
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; -- группируем данные по каждому году
-- 1 Подготовительный этап
-- в процессе запроса будет создана таблица tst_unnest_for_del, с помощью конструкции SELECT INTO
-- чтобы запрос не приводил к ошибке, в случае если вы будете несколько раз прогонять этот скрипт, начну этот скрипт с удаления таблицы.
-- я также надеюсь, что вы запускаете это не на production сервере какого-либо проекта, где есть такая таблица
DROP TABLE IF EXISTS tst_unnest_for_del; /* IF EXISTS не вызовет ошибки, если таблицы для удаления не существует */
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(id) AS arr_id -- собираю данные(id) по каждому году в массив
,array_agg(any_val) AS arr_any_val -- собираю данные(any_val) по каждому году в массив
INTO tst_unnest_for_del -- !! способ создания и заполнения таблицы из полученного результата
FROM my_table
GROUP BY year;
--2 Демонстрирование функции Unnest
SELECT unnest(arr_id) unnest_id -- разбираем столбец id
,year
,unnest(arr_any_val) unnest_any_val -- разбираем столбец any_val
FROM tst_unnest_for_del
ORDER BY 1 -- восстанавливаем сортировку по 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)*/
--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()*1000)::INTEGER)::text as values; /* Наполняем случайными числами. P.S. У меня Postgre 9.2 Random() возвращает дробное число меньше единицы, умножаю на 1000, чтобы получить целую часть, затем преобразовываю к INTEGER для избавления от дробной части, и преобразовываю к тексту, т.к. хочу, чтобы тип данных созданного столбца был TEXT*/
--2
DELETE FROM for_del_tmp
WHERE id > 500
RETURNING *; -- Данный запрос просто удалит записи, вернув удаленные строки на экран
--3
WITH deleted_id (id) AS
(
DELETE FROM for_del_tmp
WHERE id > 25
RETURNING id -- удаляем еще часть данных, записывая id в наше CTE "deleted_id"
)
INSERT INTO for_del_tmp -- инициируем INSERT
SELECT id, 'Удаленная строка в ' || now()::TIME || ' а если быть точным, то ' || timeofday()::TIMESTAMP /* здесь можно проследить за тем, как отличается время возвращаемое функциями (зависит от описания функции, углубляться не буду, и так далеко зашел)*/
FROM deleted_id -- вставляем удаленные данные из "for_del_tmp" в нее же
RETURNING *; -- сразу видим что проинсертилось
--весь блок можно выполнять бесконечно, мы будем вставлять удаляемые данные в эту же таблицу.
--4
SELECT * FROM for_del_tmp; -- проверяем, что вышло в итоге
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 -- Необязательная строка. Передает название столбцов таблицы в файл
SELECT * FROM dblink(
'host=localhost user=postgres dbname=postgres', /* host и user можно не указывать, если вы хотите использовать текущие */
'SELECT ''Удаленная база: '' || current_database()' /* есть свои нюансы и ограничения. Как пример, запрос передается в одинарных кавычках, поэтому кавычки внутри запроса должны быть экранированы (в данном примере для экранирования использую две одинарных кавычки подряд). */
)
RETURNS (col_name TEXT)
UNION ALL
SELECT 'Текущая база: ' || current_database();
«ERROR: function dblink(unknown, unknown) does not exist»необходимо выполнить установку расширения следующей командой:
CREATE EXTENSION dblink;
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;
«ERROR: function similarity(unknown, unknown) does not exist»необходимо выполнить установку расширения следующей командой:
CREATE EXTENSION pg_trgm;
WITH company (id,c_name) AS ( -- входная таблица с данными
VALUES (1, 'ООО РОМАШка')
UNION ALL
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, 'ИП "Рома Шкович"')
UNION ALL
VALUES (13, 'ООО РАГА И КАПЫТА')
),
compare (id, need) AS -- наша база для сопоставления
(VALUES (100500, 'ООО "РОМАШКА"')
UNION ALL
VALUES (9999, 'ООО "РОГА И КОПЫТА"')
)
SELECT c1.id, c1.c_name, 'сравниваем с ' || c2.need, similarity(c1.c_name, c2.need)
,dense_rank() OVER (PARTITION BY c2.need ORDER BY similarity(c1.c_name, c2.need) DESC)
AS "Ранжирование результатов" -- оконная функций, о ней будет сказано ниже
FROM company c1 CROSS JOIN compare c2
WHERE similarity(c_name, c2.need) >0.25 -- значения от 0 до 1, чем ближе к 1, тем точнее совпадение
ORDER BY similarity DESC;
-- Создаем таблицу с данными
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['В%', '%аа%', 'Ульяна Х.', 'Елисей%'])
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);
SELECT COALESCE(NULL,NULL,-20,1,NULL,-7); --выберет -20
SELECT GREATEST(2,1,NULL,5,7,4,-9); --выберет 7
SELECT LEAST(2,1,NULL,5,7,4,-9); -- выберет -9
SELECT pg_typeof(id), pg_typeof(arr), pg_typeof(NULL)
FROM (VALUES ('1'::SMALLINT, array[1,2,'3',3.5])) AS x(id,arr);
-- покажет smallint, numeric[] и unknown соответственно
SELECT pid, query, * FROM pg_stat_activity -- таблица с процессами БД. В старых версиях postgres столбец PID назывался PROCPID
WHERE state <> 'idle' and pid <> pg_backend_pid(); -- исключаем подключения и свой только что вызванный процесс
SELECT pg_terminate_backend(PID); /* подставляем сюда PID процесса который мы хотим остановить, в отличие от нижеприведенной команды, посылает более щадящий сигнал о завершении, который не всегда может убить процесс*/
SELECT pg_cancel_backend(PID); /* подставляем сюда PID процесса который мы хотим остановить. Практически гарантированно убивает запрос, что-то вроде KILL -9 в LINUX */
SELECT pg_cancel_backend(pid) FROM pg_stat_activity -- примера ради убиваем все процессы
WHERE state <> 'idle' and pid <> pg_backend_pid();
SELECT 'Мой текст', 365, 567.6, 567,6
SELECT 1, 'Апостроф '' и два апострофа подряд '''' ' -- Экранирование двойным написанием ''
UNION ALL
SELECT 2, E'Апостроф \' и два апострофа подряд \'\' ' -- экранирование обратным слешем, , английская буква E перед первой кавычкой необходима, чтобы символ \ воспринимался как символ экранирования
select $$необязательно писать '' чтобы просто вывести апостроф ', или заморачиваться с E'\' $$
select $uniq_tAg$ необязательно писать '' чтобы просто вывести апостроф ', или заморачиваться с E'\', обрамляйте в $$ или $any_text$ $uniq_tAg$
К сожалению, не доступен сервер mySQL