Работаем с PostgreSQL из psql

Apr 11, 2016 23:51 · 2249 words · 11 minute read postgres перевод

Оригинал: ‘PostgreSQL on the Command Line’ by Philipe Fatio

Мне настолько понравилась эта статья, что я решил её перевести. Получилось весьма вольно, но сама суть сохранена.

Я уже больше 4 лет работаю с PostgreSQL. В начале использовал графические программы, сейчас же обхожусь только встроенными утилитами командной строки. Так получается более эффективно.

Этот пост разделён на несколько секций согласно задачам, которые можно решать с помощью psql:

настройка psql
получение справки
возня в песочнице
описание БД
построение запросов
сравнение вывода
клонирование базы данных
получение данных

Настройка psql

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

db=# SELECT 'hello' AS foo, bar FROM generate_series(1, 2) AS bar;
  foo  | bar
-------+-----
 hello |   1
 hello |   2
(2 rows)

Пока ширина таблицы меньше ширины экрана всё нормально, но потом строки начнут переноситься и вывод станет просто отвратительным:

db=# SELECT 'really long string messing with the output' AS foo, bar, 
'another long string making things worse' AS baz FROM generate_series(1,
 2) AS bar;
                    foo                     | bar |                   ba
z
--------------------------------------------+-----+---------------------
--------------------
 really long string messing with the output |   1 | another long string 
making things worse
 really long string messing with the output |   2 | another long string 
making things worse
(2 rows)

Чтобы избежать этого воспользуемся командой активации “расширенного дисплея” (expanded display):

db=# \x
Expanded display is on.

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

db=# SELECT 'really long string messing with the output' AS foo, bar, 
'another long string making things worse' AS baz FROM generate_series(1,
 2) AS bar;
-[ RECORD 1 ]-----------------------------------
foo | really long string messing with the output
bar | 1
baz | another long string making things worse
-[ RECORD 2 ]-----------------------------------
foo | really long string messing with the output
bar | 2
baz | another long string making things worse

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

Вторая возможность - указать как будет выводиться значение NULL. По умолчанию оно неотличимо от пустой строки.

db=# SELECT '', NULL;
 ?column? | ?column?
----------+----------
          |
(1 row)

Это можно исправить задав в psql символ для визуализации: \pset null ¤. Теперь можно быть уверенным, что если в строке пусто, то это пустая строка, а не NULL.

db=# SELECT '', NULL;
 ?column? | ?column?
----------+----------
          | ¤
(1 row)

Хотел бы рассказать ещё об одной фиче psql. Если вам, как и мне, нравятся SQL выражения в ALL-CAPS, то это можно настроить в автодополнении командой \set COMP_KEYWORD_CASE upper. Теперь, когда будете набирать SQL-запрос, последовательность sel<tab> будет автоматически преобразовываться в SELECT<space>.

Конечно, прописывать все эти команды каждый раз при запуске psql будет слишком утомительным, так что я предлагаю прописать их один раз в ~/.psqlrc, который выполняется при каждой новой сессии psql.

\set COMP_KEYWORD_CASE upper
\x auto
\pset null ¤

Это лишь самая малость команд для настройки psql. Гораздо больше вы можете найти в th и psql’s doc.

Получение справки

Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через \h.

\h без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис SELECT или ALTER TABLE. Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. \h alter поможет в этом и предоставит более чем подробную информацию. psql использует пагинацию для большого вывода, так что там работает поиск. Наберите / + текст для поиска + <enter> и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно. Поискав NULL, находится вот такая команда: ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL. Теперь можно составить нужную команду, не заглядывая в Google.

ALTER TABLE table_name ALTER column_name DROP NOT NULL;

Как видите, \h даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть \?.

Ваша песочница

В psql заданы настройки по умолчанию для подключения к базе данных. Чтобы подключиться к локальной БД достаточно набрать psql db_name. Вы можете не указывать даже имя базы данных. psql трактует это как подключение к БД с именем текущего пользователя. Это позволяет быстро попасть в psql-сессию.

$ psql
psql: FATAL:  database "phil" does not exist
$ createdb `whoami`
$ psql
phil=#

Мне удобно бывает просто набрать psql и проверить синтаксис запроса или прочитать справку без необходимости соединения с какой-нибудь реальной БД.

Описание БД

