Создание базы, таблицы и GUI-клиенты

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

Здание, этаж, комната, шкаф

Чтобы найти документ в большом офисе, нужен полный адрес: здание, этаж, комната, шкаф. В PostgreSQL у каждой таблицы тоже есть адрес из четырёх уровней:

кластер (сервер)              здание
└── база данных bookshop      этаж
    └── схема public          комната
        └── таблица books     шкаф

Про кластер мы уже знаем: это запущенный сервер со всеми базами. Две новые ступени, база и схема, похожи друг на друга, но ведут себя по-разному.

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

Схема это комната на этаже. Двери между комнатами открыты: в одном запросе можно взять таблицы из разных схем, если указать полный адрес схема.таблица. Схемы помогают разложить таблицы большого приложения по смыслу, например billing и catalog.

Создаём базу

Подключись к кластеру через psql, как в прошлых уроках, и выполни:

CREATE DATABASE bookshop;

CREATE DATABASE

Новая база копируется с заготовки template1, так что сразу после создания она пустая, но уже со схемой public. Переключаемся в неё:

postgres=# \c bookshop
You are now connected to database "bookshop" as user "postgres".
bookshop=#

Две особенности, о которые спотыкаются.

Во-первых, CREATE DATABASE одно из немногих исключений из транзакционного DDL. Его нельзя выполнить внутри транзакции:

ERROR:  CREATE DATABASE cannot run inside a transaction block

Во-вторых, база с таким именем может уже существовать, и тогда сервер откажет:

ERROR:  database "bookshop" already exists

Базу удаляет команда DROP DATABASE bookshop;. Подтверждения не будет, восстановить данные потом можно только из резервной копии. Поэтому перед DROP всегда смотри на приглашение psql и проверяй, в какой кластер ты подключён.

Схема public и путь поиска

Создадим первую таблицу:

CREATE TABLE books (
  id     integer PRIMARY KEY,
  title  text    NOT NULL,
  author text    NOT NULL,
  price  numeric(8, 2)
);

Схему мы не указали, а таблица всё равно куда-то попала. \dt покажет public | books. Почему public? Сервер ищет схему по списку, который называется search_path:

SHOW search_path;

   search_path
-----------------
 "$user", public
(1 row)

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

Если таблица лежит в схеме вне этого списка, по короткому имени её не найти:

CREATE SCHEMA warehouse;
CREATE TABLE warehouse.stock (book_id integer, qty integer);

SELECT * FROM stock;

ERROR:  relation "stock" does not exist

SELECT * FROM warehouse.stock работает. Для новичкового проекта хватает одной схемы public, и в этом разделе мы почти всегда будем работать в ней.

Слово relation в ошибке значит «таблица или что-то похожее на неё». Название пришло из реляционной модели, где таблица называется отношением.