Базы данных

Посмотрим список баз данных кластера, используя системный каталог: 

=> 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






Конец демонстрации.