psql имеет достаточное количество специальных команд, которые помогают проще ориентироваться в базе данных. Для списка таблиц наберите \dt, для списка индексов - \di, представлений - \dv и т.д. Приятно, что все эти команды параметром принимают шаблон, по которому будет производиться фильтрация. Т.е. если вас интересуют только таблицы, начинающиеся на user, то выполните \dt user\*. Там, где я работаю, частенько используются схемы. Грубо говоря это пространство имён для таблиц. По умолчанию есть только одна схема public. Когда вы создаёте там таблицу foo, обратиться к ней можно будет через public.foo. В нашей же компании для каждого клиента используется своя схема. Иногда мне нужно проверить есть ли в схеме клиента определённая таблица (например, users). Для этого мне достаточно набрать \dt \*.users, и я получу список всех таблиц с указанием схем.

db=# \dt *.users
         List of relations
  Schema  | Name  | Type  | Owner
----------+-------+-------+-------
 apple    | users | table | phil
 google   | users | table | phil
 facebook | users | table | phil
(3 rows)

Для получения информации о конкретном объекте таблицы или представления служит команда \d. Она показывает следующие вещи,

список столбцов вместе с их типами и значениями по умолчанию
индексы
ограничения
внешние ключи
триггеры

db=# \d users
                          Table "public.users"
  Column  |  Type   |                     Modifiers
----------+---------+----------------------------------------------------
 id       | integer | not null default nextval('users_id_seq'::regclass)
 name     | text    | not null
 email    | text    | not null
 group_id | integer | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "users_email_check" CHECK (email ~ '.@.'::text)
    "users_name_check" CHECK (name <> ''::text)
Foreign-key constraints:
    "users_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id)
Referenced by:
    TABLE "posts" CONSTRAINT "posts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

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

db=# \df *regexp*
                                    List of functions
   Schema   |         Name          | Result data type |  Argument data types   |  Type
------------+-----------------------+------------------+------------------------+--------
 pg_catalog | regexp_matches        | SETOF text[]     | text, text             | normal
 pg_catalog | regexp_matches        | SETOF text[]     | text, text, text       | normal
 pg_catalog | regexp_replace        | text             | text, text, text       | normal
 pg_catalog | regexp_replace        | text             | text, text, text, text | normal
 pg_catalog | regexp_split_to_array | text[]           | text, text             | normal
 pg_catalog | regexp_split_to_array | text[]           | text, text, text       | normal
 pg_catalog | regexp_split_to_table | SETOF text       | text, text             | normal
 pg_catalog | regexp_split_to_table | SETOF text       | text, text, text       | normal
(8 rows)

Когда работаете со своими собственными функциями, то простого вывода сигнатуры оказывается мало. Тут на помощь приходит команда \ef для редактирования. Передайте ей имя редактируемой функции в качестве первого параметра, и она откроется в $EDITOR. Если определение функции было задано с помощью CREATE OR REPLACE FUNCTION, то после сохранения и закрытия редактора она будет обновлена. Если же вам нужно только посмотреть описание функции, то закройте редактор с помощью ненулевого кода возврата (для vim это :cq). Таким образом она не будет обновлена и выполнена. psql в таком случае понимает, что что-то пошло не так, и не будет ничего делать.

Построение запросов

psql превосходно подходит для построения небольших запросов, однако для многострочных и вложенных надо использовать более адекватный инструмент. Например, \e - она открывает последний запрос в вашем любимом редакторе, который может уже обеспечить и подсветку синтаксиса, и автодополнение, и прочие полезные вещи. Для выполнения достаточно сохранить этот псевдо-файл и выйти (ну прям как с git). \e удобно использовать для итеративного построения большого запроса.

В режиме такого редактирования можно также сохранить запрос в какой-нибудь отдельный файл на диске. Открыть же его можно с помощью команды \i filename.sql. psql прочитает запрос из filename.sql и выполнит его.

\e работает только с уже существующими файлами. Передавая имя в качестве аргумента, вы откроете файл в текстовом редакторе и выполните содержимое при выходе. Но как быть когда вы только начинаете писать новый запрос? Выходить из psql, создавать файл и возвращаться обратно слишком сложно. Можно выполнить команду оболочки прямо из psql с помощью \!, например, \! touch foo.sql.

\i и \e существенно упрощают работу над сложными sql запросами. Лично у меня открыто 2 окна side-by-side: один с vim, а другой с psql, где я выполняю только \i query.sql. Переключаюсь - правлю, переключаюсь - выполняю. Прошли те времена, когда я копипастил из java-приложений с помощью незнакомых сочетаний горячих клавиш.

