Использование psql
Команды psql
При запуске psql выполняются два скрипта ( при их наличии )
psqlrc ( общий системный скрипт ) и .psqlrc ( пользовательский файл )
Файл пользовательского скрипта располагается в домашнем каталоге
Расположение системного скрипта можно узнать с помощью команды:
pg_config --sysconfdir
В данные файлы можно поместить команды выполняемые при запуске сеанса
Например настройка постраничного просмотра:
echo "\setenv PSQL_PAGER 'less -XS'" >> ~/.psqlrc
Подключение
psql -d база -U пользователь -h узел -p порт
Вызов справки
psql --help
Список команд psql
\?
Список команд SQL
\h
Синтаксис команд SQL
\h SELECT
Вывод без выравнивания
\a
Включение и выключение заголовков и итоговой строки при выводе таблиц
\t
Расширенный формат
\x
Расширенный формат для одного запроса ( \gx )
SELECT * from sdf.table LIMIT 1 \gx
Другие возможности форматирования
\pset
--Информация о текущем подключении
\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
Взаимодействие с ОС
Возможность выполнять команды shell
\! ls -la
Вывод запроса в файл
\o
megaplan=# \o /tmp/test.log
megaplan=# select * from account;
megaplan=# \! cat /tmp/test.log
id | name | test | user | password
------------+---------------------+-----+------------------------------------------+-------
\o
#Или так
select * from account \g /tmp/test.log
#Вывод можно перенаправить ОС
select * from account \g | grep key
Выполнение команд из файла
sudo -u postgres psql megadb < test.sql
# Или так
sudo -u postgres psql megadb -f test.sql
#Или так
\i test.sql
Команда gexec выполняет операторы в каждой строке, один за другим.
Переменные
megaplan=# --Установим переменную
megaplan=# \set TEST hello
megaplan=# --Вывод переменной
megaplan=# \echo :TEST
hello
--Запись результата запроса в переменную
megaplan=# SELECT now() AS now_time \gset
megaplan=# \echo :now_time
2023-07-28 13:07:26.241554+03
--Вывод всех переменных
\set
--Присвоение переменной результата вывода команды ОС
\set dir `pwd`
--Присвоение переменной запроса SQL ( топ 5 самых больших таблиц )
\set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'
--Выполнение переменной
\set :top5
--Присвоение запроса переменной можно записать в скрипт ~/.psqlrc
--После запуска psql переменная будут доступна для выполнения
Условный оператор
В качестве условного оператора может использоваться оператор
\if \else \endif
В качестве условия if может использоваться проверка существования переменной
:{?var}
Обработка ошибок внутри транзакций
psql автоматически фиксирует транзакцию для каждой команды
Можно явно начать транзакцию выполнив команду BEGIN
Однако в случае ошибки все выполненные запросы будут отменены и не зафиксированы, так как postgres не умеет откатывать одну команду в пределах одной транзакции
Такое поведение можно изменить установив точку сохранения последней успешно выполненной команды в транзакции и вернуться к ней в случае ошибки
Для этого существует переменная ON_ERROR_ROLLBACK
Изменим поведение определив значение данной переменной как "on"
\set ON_ERROR_ROLLBACK on
Параметры которые изменяются только после парезапуска сервера
--Параметры которые изменяются только после парезапуска сервера
SELECT name, setting, unit FROM pg_settings WHERE context = 'postmaster';
Другие полезные метакоманды psql
#Список процедурных языков
\dL
#Список схем
\dn+
#Список ролей с расширенной информацией. Эквивалент команды \du
\dg+
#Список операторов
\do+
#Список таблиц и представлений с правами доступа
\dp+
#Информация о типе данных
\dT int
#Список установленных расширений
\dx
#Редактирование файла
\e или \edit [имя_файла] [номер_строки]
#Если указано имя_файла, файл открывается для редактирования; после выхода из редактора содержимое файла копируется в буфер текущего запроса.
#Данная команд извлекает определение функции или процедуры
\ef akeys
#Если функция не указана, для редактирования открывается пустая заготовка CREATE FUNCTION.
#Устанавливает кодировку клиента
\encoding
#Без аргументов выводит текущую кодировку
#Извлекает определение представления
\ev
#После выхода из редактора изменённая команда немедленно выполняется, если вы добавили к ней точку с запятой
#Подсказка по указанной команде
\h SELECT
#Вывод всех команд
\h
#Вывод в формате html
\html
#Читает ввод из файла имя_файла и выполняет его
\i
#Результат выполнения команды будет сохраняться в файл
\o /tmp/out
#Печатает содержимое буфера текущего запроса в стандартный вывод. Если этот буфер пуст, будет напечатан последний выполненный запрос.
\p
#Интерактивный ввод значения переменной
\prompt namevar
#Установка значения переменной
\set testvar text
#Вывод значения переменной
\echo :testvar
#Отображение имен столбцов
\t
#Время выполнения команды
\timing
#Многократное выполнение команды
\watch
#Если буфер текущего запроса пуст, будет повторно выполнен последний переданный запрос.
#Выводит список таблиц, представлений и последовательностей с их правами доступа
\z sdf.*
#Запуск оболочки, выход exit
\!
#Справочная информация
\?
\ddp [ шаблон ] - выводит права доступа по умолчанию
\dE[S+] [ шаблон ] - внешняя таблица
\di[S+] [ шаблон ] - индекс
\dm[S+] [ шаблон ] - материализованное представление
\ds[S+] [ шаблон ] - последовательность
\dt[S+] [ шаблон ] - таблица
\dv[S+] [ шаблон ] - представление
Можно указывать все или часть этих букв
При добавлении + к имени команды для каждого объекта дополнительно будут выводиться состояние хранения (постоянный, временный, нежурналируемый), физический размер на диске и описание
#Вывод списка таблиц и индексов для схемы sdf
\dit sdf.*
#Список таблиц, представлений, индексов и последовательностей:
\dtvis
#Эти команды можно снабдить модификатором «+», чтобы получить больше информации:
\dt+
#Чтобы получить детальную информацию об отдельном объекте, надо воспользоваться командой \d
\d top_managers
#Помимо отношений, аналогичным образом можно смотреть и на другие объекты, такие как схемы (\dn) или функции (\df).
#Еще один модификатор «S» позволяет вывести не только пользовательские, но и системные объекты. С помощью шаблона можно ограничить выборку:
\dfS pg*size
#\df — describe function, \sf — show function:
\sf pg_catalog.pg_database_size(oid)
#Полный список всегда можно посмотреть в документации или командой
psql \?
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_09_data_catalog.html
\df[anptwS+] [ шаблон ] - Выводит список функций с типами данных, аргументов и классификаций
«agg» (агрегатная)
«normal», (обычная)
«procedure» (процедурная)
«trigger» (триггерная)
«window» (оконная)
Если используется форма \df+, то дополнительно выводятся характеристики каждой функции: изменчивость, допустимость распараллеливания, владелец, классификация по безопасности, права доступа, язык, исходный код и описание
#Вывод функций с классификаций normal в схеме sdf
\dfn sdf.*
Условные блоки
\if выражение
\elif выражение
\else
\endif
Подробнее здесь
Специальные переменные
AUTOCOMMIT - При значении on (по умолчанию) после каждой успешно выполненной команды выполняется фиксация изменений.
При значении off или если переменная не определена, фиксация изменений не происходит до тех пор, пока явно не выполнена команда COMMIT или END
Если режим autocommit отключён, необходимо явно откатывать изменения в неуспешных транзакциях, выполняя команду ABORT или ROLLBACK
Отключить режим autocommit можно в общесистемном файле psqlrc или в персональном файле ~/.psqlrc
ERROR - true в случае ошибки последнего SQL-запроса, false, если он был выполнен успешно
COMP_KEYWORD_CASE - Определяет, какой регистр букв будет использован при автоматическом завершении ключевых слов SQL
HISTCONTROL - Если переменная имеет значение ignorespace, строки, начинающиеся с пробела, не сохраняются в истории. Если она имеет значение ignoredups, в историю не добавляются строки, которые в ней уже есть. Значение ignoreboth объединяет эти два варианта. Со значением none (по умолчанию) в истории сохраняются все строки, считываемые в интерактивном режиме.
HISTFILE - Имя файла, в котором будет сохраняться список истории команд
LAST_ERROR_MESSAGE, LAST_ERROR_SQLSTATE - Основное сообщение об ошибке и связанный код SQLSTATE для последнего неудавшегося запроса в текущем сеансе psql либо пустая строка и 00000, если в текущем сеансе не происходили ошибки
ON_ERROR_ROLLBACK - При значении on, если команда в блоке транзакции выдаёт ошибку, ошибка игнорируется и транзакция продолжается. Со значением interactive такие ошибки игнорируются только в интерактивных сеансах, но не в скриптах. Со значением off (по умолчанию) команда в блоке транзакции, выдающая ошибку, прерывает всю транзакцию. Для реализации режима отката транзакции за вас неявно выполняется команда SAVEPOINT непосредственно перед каждой командой в блоке транзакции, а в случае ошибки команды происходит откат к этой точке сохранения.
ON_ERROR_STOP - По умолчанию, после возникновения ошибки обработка команд продолжается. Если эта переменная установлена в значение on, обработка команд будет немедленно прекращена, подробнее стоит почитать в официальной документации
PORT - Содержит порт сервера базы данных, к которому вы сейчас подключены
PROMPT1-3 - указывает как должно выглядеть приглашение
SERVER_VERSION_NAME, SERVER_VERSION_NUM - версия сервера
SHOW_CONTEXT - Этой переменной можно присвоить значения never (никогда), errors (ошибки) или always (всегда), определяющие, когда в сообщениях с сервера будут выводиться поля КОНТЕКСТ. По умолчанию выбран вариант errors (который означает, что контекст будет выводиться в сообщениях об ошибках, но не в предупреждениях и уведомлениях). Этот параметр не действует, когда установлен уровень VERBOSITY terse или sqlstate. (Когда вам потребуется подробная версия только что выданной ошибки, может быть полезна команда \errverbose.)
USER - имя текущего пользователя базы данных
VERBOSITY - Этой переменной можно присвоить значения default, verbose, terse или sqlstate для изменения уровня детализации в сообщениях об ошибках
VERSION, VERSION_NAME, VERSION_NUM - версия psql
#Отмена фиксаций изменений
\set AUTOCOMMIT off
Параметры
#Используя несколько ключей -c, можно смешивать метакоманды SQL и psql
sudo -u postgres psql -c '\x' -c "select * from jobs;" megabase
#Ключ \a включает непрерывный вывод
sudo -u postgres psql -c '\a' -c "select * from jobs;" megabase
#Выполнение команд в терминале linux из файла
sudo -u postgres psql -d megabase -f /tmp/test.sql
#Выполнение команд в терминале psql из файла
\i /tmp/test.sql
#Сохранение вывода из терминала psql в файл в формате html
megabase=# \H
Output format is html.
megabase=# \o /tmp/test.html
/tmp/test.html: Отказано в доступе
megabase=# select * from jobs;
#Сохранение вывода из терминала linux в файл в формате html
sudo -u postgres psql -d megaplan -H -f /tmp/test.sql > /tmp/test.html
#Запуск в пошаговом режиме, может использоваться для отладки
sudo -u postgres psql -s -c 'select now()';
#Изменить текущий рабочий каталог
\cd /home/mega
#Выполнить команду linux
\! pwd
#Выводит информацию о текущем подключении к базе
\conninfo
#Выполняет содержимое буфера текущего запроса (как \g) и показывает результат в виде перекрёстной таблицы.
sudo -u postgres psql -c '\x' -c '\crosstabview job_id action status time_created' -c "select * from sdf.job;" megabase
#Вся информация об (таблицы, представления, материализованного представления, индекса, последовательности, внешней таблицы) отношении
\d name.table
#Больше информации об отношении
\d name.table+
Присвоение значений переменной
Если внутри аргумента не в кавычках встречается имя переменной psql с предшествующим двоеточием (:), оно заменяется значением переменной, как описано в разделе Интерполяция SQL ниже. Также будут работать описанные там формы :'имя_переменной' и :"имя_переменной". Конструкция :{?имя_переменной} позволяет проверить, определена ли переменная. Она заменяется значением TRUE или FALSE. Экранирование обратной косой чертой защищает двоеточие от замены.
postgres=# --Присвоение значения переменной
postgres=# \set var1 123456
postgres=# --Вывод значения переменной
postgres=# \echo :var1
123456
~$ #Присвоение значения в командной строке linux
~$ sudo -u postgres psql --set var1='test'
Подключение к базе данных
Можно сэкономить на вводе параметров подключения, установив переменные среды PGDATABASE, PGHOST, PGPORT и/или PGUSER.
Клиентская кодировка будет определяться из локальных установок (переменная окружения LC_CTYPE в Unix). Кодировку клиента можно изменить, установив переменную окружения PGCLIENTENCODING.
Обратные кавычки
Текст в обратных кавычках считается командной строкой и передается в ОС для выполнения
В содержимом этого текста не обрабатываются никакие спецпоследовательности или особые знаки, за исключением того, что все вхождения :имя_переменной, где имя_переменной — это имя переменной psql, заменяются значением этой переменной. Также вхождения :'имя_переменной' заменяются значением переменной, заключённым в апострофы с тем, что это было одним аргументом команды оболочки.
sudo -u postgres psql `echo $test`
Изменить заголовок
#Изменить заголовок, который будет выводиться при выполнении любого запроса
postgres=# \C result
Title is "result".
postgres=# select now();
result
now
-------------------------------
2023-12-24 15:35:23.956629+03
(1 row)
Копирование данных с помощью метакоманды copy
Метакоманда \copy аналогична команде SQL COPY, но менее предпочтительна для больших объемов данных
--Копирование из таблицы в файл
megabase=# \copy sdf.job to /tmp/tmp.sql
COPY 46
megabase=# delete from sdf.job;
DELETE 46
megabase=# select count(*) from sdf.job;
-[ RECORD 1 ]
count | 0
--Копирование из файла в таблицу
megabase=# \copy sdf.job from /tmp/tmp.sql
COPY 46
megabase=# select count(*) from sdf.job;
-[ RECORD 1 ]
count | 46
#Копирование только одной строки в файл
\copy ( SELECT * FROM sdf.job where job_id = 1000002 ) to /tmp/test.sql
COPY 1
#Копирование строки из файла в таблицу
megabase=# \copy sdf.job from /tmp/file.sql
COPY 1
megabase=# select count(*) from sdf.job;
-[ RECORD 1 ]
count | 47
Дополнительная информация
Переменные окружения которые могут использоваться при соединении:
https://postgrespro.ru/docs/postgresql/13/libpq-envars