Postgresql вывести сообщение в консоль

Обновлено: 04.07.2024

В следующем примере символ % будет заменён на значение v_job_id :

При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:

Предоставляет подсказку по вызванной ошибке. ERRCODE

Устанавливает код ошибки ( SQLSTATE ). Код ошибки задаётся либо по имени, как показано в Приложении A, или напрямую, пятисимвольный код SQLSTATE . COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Предоставляет имя соответствующего объекта, связанного с ошибкой.

Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :

У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:

Ещё один вариант — использовать RAISE USING или RAISE уровень USING , а всё остальное записать в списке USING .

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

Примечание

До версии PostgreSQL 9.1 команда RAISE без параметров всегда вызывала ошибку с выходом из блока, содержащего активную секцию EXCEPTION . Эту ошибку нельзя было перехватить, даже если RAISE в секции EXCEPTION поместить во вложенный блок со своей секцией EXCEPTION . Это было сочтено удивительным и не совместимым с Oracle PL/SQL.

Примечание

При задании SQLSTATE кода необязательно использовать только список предопределённых кодов ошибок. В качестве кода ошибки может быть любое пятисимвольное значение, состоящее из цифр и/или ASCII символов в верхнем регистре, кроме 00000 . Не рекомендуется использовать коды ошибок, которые заканчиваются на 000 , потому что так обозначаются коды категорий. И чтобы их перехватить, нужно перехватывать целую категорию.

39.8.2. Проверка утверждений

Оператор ASSERT представляет удобное средство вставлять отладочные проверки в функции PL/pgSQL .

Здесь условие — это логическое выражение, которое, как ожидается, должно быть всегда истинным; если это так, оператор ASSERT больше ничего не делает. Если же оно возвращает ложь или NULL, этот оператор выдаёт исключение ASSERT_FAILURE . (Если ошибка происходит при вычислении условия , она выдаётся как обычная ошибка.)

Учтите, что оператор ASSERT предназначен для выявления программных дефектов, а не для вывода обычных ошибок (для этого используется оператор RAISE , описанный выше).


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

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

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

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

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

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

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

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

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

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

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

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

Cокращенный синтаксис Описание Аналог (I)LIKE
~ ‘text’
or
~~ ‘%text%’		 Проверяет соответствие выражению с учётом регистра LIKE '%text%'
~* ‘text’
~~* ‘%text%’		 Проверяет соответствие выражению без учёта регистра ILIKE '%text%'
!~ ‘text’
!~~ ‘%text%’		 Проверяет несоответствие выражению с учётом регистра NOT LIKE '%text%'
!~* ‘text’
!~~* ‘%text%’		 Проверяет несоответствие выражению без учёта регистра NOT ILIKE '%text%'

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

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

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

a) Простой SELECT


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

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


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


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

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

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

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

Табуляция \t — к примеру, позволит при вставки ячейки в EXCEL без усилий разбить значения на столбцы (использовать так: array_to_string(array[], E'\t') )
Перевод строки \n — разложит значения массива по строкам в одной ячейке (использовать так: array_to_string(array[], E'\n') — объясню ниже почему)


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

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



Результат:

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

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

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

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

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

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

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

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


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

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

8. Функция similarity

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


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

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

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



Получим такой результат:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Внимание! Ни в коем случае не убивайте зависший процесс через консоль KILL -9 или диспетчер задач.
Это может привести к краху БД, потере данных и долгому автоматическому восстановлению базы.

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

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


результат:

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


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

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


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


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

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

Заключение

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

Шпаргалка по PostgreSQL

Шпаргалка по PostgreSQL содержит общие команды, которые позволят вам быстро и эффективно работать с PostgreSQL.

Ниже предоставляем вам 3-страничную шпаргалку по PostgreSQL в формате PDF. Вы можете скачать и распечатать ее для быстрого ознакомления с наиболее часто используемыми операторами в PostgreSQL:

Команды PostgreSQL (Шпаргалка по PostgreSQL)

Доступ к серверу PostgreSQL через psql с определенным пользователем:

Например, следующая команда использует пользователя postgres для доступа к серверу базы данных PostgreSQL:

Подключение к определенной базе данных:

