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

Запуск

$ psql -d база -U пользователь -h узел -p порт

Новое подключение в psql

=> \c[onnect] база пользователь узел порт

Информация о текущем подключении

=> \conninfo

При запуске psql определяет параметры подключения: имя базы данных, имя пользователя, имя сервера, номер порта. Если эти параметры не указаны, psql попробует подключиться, используя

значения по умолчанию:

• база — совпадает с именем пользователя;
• пользователь — совпадает с именем пользователя ОС;
• узел — соединение через Unix-socket;
• порт — обычно 5432.

Если требуется выполнить новое подключение не выходя из psql, нужно выполнить команду \connect. Значения по умолчанию для нее берутся из текущего соединения. Команда \conninfo выдает информацию о текущем подключении.

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

В командной строке ОС

$ psql --help

$ man psql

В psql

=> \? список команд psql

=> \? variables переменные psql

=> \h[elp] список команд SQL

=> \h команда синтаксис команды SQL

=> \q выход

Справочную информацию по psql можно получить не только в документации, но и прямо в системе.

psql с ключом --help выдает справку по запуску. А если в системе была установлена документация, то справочное руководство можно получить командой man psql.

psql умеет выполнять команды SQL и свои собственные команды. Все команды psql начинаются с обратной косой черты и, как правило, их можно сокращать до первой буквы. Внутри psql есть возможность получить список и краткое описание команд psql: \?.

Команда \help выдает список команд SQL, которые поддерживает сервер, а также синтаксис конкретной команды SQL.

И еще одна команда, которую полезно знать, хоть она и не имеет отношения к справке: это \q — выход из psql. В качестве альтернативы для выхода можно использовать команды exit и quit.

Выполнение команд SQL и форматирование результатов

Запускаем psql:

student$ psql

Проверим текущее подключение:

=> \conninfo

You are connected to database "student" as user "student" via socket in

"/var/run/postgresql" at port "5432".

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

Команда \c[onnect] выполняет новое подключение, не покидая psql.

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

=> SELECT schemaname, tablename, tableowner

FROM pg_tables

LIMIT 5;

schemaname | tablename | tableowner

------------+-----------------------+------------

pg_catalog | pg_statistic | postgres

pg_catalog | pg_type | postgres

pg_catalog | pg_foreign_table | postgres

pg_catalog | pg_authid | postgres

pg_catalog | pg_statistic_ext_data | postgres

(5 rows)

Утилита psql умеет выводить результат запросов в разных форматах. Вот только некоторые из них:

• формат с выравниванием значений;
• формат без выравнивания;
• расширенный формат.

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

Команды psql для переключения режима выравнивания:

• \a — переключает режим с выравниванием и без выравнивания;
• \t — переключает отображения строки заголовка и итоговой строки.

Отключим выравнивание, вывод заголовка и итоговой строки, а в качестве разделителя столбцов установим пробел:

=> \t \a

Tuples only is on.

Output format is unaligned.

=> \pset fieldsep ' '

Field separator is " ".

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

pg_catalog pg_statistic postgres

pg_catalog pg_type postgres

pg_catalog pg_foreign_table postgres

pg_catalog pg_authid postgres

pg_catalog pg_statistic_ext_data postgres

=> \t \a

Tuples only is off.

Output format is aligned.

Расширенный формат удобен, когда нужно вывести много столбцов для одной или нескольких записей:

=> \x

Expanded display is on.

=> SELECT * FROM pg_tables WHERE tablename = 'pg_class';

-[ RECORD 1 ]-----------

schemaname | pg_catalog

tablename | pg_class

tableowner | postgres

tablespace |

hasindexes | t

hasrules | f

hastriggers | f

rowsecurity | f

=> \x

Expanded display is off.

Расширенный формат можно установить только для одного запроса, если в конце вместо точки с запятой указать \gx:

=> SELECT * FROM pg_tables WHERE tablename = 'pg_proc' \gx

-[ RECORD 1 ]-----------

schemaname | pg_catalog

