postgresql запуск sql скрипта

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

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

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

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

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

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

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

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

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

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

* (две тильды со звездочкой)

Поиск регулярными выражениями (имеет отличный от LIKE синтаксис)
оператор

* (одна тильда и звездочка) регистронезависимая версия

Приведу пример поиска разными способами строк, которые содержат слово text

‘%text%’

* ‘%text%’

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

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

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

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

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

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

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

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

Значения в реляционной базе хранятся разрозненно (атрибуты по одному объекту могут быть представлены в нескольких строках). Для передачи данных какому-либо приложению часто возникает необходимость собрать данные в одну строку (ячейку) или массив.
В PostgreSQL для этого существует функция array_agg(), она позволяет собрать в массив данные всего столбца (если выборка из одного столбца).
При использовании GROUP BY в массив попадут данные какого-либо столбца относительно каждой группы.

Сразу опишу еще одну функцию и перейдем к примеру.
array_to_string(array[], ‘;’) позволяет преобразовать массив в строку: первым параметром указывается массив, вторым — удобный нам разделитель в одинарных кавычках (апострофах). В качестве разделителя можно использовать

Выдаст результат:

Выполним обратное действие. Разложим массив в строки при помощи функции UNNEST, заодно продемонстрирую конструкцию SELECT columns INTO table_name. Помещу это в спойлер, чтобы статья не сильно разбухала.

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

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

Можно использовать в связке с CTE, организую безумный пример.

Таким образом, выполнится удаление данных, и удаленные значения передадутся на следующий этап. Все зависит от вашей фантазии и целей. Перед применением сложных конструкций обязательно изучите документацию вашей версии СУБД! (при параллельном комбинировании INSERT, UPDATE или DELETE существуют тонкости)

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

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

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

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

Если возникает ошибка:

«ERROR: function dblink(unknown, unknown) does not exist»

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

8. Функция similarity

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

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

Получим следующий результат:

Если возникает ошибка

«ERROR: function similarity(unknown, unknown) does not exist»

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

Сортируем по similarity DESC. Первыми результатами видим наиболее похожие строки (1— полное сходство).

Необязательно выводить значение similarity в SELECT, можно просто использовать его в условии WHERE similarity(c_name, ‘ООО «РОМАШКА»’) >0.7
и самим задавать устраивающий нас параметр.

P.S. Буду признателен, если подскажете какие еще есть способы сопоставления текстовых данных. Пробовал убирать регулярными выражениями все кроме букв/цифр, и сопоставлять по равенству, но такой вариант не срабатывает, если присутствуют опечатки.

9. Оконные функции OVER() (PARTITION BY __ ORDER BY __ )

Почти описав в своем черновике этот очень мощный инструмент, обнаружил (с грустью и радостью), что подобная качественная статья на эту тему уже существует. Не вижу смысла дублировать информацию, поэтому рекомендую обязательно ознакомиться с данной статьей (ссылка — habrahabr.ru/post/268983/, автору низкий поклон ) тем, кто еще не умеет пользоваться оконными функциями SQL.

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

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

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

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

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

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

11. Несколько полезных функций

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

COALESCE выбирает первое не NULL значение

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

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

PG_TYPEOF показывает тип данных столбца

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

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

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

результат:

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

результат одинаковый:

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

получаю данные в первозданном виде:

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

Увидим наш текст:

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

Заключение

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

Желаю успехов в изучении SQL. Жду комментариев и благодарю за прочтение!
UPD. Вышло продолжение

Источник

PostgreSQL

Введение

PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).

Установка

После установки

После установки проверьте версию установленного PostgreSQL

postgres (PostgreSQL) 9.2.24

Расположение файлов с настройками, например, postgresql.conf можно получить выполнив

В этом примере директория, которая содержит настройки это

Полезно изучить её содержание

Подключение к БД

Чтобы получить доступ к базам данных можно воспользоваться стандартной консолью psql.

Если Вы хотите подключиться из bash в Windows не забудьте добавить местоположение psql.exe (у меня это C:\Program Files\PostgreSQL\12\bin) в PATH

Как это делается описано в статье PATH

psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type «help» for help.

Если БД на локальном хосте

Получить данные о подключении

You are connected to database «postgres» as user «postgres» via socket in «/var/run/postgresql» at port «5432».

Список существующих БД

Список уже существующих на сервере баз данных можно получить командой

Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.

Создать базу данных

Создадим базу данных heihei_ru_db с кодировкой utf8

CREATE DATABASE «heihei_ru_db» WITH OWNER «postgres» ENCODING ‘UTF8’;

Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template

CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING
‘UTF8’ LC_COLLATE = ‘C’ LC_CTYPE = ‘C’ TEMPLATE = template0;

