Посмотрим список баз данных кластера, используя системный каталог:
=> SELECT datname FROM pg_database;
datname ----------- postgres student template1 template0 (4 rows)
Ту же информацию можно получить специальной командой psql (она выводит много полей, которые нас не интересуют):
=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
student | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Мы всегда можем посмотреть, какие запросы выполняет команда:
=> \set ECHO_HIDDEN on
=> \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
student | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Отключим вывод команд.
=> \set ECHO_HIDDEN off
Когда мы создаем новую базу данных, она (по умолчанию) копируется из шаблона template1.
=> CREATE DATABASE test;
CREATE DATABASE
=> \c test
You are now connected to database "test" as user "student".
=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
student | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
Список схем можно узнать в системном каталоге:
=> SELECT nspname FROM pg_namespace;
nspname
--------------------
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema
(6 rows)
Про некоторые из перечисленных схем (public, pg_catalog, information_schema) мы уже говорили; про остальные поговорим позже в других темах.
В psql есть специальная команда (dn = describe namespace):
=> \dn
List of schemas Name | Owner --------+---------- public | postgres (1 row)
Команда не показывает служебные схемы. Чтобы увидеть их, нужно добавить модификатор S (он работает аналогичным образом и для многих других команд):
=> \dnS
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
(6 rows)
Еще один полезный модификатор - "плюс", который выводит дополнительную информацию:
=> \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)
Создадим новую схему (для каких-то специальных объектов):
=> CREATE SCHEMA special;
CREATE SCHEMA
=> \dn
List of schemas Name | Owner ---------+---------- public | postgres special | student (2 rows)
Если теперь создать таблицу (и не указать имя схемы), в какую схему она попадет?
Надо посмотреть на путь поиска.
=> SHOW search_path;
search_path ----------------- "$user", public (1 row)
Конструкция "$user" обозначает схему с тем же именем, что и имя текущего пользователя (в нашем случае - student). Поскольку такой схему нет, она игнорируется.
Чтобы не думать над тем, какие схемы есть, каких нет, и какие не указаны явно, можно воспользоваться функцией:
=> SELECT current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
Теперь создадим таблицу:
=> CREATE TABLE t(n integer);
CREATE TABLE
Список таблиц можно получить командой \dt:
=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | t | table | student
(1 row)
А вот как можно было бы получить список объектов в схеме public из таблиц системного каталога:
=> SELECT relname, relnamespace FROM pg_class WHERE relnamespace = 'public'::regnamespace;
relname | relnamespace ---------+-------------- t | 2200 (1 row)
Поле relnamespace имеет тип OID; вот соответствующая строка таблицы pg_namespace:
=> SELECT oid, nspname FROM pg_namespace WHERE nspname = 'public';
oid | nspname ------+--------- 2200 | public (1 row)
Преобразование имени схемы к типу regnamespace позволяет упростить запрос и обойтись без явного соединения таблиц. Аналогичные reg-типы определены и для некоторых других таблиц системного каталога.
Объект можно перемещать между схемами. Поскольку речь идет о логической организации, перемещение происходит только в системном каталоге; сами данные физически остаются на месте.
=> ALTER TABLE t SET SCHEMA special;
ALTER TABLE
Воспользуемся тем, что в команде \dt можно указывать шаблон для имен схем и таблиц:
=> \dt public.*
No matching relations found.
=> \dt special.*
List of relations
Schema | Name | Type | Owner
---------+------+-------+---------
special | t | table | student
(1 row)
Теперь к таблице t можно обращаться с явным указанием схемы:
=> SELECT * FROM special.t;
n --- (0 rows)
Но если опустить имя схемы, таблица не будет найдена:
=> SELECT * FROM t;
ERROR: relation "t" does not exist
LINE 1: SELECT * FROM t;
^
Установим путь поиска, например, так:
=> SET search_path = public, special;
SET
Теперь таблица будет найдена.
=> SELECT * FROM t;
n --- (0 rows)
Здесь мы установили конфигурационный параметр на уровне сеанса (при переподключении значение пропадет). Устанавливать такое значение на уровне всего кластера тоже не правильно - возможно, этот путь нужен не всегда и не всем.
Но параметр можно установить и на уровне отдельной базы данных:
=> ALTER DATABASE test SET search_path = public, special;
ALTER DATABASE
Теперь он будет устанавливаться для всех новых подключений к БД test. Проверим:
=> \c test
You are now connected to database "test" as user "student".
=> SHOW search_path;
search_path ----------------- public, special (1 row)
Схему нельзя удалить, если в ней находятся какие-либо объекты:
=> DROP SCHEMA special;
ERROR: cannot drop schema special because other objects depend on it DETAIL: table t depends on schema special HINT: Use DROP ... CASCADE to drop the dependent objects too.
Но можно удалить схему вместе со всеми ее объектами:
=> DROP SCHEMA special CASCADE;
NOTICE: drop cascades to table t DROP SCHEMA
Базу данных можно удалить, если к ней нет активных подключений.
=> \conninfo
You are connected to database "test" as user "student" via socket in "/var/run/postgresql" at port "5432".
=> \c postgres
You are now connected to database "postgres" as user "student".
=> DROP DATABASE test;
DROP DATABASE
Конец демонстрации.