tablename | pg_proc

tableowner | postgres

tablespace |

hasindexes | t

hasrules | f

hastriggers | f

rowsecurity | f

Все возможности форматирования результатов запросов доступны через команду \pset. А без параметров она покажет текущие настройки форматирования:

=> \pset

border 1

columns 0

csv_fieldsep ','

expanded off

fieldsep ' '

fieldsep_zero off

footer on

format aligned

linestyle ascii

null ''

numericlocale off

pager 1

pager_min_lines 0

recordsep '\n'

recordsep_zero off

tableattr

title

tuples_only off

unicode_border_linestyle single

unicode_column_linestyle single

unicode_header_linestyle single

xheader_width full

Взаимодействие с ОС

В psql можно выполнять команды shell:

=> \! pwd

/home/student

Можно установить переменную окружения операционной системы:

=> \setenv HELLO Hello

=> \! echo $HELLO

Hello

Можно записать вывод команды в файл с помощью \o[ut]:

=> \o tmp/dba1_log

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

На экран ничего не попало. Посмотрим в файле:

=> \! cat tmp/dba1_log

schemaname | tablename | tableowner

------------+-----------------------+------------

pg_catalog | pg_statistic | postgres

pg_catalog | pg_type | postgres

pg_catalog | pg_foreign_table | postgres

pg_catalog | pg_authid | postgres

pg_catalog | pg_statistic_ext_data | postgres

(5 rows)

Вернем вывод на экран:

=> \o

Выполнение скриптов

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

Вывод запроса можно направить команде ОС, если указать указать ее после вертикальной черты. Например, можно вывести результат запроса на экран, пронумеровав строки:

=> SELECT format('SELECT count(*) FROM %I;', tablename)

FROM pg_tables

LIMIT 3

\g (tuples_only=on format=unaligned) | cat -n

1SELECT count(*) FROM pg_statistic;

2SELECT count(*) FROM pg_type;

3SELECT count(*) FROM pg_foreign_table;

В команде \g можно указать имя файла, в который будет направлен вывод:

=> SELECT format('SELECT count(*) FROM %I;', tablename)

FROM pg_tables

LIMIT 3

\g (tuples_only=on format=unaligned) tmp/dba1_log

Вот что получилось в файле:

=> \! cat tmp/dba1_log

SELECT count(*) FROM pg_statistic;

SELECT count(*) FROM pg_type;

SELECT count(*) FROM pg_foreign_table;

Выполняем теперь этот файл как скрипт с помощью \i[nclude]:

=> \i tmp/dba1_log

count

-------

409

(1 row)

count

-------

613

(1 row)

count

-------

0

(1 row)

Другие способы выполнить команды из файла:

psql < filename

psql -f filename

В предыдущем примере можно обойтись и без создания файла, если завершить запрос командой \gexec:

=> SELECT format('SELECT count(*) FROM %I;', tablename)

FROM pg_tables

LIMIT 3

\gexec

count

-------

409

(1 row)

count

-------

613

(1 row)

count

-------

0

(1 row)

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

Переменные psql и управляющие конструкции

По аналогии с shell, psql имеет собственные переменные, среди которых есть ряд встроенных (имеющих особый смысл для psql).

Запомним в переменной psql User значение переменной окружения USER:

=> \getenv User USER

И установим переменную Test:

=> \set Test Hi

Чтобы подставить ее значение, надо предварить имя переменной двоеточием:

=> \echo :Test :User!

Hi student!

Сбросить переменную можно так:

=> \unset Test

=> \echo :Test

:Test

Результат запроса можно записать в переменную. Для этого запрос нужно завершить командой \gset:

=> SELECT now() AS curr_time \gset

=> \echo :curr_time

2024-07-08 14:50:19.126332+03

Запрос должен возвращать только одну запись.

Без параметров \set выдает значения всех установленных переменных:

=> \set

AUTOCOMMIT = 'on'

COMP_KEYWORD_CASE = 'preserve-upper'