Подключение к базе данных dvdrental:

Чтобы выйти из psql:

Список всех баз данных на сервере PostgreSQL

Список всех схем:

Список всех хранимых процедур и функций:

Список всех представлений

Список всех таблиц в текущей базе данных.

Или для получения дополнительной информации о таблицах в текущей базе данных:

Получение подробной информации о таблице.

Показывает хранимую процедуру или код функции:

Показывает вывод запроса в красивом формате:

Список всех пользователей:

Создает новую роль:

Создает новую роль с: username и password :

Изменяет роль для текущей сессии на new_role :

Разрешить role_1 установить свою роль как role_2 :

Управление базами данных

Создание новой базы данных:

Удаление базы данных навсегда:

Управление таблицами

Создание новой или временной таблицы

Добавление нового столбца в таблицу:

Удаление столбца в таблице:

Установите или удалите значение по умолчанию для столбца:

Добавление первичного ключа к таблице.

Удаление первичного ключа из таблицы.

Удаление таблицы и зависимых от нее объектов:

Управление представлениями

Создаем рекурсивное представление:

Создайте детализированное представление:

Обновление детализированного представления:

Удаление существующего представления.

Удаление детализированного представления:

Управление индексами

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

Удаление указанного индекса из таблицы

Запрос данных из таблиц

Запросить все данные из таблицы:

Запрос данных из указанных столбцов всех строк таблицы:

Запрашивает данные и выбирает только уникальные строки:

Запрашивает данные из таблицы с помощью фильтра:

Назначение псевдонима столбцу в наборе результатов:

Запрос данных с помощью оператора LIKE

Запрос данных с помощью оператора BETWEEN:

Запрос данных с помощью оператора IN:

Ограничение возвращаемых строк с помощью условия LIMIT:

Запрос данных из множества с использованием inner join, left join, full outer join, cross join и natural join:

Возвращает количество строк таблицы.

Сортировка строк в порядке возрастания или убывания:

Группировка строк с помощью GROUP BY.

Фильтруйте группы, используя HAVING.

Операции

Объединение набора результатов двух или более запросов с помощью оператора UNION :

Минус результат используя оператор EXCEPT :

Получаем пересечение наборов результатов двух запросов:

Изменение данных

Добавляет новую строку в таблицу:

Добавляет несколько строк в таблицу:

Обновление данных для всех строк:

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


Подключение к консоли PostgreSQL осуществляется исключительно под специальным пользователем postgres.

Основные команды интерактивного режима PostgreSQL:

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

  • -c (или –command) – запуск команды SQL без выхода в интерактивный режим
  • -f file.sql — выполнение команд из файла file.sql
  • -l (или –list) – выводит список доступных баз данных
  • -U (или –username) – указываем имя пользователя (например postgres)
  • -W (или –password) – приглашение на ввод пароля
  • -d dbname — подключение к БД dbname
  • -h – имя хоста (сервера)
  • -s – пошаговый режим, то есть, нужно будет подтверждать все команды
  • –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
  • -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

Утилиты (программы) PosgreSQL:

  • createdb и dropdb – создание и удаление базы данных (соответственно)
  • createuser и dropuser – создание и пользователя (соответственно)
  • pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
  • postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
  • initdb – создание новых кластеров PostgreSQL
  • initlocation – программа для создания каталогов для вторичного хранения баз данных
  • vacuumdb – физическое и аналитическое сопровождение БД
  • pg_dump – архивация и восстановление данных
  • pg_dumpall – резервное копирование всего кластера PostgreSQL
  • pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

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

Создание резервной копии с сжатием в gz

Бекап всех баз данных используя команду pg_dumpall.

Список наиболее часто используемых опций:

  • -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
  • -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
  • -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
  • -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
  • -b — включать в дамп большие объекты (blog’и).
  • -s, —schema-only — дамп только схемы.
  • -C, —create — добавляет команду для создания БД.
  • -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
  • -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
  • -F, —format — выходной формат дампа, custom, tar, или plain text.
  • -t, —table=TABLE — указываем определенную таблицу для дампа.
  • -v, —verbose — вывод подробной информации.
  • -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

Читайте также: