Команды psql: шпаргалка по консоли PostgreSQL

Коротко

• Подключение: psql -h хост -p 5432 -U пользователь -d база.
• Команды psql начинаются с обратной косой черты и выполняются по Enter: \dt — таблицы, \d имя — описание таблицы, \q — выход.
• SQL-запрос выполняется только после точки с запятой. Приглашение база-# значит, что psql ждёт продолжения.
• \x auto показывает широкие строки столбиком, \timing — время каждого запроса.
• Выгрузка в CSV: psql --csv -c "запрос" > файл.csv или \copy (запрос) to 'файл.csv' csv header.

Как подключиться

psql принимает те же пять параметров, что и любой клиент: хост, порт, пользователь, база и пароль. Пароль в командной строке не передаётся — psql спросит его сам. Если параметр не указан, берётся значение по умолчанию: сокет локального сервера, порт 5432, пользователь и база с именем вашего пользователя в системе.

Вместо отдельных ключей можно передать строку подключения — её часто присылают администраторы и облачные сервисы. В Linux к локальному серверу под суперпользователем подключаются через системного пользователя: sudo -u postgres psql.

Осмотреть базу: \l, \dt, \d

Первое, что делают в незнакомой базе, — смотрят, какие есть базы, схемы и таблицы. Буква после \d обозначает тип объекта: t — таблицы, v — представления, i — индексы, n — схемы, f — функции. Плюс в конце (\dt+) добавляет размер и описание.

Команды принимают шаблон: \dt sales.* покажет все таблицы схемы sales, \dt *order* — таблицы со словом order в названии. Без шаблона psql показывает только объекты из схем, которые есть в пути поиска (search_path), обычно это public.

mayak=> \d payments
                    Table "public.payments"
    Column    |     Type      | Collation | Nullable | Default
--------------+---------------+-----------+----------+---------
 payment_id   | integer       |           |          |
 user_id      | integer       |           |          |
 paid_at      | date          |           |          |
 amount       | numeric(10,2) |           |          |
 plan         | text          |           |          |
 payment_type | text          |           |          |

Выполнить запрос и прочитать результат

Обычный SQL набирается прямо в приглашении и может занимать несколько строк. psql отправит запрос на сервер, только когда встретит точку с запятой. Стрелка вверх возвращает предыдущие строки, \e открывает последний запрос в текстовом редакторе — удобно, когда запрос длинный.

Если строк больше, чем помещается на экране, psql показывает их через программу постраничного просмотра. Пролистать — пробел, выйти — клавиша q. Отключить просмотр совсем — \pset pager off.

Выполнить файл и запрос из командной строки

Длинные запросы удобно держать в файлах. Внутри psql файл выполняет \i путь/к/файлу.sql, снаружи — ключ -f. Ключ -c выполняет одну команду и выходит: так psql встраивают в скрипты и задания по расписанию.

По умолчанию psql продолжает выполнять файл после ошибки, и в результате половина команд прошла, а половина — нет. Ключ -v ON_ERROR_STOP=1 останавливает выполнение на первой ошибке и возвращает ненулевой код выхода.

Выгрузить результат в CSV

Начиная с psql 12 есть ключ --csv: результат печатается в формате CSV с заголовком, и его остаётся перенаправить в файл. Внутри psql то же делает \copy с запросом в скобках. В отличие от SQL-команды COPY, \copy пишет файл на вашем компьютере, а не на сервере, и не требует особых прав.

Для одного числа в скрипте удобен ключ -At: без выравнивания (-A) и без заголовков (-t) psql выводит голое значение, например 4613.

-- из командной строки
psql -d mayak --csv -c "select plan, count(*) from payments group by plan order by 1" > plans.csv

-- изнутри psql
\copy (select plan, count(*) from payments group by plan order by 1) to 'plans.csv' csv header

-- содержимое plans.csv на учебной базе:
-- plan,count
-- basic,706
-- pro,403
-- team,142

Пароль без ввода: .pgpass

Вводить пароль при каждом запуске скрипта неудобно, а писать его в команду небезопасно: он останется в истории оболочки. Для этого есть файл ~/.pgpass (на Windows — %APPDATA%\postgresql\pgpass.conf). Каждая строка — одно подключение в формате хост:порт:база:пользователь:пароль, звёздочка подходит к любому значению.

На macOS и Linux psql читает файл, только если доступ к нему есть у одного владельца: выполните chmod 600 ~/.pgpass. Иначе psql проигнорирует файл и снова спросит пароль.

db.example.ru:5432:mayak:analyst:придуманный-пароль
localhost:5432:*:postgres:другой-пароль

Настроить psql под себя: .psqlrc

При запуске psql выполняет команды из файла ~/.psqlrc. Туда кладут то, что иначе набираешь в каждой сессии: показывать время запросов, NULL заметным знаком, широкие строки столбиком. Достаточно трёх строк.

\timing on
\x auto
\pset null '∅'

Что значит приглашение psql

Приглашение подсказывает, в каком состоянии консоль. Если psql «не реагирует» на Enter, почти всегда дело в нём: запрос не закончен точкой с запятой или открыта кавычка.

• Сбросить набранный запрос, не выполняя его, — \r.
• Прервать долгий запрос — Ctrl+C: psql попросит сервер отменить его.
• Выйти — \q или Ctrl+D.

Частые ошибки

Частые вопросы

Писать запросы удобнее всего учиться на готовой базе в браузере: в песочнице или в курсе «SQL с нуля», где каждое решение проверяется.