Проверим, что получилось

То получу сразу четыре доступные кодировки

C
C.UTF-8
en_US.utf8
POSIX

Но создать БД, например, с en_US.utf8 у меня не получается

CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.utf8’ LC_CTYPE = ‘en_US.utf8’ TEMPLATE = template0;

ERROR: invalid locale name: «en_US.UTF-8»

UPD: Выполнил такую же команду в Ubuntu и база успешно создана

CREATE DATABASE «new_db» WITH OWNER «postgres» ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’ TEMPLATE = template0;

Удалить базу данных

Чтобы удалить базу данных воспользуйтесь командой

DROP DATABASE db_name;

Проверить кодировки

Проверить кодировку сервера можно командой

Проверить кодировку клиента

Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс

Где db это название текущей базы данных

Показать адрес текущей директории

Вернуться в PostgreSQL можно выполнив

Зайти в БД

Чтобы начать работу с базой данных нужно знать её имя, например пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB

You are now connected to database «HeiHei_ru_DB» as user «postgres».

Если вы работаете в Linux и база данных находится на том же хосте можно выполнить

Чтобы посмотреть список таблиц введите

List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | person | table | postgres
(1 row)

Чтобы посмотреть всю таблицу person уже можно использовать стандартный

SELECT * FROM person;

Запуск скрипта из файла

Сперва проверим, что с переменными окружения всё впорядке.

Для этого введём в консоль psql.exe нажмём Enter и проверим что bash не жалуется на неизвестную команду.

Пишем скрипт script.sql

CREATE TABLE person ( id int, first_name VARCHAR(50), last_name VARCHAR(50), gender VARCHAR(5), date_of_birth DATE )

Применим этот скрипт к базе данных HeiHei_ru_DB

Password for user postgres:
CREATE TABLE

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

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

CREATE TABLE booking_sites ( id BIGSERIAL NOT NULL PRIMARY KEY, company_name VARCHAR(50) NOT NULL, origin_country VARCHAR(50) NOT NULL, age VARCHAR(3) NOT NULL, date_of_birth DATE NOT NULL, website_url VARCHAR(50) );

Password for user postgres:
CREATE TABLE

Содержание базы данных

В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites

Убедимся в том, что скрипт отработал удачно

Перейдём в базу данных heihei и проверим создалась ли таблица

You are now connected to database «heihei» as user «postgres».

Описание таблицы

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

Для описания таблицы используется команда \d

Пользователи

Получить список пользователей

Изменение таблицы

Когда нужно обновить название столбца таблицы используем команду ALTER

Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.

ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR( 60);

Проверим изменилась ли таблица

Чтобы файл открылся желательно положить его в папку

Источник

Набор скриптов для знакомства с PostgreSQL

Снова за свое

В одной из прошлых статей был предложен небольшой набор скриптов для SQL Server, позволяющий оценить текущее состояние сервера, узнать какие базы на нем расположены и другую полезную информацию. Сегодня мы попытаемся сделать то же самое для PostgreSQL. Ведь все любят PostgreSQL, не так ли?

Информация будет полезна для администраторов и разработчиков, имеющих дело с хайповой СУБД, а также всем энтузиастам, желающих «пощупать» PostgreSQL. Мы пройдем примерно такой же путь, что проделывали для SQL Server, но с некоторыми существенными отличиями, ведь обе СУБД значительно отличаются в архитектуре и принципах функционирования.

Здесь Вы не найдете продвинутых скриптов, ведь это лишь для знакомство с новым серверов PostgreSQL. Но обо всем далее.

Это не руководство

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

Все скрипты можно запускать с помощью терминального клиента psql, с помощью графической утилиты pgAdmin или же с помощью другого графического инструмента Azure Data Studio (поддержка PostgreSQL реализовано через расширение, не забудьте его установить). Это прямо «золотой век» инструментария для работы с базами данных!

Начнем с простых скриптов и постепенно перейдем к некоторым вопросам производительности.

Поехали!

Перейдем уже непосредственно к скриптам. Все они были проверены на PostgreSQL версии 10, но абсолютное большинство скриптов можно запускать и на более ранних версиях.

Первое знакомство

Как только Вы запустили свое клиентское приложение, то в первую очередь стоило бы узнать следующую информацию

Под базовой информацией понимается адрес и порт сервера, версия установленной СУБД PostgreSQL, а также текущее имя базы.

Скорее всего, большинство этих параметров Вам уже известны еще до подключения. Разве что версия СУБД может оставлять вопросы.

Иногда может быть полезным узнать, когда последний раз была перезапущена служба PostgreSQL.

Скрипт показывает время с момента старта службы в секундах и днях.