DBNAME = 'student'

ECHO = 'none'

ECHO_HIDDEN = 'off'

ENCODING = 'UTF8'

ERROR = 'false'

FETCH_COUNT = '0'

HIDE_TABLEAM = 'off'

HIDE_TOAST_COMPRESSION = 'off'

HISTCONTROL = 'none'

HISTFILE = 'hist'

HISTSIZE = '500'

HOST = '/var/run/postgresql'

IGNOREEOF = '0'

LAST_ERROR_MESSAGE = ''

LAST_ERROR_SQLSTATE = '00000'

ON_ERROR_ROLLBACK = 'off'

ON_ERROR_STOP = 'off'

PORT = '5432'

PROMPT1 = '%/%R%x%# '

PROMPT2 = '%/%R%x%# '

PROMPT3 = '>> '

QUIET = 'off'

ROW_COUNT = '1'

SERVER_VERSION_NAME = '16.3 (Ubuntu 16.3-1.pgdg22.04+1)'

SERVER_VERSION_NUM = '160003'

SHELL_ERROR = 'false'

SHELL_EXIT_CODE = '0'

SHOW_ALL_RESULTS = 'on'

SHOW_CONTEXT = 'errors'

SINGLELINE = 'off'

SINGLESTEP = 'off'

SQLSTATE = '00000'

USER = 'student'

User = 'student'

VERBOSITY = 'default'

VERSION = 'PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled

by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit'

VERSION_NAME = '16.3 (Ubuntu 16.3-1.pgdg22.04+1)'

VERSION_NUM = '160003'

curr_time = '2024-07-08 14:50:19.126332+03'

В скриптах можно использовать условные операторы.

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

=> \echo :{?working_dir}

FALSE

Следующий условный оператор psql проверяет существование переменной и при необходимости устанавливает значение по умолчанию:

=> \if :{?working_dir}

-- переменная определена

\else

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

\set working_dir `pwd`

\endif

Теперь мы можем быть уверены, что переменная working_dir определена:

=> \echo :working_dir

/home/student

Команды для работы с системным каталогом

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

Например:

=> \d pg_tables

View "pg_catalog.pg_tables"

Column | Type | Collation | Nullable | Default

-------------+---------+-----------+----------+---------

schemaname | name | | |

tablename | name | | |

tableowner | name | | |

tablespace | name | | |

hasindexes | boolean | | |

hasrules | boolean | | |

hastriggers | boolean | | |

rowsecurity | boolean | | |

Подробнее такие команды будут рассмотрены позже.

Настройка psql

При запуске psql выполняются два скрипта (при их наличии):

• сначала общий системный скрипт psqlrc;
• затем пользовательский файл .psqlrc.

Пользовательский файл должен располагаться в домашнем каталоге, а расположение системного скрипта можно узнать командой:

student$ pg_config --sysconfdir

/etc/postgresql-common

По умолчанию оба файла отсутствуют.

В эти файлы можно поместить команды для настройки сеанса, например:

• приглашение psql;
• программу постраничного просмотра результатов запросов;
• переменные для хранения текста часто используемых команд.

Для примера запишем в переменную top5 текст запроса на получение пяти самых больших по размеру таблиц:

=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Для выполнения запроса достаточно набрать:

=> :top5

tablename | bytes

----------------+---------

pg_proc | 1245184

pg_rewrite | 745472

pg_attribute | 720896

pg_description | 630784

pg_statistic | 294912

(5 rows)

Если записать эту команду \set в файл ~/.psqlrc, переменная top5 будет доступна сразу после запуска psql.

Благодаря поддержке readline, в psql работает автодополнение ключевых слов и имен объектов, а также сохраняется история команд. Имя и размер файла истории настраиваются переменными HISTFILE, HISTSIZE.

Итоги

psql — терминальный клиент для работы с СУБД

При запуске требуются параметры подключения

Выполняет команды SQL и psql

Содержит инструменты для интерактивной работы, а также для подготовки и выполнения скриптов