Посмотрим список имеющихся баз:
postgres$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | 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
(3 rows)
Пока нас интересует только название, значение остальных полей рассмотрим позже.
Также можно посмотреть в самой базе данных:
=> SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database;
datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 (3 rows)
Подключимся к шаблонной базе template1.
=> \c template1
You are now connected to database "template1" as user "postgres".
Проверим, доступна ли функция digest, вычисляющая хеш-код текстовой строки.
=> SELECT digest('Hello, world!', 'md5');
ERROR: function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('Hello, world!', 'md5');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Такой функции нет.
На самом деле digest определена в пакете pgcrypto. Установим его:
=> CREATE EXTENSION pgcrypto;
CREATE EXTENSION
Если бы расширение pgcrypto не было установлено с помощью make install, мы получили бы ошибку "ERROR: could not open extension control file..."
Теперь нам доступны функции, входящие в расширение pgcrypto. Например, можно вычислить MD5-дайджест:
=> SELECT digest('Hello, world!', 'md5');
digest
------------------------------------
\x6cd3556deb0da54bca060b4c39479839
(1 row)
Чтобы шаблон можно было использовать для создания базы, к нему не должно быть активных подключений, поэтому отключимся от базы template1.
=> \c postgres
You are now connected to database "postgres" as user "postgres".
Для создания новой базы данных служит команда CREATE DATABASE:
=> CREATE DATABASE db;
CREATE DATABASE
=> \c db
You are now connected to database "db" as user "postgres".
Альтернативно можно было бы воспользоваться утилитой createdb.
=> SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database;
datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 db | f | t | -1 (4 rows)
Поскольку для создания по умолчанию используется шаблон template1, в ней также будет доступны функции пакета pgcrypto:
=> SELECT digest('Hello, world!', 'md5');
digest
------------------------------------
\x6cd3556deb0da54bca060b4c39479839
(1 row)
Созданную базу данных можно переименовать (к ней не должно быть подключений):
=> \c postgres
You are now connected to database "postgres" as user "postgres".
=> ALTER DATABASE db RENAME TO appdb;
ALTER DATABASE
=> SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database;
datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 appdb | f | t | -1 (4 rows)
Можно изменить и другие параметры, например:
=> ALTER DATABASE appdb CONNECTION LIMIT 10;
ALTER DATABASE
=> SELECT datname, datistemplate, datallowconn, datconnlimit FROM pg_database;
datname | datistemplate | datallowconn | datconnlimit -----------+---------------+--------------+-------------- postgres | f | t | -1 template1 | t | t | -1 template0 | t | f | -1 appdb | f | t | 10 (4 rows)
Размер базы данных можно узнать с помощью функции:
=> SELECT pg_database_size('appdb');
pg_database_size
------------------
7222887
(1 row)
Чтобы не считать разряды, можно вывести размер в читаемом виде:
=> SELECT pg_size_pretty(pg_database_size('appdb'));
pg_size_pretty ---------------- 7054 kB (1 row)
В этой базе данных еще нет пользовательских объектов (кроме расширения pgcrypto); фактически, это размер "пустой" базы.
Список схем можно узнать командой psql (dn = describe namespace):
=> \dn
List of schemas Name | Owner --------+---------- public | postgres (1 row)
Создадим новую схему:
=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> CREATE SCHEMA app;
CREATE SCHEMA
=> \dn
List of schemas Name | Owner --------+---------- app | postgres public | postgres (2 rows)
Если теперь создать таблицу (и не указать имя схемы), в какую схему она попадет?
Надо посмотреть на путь поиска.
=> SHOW search_path;
search_path ----------------- "$user", public (1 row)
Конструкция "$user" обозначает схему с тем же именем, что и имя текущего пользователя (в нашем случае - postgres). Поскольку такой схемы нет, она игнорируется.
Чтобы не думать над тем, какие схемы есть, каких нет, и какие не указаны явно, можно воспользоваться функцией:
=> SELECT current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
Теперь создадим таблицу:
=> CREATE TABLE t(s text);
CREATE TABLE
=> INSERT INTO t VALUES ('Я - таблица t');
INSERT 0 1
Список таблиц можно получить командой \dt:
=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
(1 row)
Объект можно перемещать между схемами. Поскольку речь идет о логической организации, перемещение происходит только в системном каталоге; сами данные физически остаются на месте.
=> ALTER TABLE t SET SCHEMA app;
ALTER TABLE
Теперь к таблице t можно обращаться с явным указанием схемы:
=> SELECT * FROM app.t;
s
---------------
Я - таблица t
(1 row)
Но если опустить имя схемы, таблица не будет найдена:
=> SELECT * FROM t;
ERROR: relation "t" does not exist
LINE 1: SELECT * FROM t;
^
Установим путь поиска, например, так:
=> SET search_path = public, app;
SET
Теперь таблица будет найдена.
=> SELECT * FROM t;
s
---------------
Я - таблица t
(1 row)
Здесь мы установили конфигурационный параметр на уровне сеанса (при переподключении значение пропадет). Устанавливать такое значение на уровне всего кластера тоже не правильно - возможно, этот путь нужен не всегда и не всем.
Но параметр можно установить и на уровне отдельной базы данных:
=> ALTER DATABASE appdb SET search_path = public, app;
ALTER DATABASE
Теперь он будет устанавливаться для всех новых подключений к БД appdb. Проверим:
=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> SHOW search_path;
search_path ------------- public, app (1 row)
=> SELECT current_schemas(true);
current_schemas
-------------------------
{pg_catalog,public,app}
(1 row)
Создадим временную таблицу:
=> CREATE TEMP TABLE t(s text);
CREATE TABLE
=> \dt
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_3 | t | table | postgres
(1 row)
Таблица создана в специальной схеме. Каждому сеансу выделяется отдельная "временная" схема, так что он может видеть только свои собственные временные таблицы.
Но куда пропала обычная таблица t?
Ответ дает развернутый путь поиска: в него теперь подставлена временная схема, и объект в ней "перекрывает" одноименный объект схемы app.
=> SELECT current_schemas(true);
current_schemas
-----------------------------------
{pg_temp_3,pg_catalog,public,app}
(1 row)
=> INSERT INTO t VALUES ('Я - временная таблица');
INSERT 0 1
Тем не менее, к каждой из таблиц можно обращаться с явным указанием схемы. Для временной таблицы надо использовать псевдосхему pg_temp - она автоматически отображается в нужную схему pg_temp_N:
=> SELECT * FROM app.t;
s
---------------
Я - таблица t
(1 row)
=> SELECT * FROM pg_temp.t;
s
-----------------------
Я - временная таблица
(1 row)
В принципе, во временной схеме можно создавать не только таблицы.
=> CREATE VIEW v AS SELECT * FROM pg_temp.t;
NOTICE: view "v" will be a temporary view CREATE VIEW
Временные таблицы и данные в них могут иметь различные сроки жизни (в зависимости от указания ON COMMIT DELETE/PRESERVE/DROP). В любом случае при переподключении все объекты во временной схеме уничтожаются:
=> \c appdb
You are now connected to database "appdb" as user "postgres".
=> SELECT current_schemas(true);
current_schemas
-------------------------
{pg_catalog,public,app}
(1 row)
=> SELECT * FROM pg_temp.v;
ERROR: relation "pg_temp.v" does not exist
LINE 1: SELECT * FROM pg_temp.v;
^
=> SELECT * FROM pg_temp.t;
ERROR: relation "pg_temp.t" does not exist
LINE 1: SELECT * FROM pg_temp.t;
^
Схему нельзя удалить, если в ней находятся какие-либо объекты:
=> DROP SCHEMA app;
ERROR: cannot drop schema app because other objects depend on it DETAIL: table t depends on schema app HINT: Use DROP ... CASCADE to drop the dependent objects too.
Но можно удалить схему вместе со всеми ее объектами:
=> DROP SCHEMA app CASCADE;
NOTICE: drop cascades to table t DROP SCHEMA
Базу данных можно удалить, если к ней нет активных подключений.
=> \conninfo
You are connected to database "appdb" as user "postgres" via socket in "/tmp" at port "5432".
=> \c postgres
You are now connected to database "postgres" as user "postgres".
=> DROP DATABASE appdb;
DROP DATABASE
Конец демонстрации.