Работа PostgreSQL построена таким образом, что каждое соединение порождает серверный процесс «postgres» на стороне сервера, именно поэтому количество и список соединений может стать очень важной информацией, для получения представления о нагрузке, интенсивности работы с базой и настроек самой СУБД для оптимальной работы. И по этой же причине для оптимальной работы с PG нужен пул соединений, который и использует платформа 1С.

Часто имеет смысл собирать количество соединений на постоянной основе с помощью систем мониторинга.

Большое количество параметров сервера PostgreSQL задается в файле postgresql.conf, поэтому обязательно следует рассмотреть настройки этого файла. Однако, это не обязательно делать с помощью редактора VI и перезапускать весь сервер, чтобы из этого редактора выйти 🙂

Можно выполнить такой запрос.

Общую информацию мы получили, пойдемте дальше.

О базах данных

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

Теперь мы знаем какие базы у нас есть на сервере.

Не думаю, что эта информация может быть полезна сама по себе. Теперь узнаем размер всех баз.

Скрипт позволяет узнать какие базы у нас самые большие по размеру.

Чем больше база, тем больше к ней вопросов.

На следующем шаге уже может потребоваться посмотреть почему эта база такая большая.

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

Примерно такой же скрипт используется в отчете Просмотр и анализ структуры базы данных (отчет на СКД), для получения информации о размере объектов информационной базы 1С.

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

И снова индексы

Индексы являются одними из самых важных объектов любой базы данных, обеспечивающих производительность запросов и клиентских приложений для базы (в нашем случае это платформы 1С). Узнаем список индексов, который у нас есть.

В скрипте нет ничего необычного, просто список индексов и команда для их создания.

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

Теперь копнем глубже.

С помощью предложенного скрипта можно получить информацию о таблицах и их индексах, их размер, а также количество операций сканирования, чтений и количество «живых» строк, прочитанных из индекса.

Попробуем определить недостающие индексы.

К сожалению, PostgreSQL не предоставляет таких эффективных инструментов для поиска недостающих индексов, как это есть в SQL Server. Но с помощью статистики использования таблиц, мы можем определить для каких таблиц индексов явно не хватает за счет количества операций сканирования.

Далее нужно анализировать запросы к таблице и планы их выполнения, которые нужно собирать отдельно. Это выходит за рамки публикации, но может быть мы вернемся к этой теме в будущем.

Также стоит держать под контролем показатели фрагментации индексов, или bloat («раздутия») как это обычно еще называют в PostgreSQL.

По результатам скрипта можно судить корректно ли настроено обслуживание, выполняется ли операция VACUUM, эффективно ли обслуживания.

На этом с индексами пока все. Давайте посмотрим на статистику.

Статистика в порядке?

Статистика является одним из самых важных показателей, который использует планировщик для построения эффективных планов запросов. Если статистика устареет, то запросы могут быть выполнены самым неоптимальным образом. В итоге вся информационная система может столкнуться с деградацией производительности.

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

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

Теперь давайте поговорим о производительности.

Производительность

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

Скрипт покажет все активные запросы на сервере.

Ну а что дальше делать с этими запросами зависит от ситуации.

Теперь мы можем получить план запроса.

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

EXPLAIN поможет получить план запроса. Если использовать опцию «ANALYZE», то мы получим более подробную информацию о запросе, в том числе количество строк и время выполнение. Это достигается за счет фактического выполнения запроса. Без этого параметра будет формироваться предварительный план, который во многих случаях также может быть полезен для анализа.

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

Может быть полезным получить информацию о выполняемых транзакциях.

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

Также можно найти «плохие» транзакции, которые ожидают чего-то (снятие блокировки или других ресурсов) или отменены по каким-либо причинам.

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

Можно проверить эффективность работы кэша.

Аналогично SQL Server, можно отслеживать параметр эффективности работы с кэшем.

Если значение выше 90%, то СУБД может эффективно использовать память для оптимизации своей работы.

И под конец попробуем получить длительные запросы.

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

В примере это запросы, выполняющиеся более 1 минуты. К сожалению, скрипт не может предоставить полноценную картину. Только сбор и анализ запросов в рамках мониторинга ответит на все вопросы по проблемам производительности.

Вот и все, со скриптами пока все.

Любите ли Вы PostgreSQL?

Никаких готовых рецептов в статье нет, также как и нет информации о настройке операционной системы для оптимальной работы СУБД (не важно Windows это или *.nix) или настройке мониторинга. Лишь скрипты для получения общей информации.

Однако, теперь у Вас может появиться интерес и направление для изучения этой популярной и эффективной СУБД.

Есть чем дополнить? Добро пожаловать в комментарии!

Или есть интересные вопросы или опыт по PostgreSQL? Не стесняйтесь, пишите!

Источник