Сравнение вывода

Когда вы запускаете команду с помощью psql, результат оказывается в том же терминале. Команда \o позволяет указать имя файла, куда будет записан вывод. \o output.txt перенаправит вывод, а \o вернёт всё обратно.

Ну и зачем это надо? Некоторые используют для экспорта данных (позже покажу способ получше). Я же использую \o для сравнение чего-либо тяжёлого, например, планов выполнения с помощью \! vimdiff.

db=# \o a.txt
db=# EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM groups WHERE name = 'admins');
db=# \o b.txt
db=# EXPLAIN SELECT users.* FROM users LEFT JOIN groups WHERE groups.name = 'admins';
db=# \! vimdiff a.txt b.txt

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

Клонирование базы данных

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

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

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

$ createdb -T app_db app_db_backup

Для восстановления удалим существующую и склонируем обратно.

$ dropdb app_db
$ createdb -T app_db_backup app_db

Получение данных

Зачастую нам нужно поделиться данными из postgres с сотрудниками, руководством или клиентами, причём желательно в каком-нибудь удобочитаемом формате типа CSV или Excel. Вы уже подготовили запрос, свели всё в одну таблицу, осталось только куда-нибудь это выгрузить. Погуглив psql csv export, можно найти 2 способа. Первый более примитивный.

CSV фйалы представляют собой просто строки со значениями, которые разделены запятыми. Таким образом, указав psql как форматировать вывод, можно получить похожую структуру. Тут есть одна большая проблема - если ваши данные содержат запятую, то её нужно экранировать, а т.к. количество полей может быть огромным, то такой способ ведёт в тупик.

Правильным способом будет выгрузка в CSV с помощью мета-команды \copy или SQL команды COPY. выглядит это примерно так:

COPY (SELECT ...)
TO '/absolute/path/export.csv'
WITH (FORMAT csv, HEADER true);

COPY накладывает несколько ограничений. Во-первых, путь к файлу должен быть абсолютным. Во-вторых, вы можете писать только на локальную файловую систему. То есть подключиться к удалённой БД и выгрузить данные на локальный компьютер не получится. И тут на помощь приходит \copy, которая представляет из себя всего лишь более удобную оболочку для COPY. Запрос выше можно переписать так:

\copy (SELECT ...) TO export.csv WITH (FORMAT csv, HEADER true)

Обратите внимание, что используется относительный путь до файла. Также можно задать и другие параметры CSV. Единственное ограничение состоит в том, что команда должна быть одной строкой. Причина в том, что окончанием выражения для команды с \ является перевод строки, а не точка с запятой. Это не касается \e - там управление передаётся в редактор. Под капотом \copy выполняет всё тот же COPY, перенаправляя вывод в STDOUT вместо файла. Далее psql забирает со STDOUT и записывает в локальный файл. Если вам нужно выгрузить данные в Excel, то убедитесь, что задали правильную кодировку. Он не дружит с UTF-8, так что лучше откатиться до latin1 (2015 год на дворе, я солидарен с негодованиями автора):

\copy (SELECT ...) TO export.csv WITH (FORMAT csv, HEADER true, ENCODING 'latin1')

Если sql-запрос у вас находится в файле, то использовать его для команды \copy не получится. Вам придётся скопировать всё тело запроса в выражение в команде, удалив переносы строк.

Наверно, я вам уже надоел, так что закругляюсь. Напоследок хотелось бы ещё рассказать про утилиту psql2csv. Она позволяет запускать запрос из файла и получать отформатированный CSV в STDOUT (который можно потом перенаправить в файл):

$ psql2csv database 'SELECT ...' > export.csv

В случае запроса из файла:

$ psql2csv database < query.sql > export.csv

Параметры вызова утилиты совпадают с параметрами psql. Вы можете заменить вызов psql на psql2csv, передав запрос в качестве аргумента, а на выходе получить валидный CSV. Но это ещё не всё - почему бы не подать вывод на вход какой-нибудь другой утилите?! psql2csv также принимает аргументы --no-header --encoding=ENCODING для совместимости с Excel.

Я уже очень привык работать с PostgreSQL через командную строку. Всё, что раньше делал в pgAdmin, можно делать и тут, причём быстрее. Я надеюсь, что эта статья убедила вас сделать psql основным инструментом для работы с PostgreSQL, показав удобство и гибкость.

Если у вас есть какие-то замечания или дополнения, пожалуйста, напишите!