Что такое схема в postgresql
Перейти к содержимому

Что такое схема в postgresql

  • автор:

SQL-Ex blog

Схемы в PostgreSQL. Изучаем PostgreSQL вместе с Grant Fritchey

Добавил Sergey Moiseenko on Среда, 9 августа. 2023

Важным аспектом при построении и обслуживании базы данных является организация объектов в вашей базе данных. У вас могут быть таблицы, которые обслуживают различные направления, например, схема для операций с хранилищами данных и схема для продаж. Некоторым логинам может потребоваться доступ к определенным таблицам, но не к остальным. Вы можете захотеть изолировать одно множество объектов в базе данных от других множеств объектов. Все это, и многое другое, может быть выполнено при помощи схем в базе данных, и PostgreSQL поддерживает использование схемы именно для этих типов функциональности.

В тестовой базе данных, которую я использую для примеров к этой серии статей, была создана пара схем с таблицами в каждой из них. Вы можете посмотреть на эту базу данных в скрипте CreateDatabase.sql. Остальной код для этой статьи находится в папке 08_Schema.

Обслуживание схемы

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

Когда вы создаете объект типа таблицы, он автоматически приписывается схеме по умолчанию, если не задано обратное. По умолчанию все логины в базе данных имеют доступ к схеме public (PostgreSQL 15 изменил это поведение по умолчанию, поэтому теперь пользователи не имеют прав на создание объектов в схеме public). Помимо этого поведения по умолчанию, схема public является просто одной из схем в базе данных, и большинство функций и правил, которые будут далее обсуждаться, применимы к этой схеме.

Для начала создадим свои собственные схемы. Синтаксис очень простой:

CREATE SCHEMA mytestschema;

Этот оператор создает схему с именем mytestschema. Для создания таблицы в этой схеме вы просто используете имя таблицы из двух частей (имя_схемы.имя_таблицы) в операторе CREATE TABLE, например, так:

create table mytestschema.testtable 
(id int,
somevalue varchar(50));

Так же и при любых запросах:

select id from mytestschema.testtable;

Вы можете думать о схеме как о владельце таблицы (владелец схемы технически является владельцем таблицы). Указание владельца повсюду в вашем коде является гарантией, что ничего неожиданного не произойдет. Поскольку, когда вы начинаете использовать схему, то можете давать объектам имена, которые существуют в других схемах. Давать уникальные имена — это хорошая практика, но иногда одно и то же имя в различных схемах является лучшим вариантом):

create schema secondschema: 

create table secondschema.testtable
(insertdate date,
someothervalue varchar(20));

Это совершенно допустимо. Если бы я написал то, что я считаю плохим кодом, например:

select * from testtable;

то, вероятно, получил бы следующую ошибку:

ERROR: relation «testtable» does not exist
(отношение «testtable» не существует)
LINE 2: select * from testtable;

Сначала кажется, что ошибка возникает потому, что PostgreSQL не может решить, из какой из двух таблиц брать данные. Скорее, потому, что логины имеют схему по умолчанию. Когда я выполняю запрос, подобный последнему, без указания схемы, где находится таблица, PostgreSQL смотрит путь по умолчанию. Если таблицы там нет, значит ее не существует. Это справедливо, хотя у меня есть две таблицы с этим именем. PostgreSQL не проверяет другие схемы «на всякий случай».

Ниже в этой статье я расскажу, как управлять схемами по умолчанию.

Если схема пустая, вы можете ее удалить:

drop table if exists secondschema.testtable; 
drop schema if exists secondschema;

Если я сначала не удалю таблицу, возникнет ошибка:

SQL Error [2BP01]: ERROR: cannot drop schema mytestschema because other objects depend on it
(нельзя удалить схему mytestschema, поскольку от нее зависят другие объекты)
Detail: table mytestschema.testtable depends on schema mytestschema
(таблица mytestschema.testtable зависит от схемы mytestschema)
Hint: Use DROP . CASCADE to drop the dependent objects too.
(используйте DROP . CASCADE для удаления также и зависимых объектов)

В сообщении об ошибке дается совет, как исправить ситуацию. Я мог бы переписать запрос следующим образом:

drop schema if exists mytestschema cascade;

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

В каждой базе данных создается схема по умолчанию с именем public. Однако это только по умолчанию и, как в большинстве настроек по умолчанию, ее можно изменить. Фактически вы даже можете удалить схему public, если захотите. Я начал этот раздел с объяснения, как создать свою собственную схему, которой вы непосредственно управляете, в противоположность принимаемой по умолчанию.

Управление путями поиска по умолчанию

Помимо помощи в организации объектов вашей базы данных, схема помогает контролировать доступ к этим объектам. Я еще не углублялся в тему безопасности в этой серии и, вероятно, до нее еще далеко. Однако я немного расскажу о том, как схема помогает управлять безопасностью базы данных. (Мой коллега Ryan Booz недавно опубликовал статью на эту тему ).

В этом разделе я хочу более детально обсудить некоторые способы управления схемой по умолчанию.

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

На самом деле существует определенный список поиска для схемы, который вы можете увидеть, используя такой запрос:

show search_path;

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

Каждый пользователь имеет собственную схему, как в SQL Server. Это и есть схема $user, которую вы видите выше. Однако, если вы не указали схему, по умолчанию будет принята первая в списке поиска, public в данном случае. Мы можем добавить схему в список поиска для текущего подключения:

SET search_path TO radio,public;

Это не только добавит схему radio в search_path, но и изменит порядок в пути поиска, поэтому схема radio ищется до схемы public. Если вы выполните отключение, а потом подключитесь вновь, вы должны будете переустановить путь с помощью команды SET.

Если вы хотите сделать изменения пути принимаемыми по умолчанию, то можете использовать ALTER ROLE, чтобы установить для любой роли специфический путь поиска. Например:

ALTER ROLE scaryDba SET search_path = 'radio,public,$user';

Если вы хотите установить значение по умолчанию для сервера/кластера/базы данных, то можете изменить search_path в файле postgressql.cnf или использовать команду:

ALTER ROLE ALL SET search_path = '$user';

Это не будет иметь приоритета над индивидуальными установками путей, но сделает для каждого логина, который не имеет приоритетного пути поиска, необходимость указывать имя схемы при ссылках на любой объект. (Что, как уже отмечалось, является лучшей практикой.)

Владение и основные привилегии

Когда вы создаете схему, то можете определить владельца схемы, отличного от логина, который выполняет эту команду:

CREATE SCHEMA secureschema AUTHORIZATION radio_admin;

Схема, которую я еще не создал ранее, secureschema, будет создана с владельцем, являющимся ролью логина radio_admin (тоже еще не определенной, поскольку я еще не разбирался с безопасностью). Это будет гарантировать, что только логин radio_admin и, конечно, любая учетная запись, определенная как суперпользователь, смогут работать в этой схеме.

Вы можете также управлять поведением по схеме. Например, поскольку я установил независимую схему в этой базе данных и намереваюсь использовать ее в этой манере, я могу запретить доступ для всех логинов на создание объектов в схеме public (это необходимо только в PostgreSQL 14 и ранее, в 15 разрешение на создание не предоставляется по умолчанию):

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Здесь используется слово “public” в двух разных значениях. В первом, ‘public’, мы ссылаемся на схему с этим именем. Во втором, ‘PUBLIC’, мы говорим о роли, которая содержит всех пользователей в базе данных. Этот механизм призван гарантировать, что ничего случайно не будет помещено в схему public. Я бы сказал, что полезно следовать этой практике, если вы собираетесь использовать другие схемы, особенно, если вы используете их для обеспечения безопасности вашей базы данных.

Вы можете предоставить различные привилегии между схемой и пользователями так, чтобы данный пользователь мог читать данные из таблиц в схеме, но не мог модифицировать данные в таблицах (доступ только на чтение). Тем самым вы можете объединить данные разных типов в одной базе данных, но изолировать их при необходимости друг от друга. Это главная причина использования схем в базе данных.

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

Заключение

Схемы представляют собой контейнеры, которые позволяют сегментировать объекты и обеспечивать безопасность на более низком уровне, чем уровень базы данных. Использование схем, отличных от public, имеет хорошие преимущества. В PostgreSQL имеется несколько методов установки схемы по умолчанию, если ваши пользователи не любят использовать двойные имена.

Если вы знакомы со схемами в SQL Server, базовая функциональность схемы примерно та же, что и там. Однако есть дополнительная функциональность, подобно возможности управлять изменением списка поиска, которой обладает PostgreSQL.

Ссылки по теме

  1. Безопасность SQL Server — модель безопасности с использованием определяемых пользователем ролей
  2. Типы данных в PostgreSQL: изучаем PostgreSQL с Grant Fritchey

Что такое schema в БД?

Что такое schema в postgreSQL? Её надо создавать сразу после создания БД? Это логическое устройство таблиц, наполнения, прав и т.д. И тогда может быть много схем и они могут использовать общие таблицы?

Отслеживать
задан 14 окт 2020 в 13:42
585 3 3 серебряных знака 13 13 бронзовых знаков
@Мелкий писал ответ на этот вопрос тут qna.habr.com/answer?answer_id=1423551#answers_list_answer
14 окт 2020 в 14:49
и тут теперь будет
14 окт 2020 в 14:54
@Мелкий может ли в одной схеме быть таблица из другой?
14 окт 2020 в 18:38

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

14 окт 2020 в 20:10

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Схемы — это дополнительный уровень структурирования объектов базы. Похоже на директории в файловой системе или пространства имён ( namespace ) в программировании. Но не могут быть вложенными.

Пользуясь аналогией с файловой системой и вебом: есть файлы стилей CSS, какие-то JS. Ничто не мешает их все размещать в корневой директории веб-сервера. Но обычно их размещают всё-таки в поддиректориях для собственного удобства.

После создания новой базы у вас будет предопределённая схема public с правами для создания новых объектов для всех пользователей. Что делать дальше — решение разработчика схемы этой базы, проигнорировать схемы и размещать всё в public , структурировать как-либо по схемам, можно удалить public схему даже.

  • users
  • user_settings
  • user_favorites
  • blog_posts
  • blog_comments
  • users
  • users.settings
  • users.favorites
  • blog.posts
  • blog.comments

Самой базе без разницы. Схемы — это логический уровень, как названия таблиц.

Большинство проектов схемы не используют.

Права: у схем есть права create — кто может создавать новые объекты, и usage — кто может обращаться к объектам в той схеме. Поэтому может быть удобно для разработчиков сделать отдельную схему user_tmp и исключить её из бекапов, а в остальные схемы не давать прав create — тем самым форсируя, что таблицы приложения проходят через обычную принятую у вас процедуру миграций.

Для полноты картины: схемы public может и не быть, если она была удалена в базе, которая указанна в template опции create database

В Postgresql схема: зачем нужна и как я её могу использовать в своих проектах?

Здравствуйте!
Ещё не работал с Postgresql, и не знаю, как он работает, в чем он лучше, а в чем хуже других СУБД.
Недавно начал изучить Postgresql (надо было реализовать один проект и знакомые предложили Postgresql. а до этого работал с MySQL, Oracle)
В проекте такая структура, Интернет магазин(Покупатель, продавец, товары, склады, заказы и т.д) Структура интернет магазина чуть сложнее. и Есть одна задача:
— Интернет магазина можем продавать в других городах. А БД нужно реализовать так чтобы мы могли дать доступ клиенту, и он работал только со своими городом, и при этом все данные будет в одном БД но не должно смешиваться. с остальными городами.
Я прочитал о схема в Postgresql, пока не очень понял в чем фишка схем в Postgresql но я думаю. можно использовать схема Postgresql в моих проектах? и как лучше реализовать?

  • Вопрос задан более трёх лет назад
  • 8304 просмотра

5 комментариев

Средний 5 комментариев

sim3x

Уточните зачем вам схемы впринципе нужны для мульти-магазинов на одной БД?

Если вам требуется подкорректировать проект БД, то нужно полнее описать задачу

dilikpulatov

Дилик Пулатов @dilikpulatov Автор вопроса

sim3x, я думал, может стоит использовать схему для разделение данные клиентов которые покупали наш проект? Мне просто нужно как-то проще разделить данные клиента но при этом они все должны быт в одном БД. Думаю вы поняли о чем я

sim3x

Если клиент покупает магазин на вашем хостинге, то можно придумать как присобачить схему
Но удобнее использовать миграции

Если на хосте клиента — то вообще никак

Используйте механизм миграций в вашем бекенд фреймворке

Думаю вы поняли о чем я

dilikpulatov

Дилик Пулатов @dilikpulatov Автор вопроса

sim3x, Грубо говоря клиент покупает только доступ. Все данные будет только у меня на сервере.
О миграции пока не знаю, но я стараюсь чтобы все данные было в одном БД, потому что при добавление нового функции или исправление багов. ну вообщем при изменение мне не придется гулять в каждому БД клиента

sim3x

Физическое разнесение клиентов по своим БД имеет свои плюсы

у вообщем при изменение мне не придется гулять в каждому БД клиента

именно для автоматизации такого рода вещей и изобрели миграции

грубо говоря клиент покупает только доступ. Все данные будет только у меня на сервере.

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

Решения вопроса 0
Ответы на вопрос 1
PostgreSQL DBA

В оракле схем разве нет.

schema — дополнительный уровень структуризации объектов. Как namespace в программировании. И, к слову, входит в стандарт SQL.

Вы можете сделать таблицы:
user_subscriptions
user_orders
user_favorites
Вы можете сделать
user.subscriptions
user.orders
user.favorites
И в этом нет никакой разницы для СУБД. Но может быть удобно разработчику оперировать не с сотней таблиц одним списком, десятки из которых с одинаковыми префиксами (т.к. относятся к своим сущностям), а отдельные схемы по сущностях.

Пилить же одну таблицу на несколько смысла при этом не так много, зато добавляется хлопот.
Если вы хотите давать прямой доступ пользователю к базе — то зачем? Не надо так делать в разделяемой среде. Любую СУБД можно положить каким-нибудь интересным запросом. А в то что люди временами будут писать интересные и сильно творческие запросы — по опыту DBA вам гарантирую. Иногда такого наворотят. 0,5тб временный файлов одним запросом, например. Или сожрать 30гб RAM и увести базу в аварийный рестарт от OOM.

Sysadminium

В этой статье поговорим про схемы в базах данных PostgreSQL и шаблоны. Для понимания, иерархия такая: СУБД > Базы данных > Схемы > Таблицы (и другие объекты).

Оглавление скрыть

Базы данных и шаблоны

Когда мы создаём новые кластер командой initdb у нас создается 3 одинаковые базы данных:

postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows)

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

Две дополнительные базы template0 и template1 — это шаблоны. Новая база всегда создается путём копирования из другой шаблонной базы. По умолчанию для шаблона используется база template1. Поэтому, если у вас есть расширения, которыми вы пользуетесь, можете их заранее создать в template1.

Основная задача базы template0 заключается в том, что бы она никогда не менялась. Она используется, например при загрузке базы из дампа. Вначале вы создаёте базу из template0, а затем туда заливаете сохранённый дамп. Также база template0 позволяет создавать базы с использованием категорий локалей не по умолчанию (LC_COLLATE, LC_CTYPE).

Создание базы данных из шаблона

Схемы

Схема — это пространство имён для объектов внутри базы данных.

Суть работы схемы можно представить так: мы все складываем не все в одну большую кучу, а по небольшим отдельным кучкам. Например, как в файловой системе, всё кладем не в один каталог, а раскладываем по подкаталогам.

Вот пример работы со схемами! В одну схему поместим объекты для модуля «логистика», а в другую для модуля «финансы» и так далее.

Схемы PostgreSQL

В базе данных может быть несколько схем. По умолчанию существует две глобальные схемы. Глобальные они потому-что не принадлежат какой-то определённой базе данных:

  • pg_catalog — служебная схема (её ещё называют системный каталог), присутствует во всех базах данных, например там находится представление pg_tables;
  • public — общая схема, присутствует во всех базах данных и по умолчанию все объекты создаются в ней.

Также вы можете создать свои дополнительные схемы.

Путь поиска

Так называемое «Квалифицированное имя» состоит из явно указанной схемы и имени объекта (как абсолютный путь в файловой системе). Например: .

Если мы не указываем схему, то нужно понять, в какой схеме искать или создавать объект. Определяют схему с помощью пути поиска, который задается параметром search_path.

В параметре search_path можно через запятую перечислить схемы, в которых нужно искать объект, если мы не указываем схему явно. search_path это что-то вроде переменной окружения PATH в Linux, для поиска команд.

Из search_path исключаются:

  • несуществующие схемы;
  • схемы к которым нет доступа.

А некоторые схемы всегда добавляются в search_path, даже если мы их туда не запишем. Например pg_catalog.

Реальное значение search_path показывает функция current_schemas().

postgres@postgres=# SELECT current_schemas(true); current_schemas --------------------- (1 row) Time: 1,945 ms

При создании нового объекта, он будет помещаться в первую указанную в search_path схему. Если посмотреть пример выше, то так как у нас нет права писать в схему pg_catalog, объекты будут создаваться в public.

Специальные схемы, временные объекты

К специальным схемам относят:

  • public — по умолчанию входит в путь поиска, если ничего не менять, все объекты будут в этой схеме.
  • Схема, одноимённая с пользователем — по умолчанию входит в search_path, но не существует. Если сделать, например схему postgres, то пользователь postgres будет по умолчанию работать с этой схемой.
  • pg_catalog — схема для объектов системного каталога. Если pg_catalog не прописан, то это схема будет там подразумеваться первой.

Временные таблицы — существуют на время сеанса или транзакции. Они не журналируются и не попадают в общую память. Чтобы реализовать временную таблицу в postgres применяет временные схемы.

Схема pg_temp_N — автоматически создается для временных таблиц. Такая схема тоже по умолчанию находится в search_path. По окончанию все объекты временной схемы удаляются, а сама схема остается. Оставшаяся временная схема может использоваться для новых временных таблиц, новой транзакции или сеанса.

Практика

Список баз

Как мы уже видели с помощью команды \l , у нас действительно 3 базы. Сейчас мы подключены к базе postgres. Тут мы можем обратиться к представлению pg_database и посмотреть на список баз из этого представления:

postgres@postgres=# 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) Time: 0,875 ms
  • datname — имя базы данных;
  • datistemplate — является ли база данных шаблоном;
  • datallowconn — разрешены ли соединения с базой данных;
  • datconnlimit — максимальное количество соединений (-1 = без ограничений).

Настройка шаблона template1

Проверим, доступна ли нам функция шифрования в этой базе, если не доступна, то создадим необходимое расширение и повторим проверку:

postgres@postgres=# \c template1 You are now connected to database "template1" as user "postgres". postgres@template1=# 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. Time: 2,081 ms postgres@template1=# CREATE EXTENSION pgcrypto; CREATE EXTENSION Time: 24,470 ms postgres@template1=# SELECT digest('Hello, world!', 'md5'); digest ------------------------------------ \x6cd3556deb0da54bca060b4c39479839 (1 row) Time: 0,419 ms

В случае если у вас не было скомпилировано это расширение, то в первом уроке мы разбирали как компилировать postgres и его расширения. Примерно это делается так:

$ cd postgresql-13.3/contrib/pgcrypto/ $ make $ sudo make install

Теперь создадим новую базу данных и так как она была создана из шаблона template1, то и расширение pgcrypto здесь уже установлено:

postgres@postgres=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# CREATE DATABASE db; CREATE DATABASE Time: 103,788 ms postgres@postgres=# \c db You are now connected to database "db" as user "postgres". postgres@db=# SELECT digest('Hello, world!', 'md5'); digest ------------------------------------ \x6cd3556deb0da54bca060b4c39479839 (1 row) Time: 0,868 ms

Выше мы вначале отключились от базы template1, так как использовать шаблон можно только, если к нему никто не подключен!

Редактирование базы

Теперь переименуем созданную базу данных (ALTER DATABASE … RENAME TO … ), предварительно отключившись от неё:

postgres@db=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# ALTER DATABASE db RENAME TO appdb; ALTER DATABASE Time: 1,164 ms postgres@postgres=# 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) Time: 0,434 ms

С помощью ALTER DATABASE можно менять и другие параметры, например число доступных подключений:

postgres@postgres=# ALTER DATABASE appdb CONNECTION LIMIT 10; ALTER DATABASE Time: 0,456 ms postgres@postgres=# 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) Time: 0,202 ms

Смотрим размер базы данных

Размер базы данных можно считать с помощью функции pg_database_size(). Для перевода из байтов в более удобочитаемые единицы, можно использовать функцию pg_size_pretty():

postgres@postgres=# SELECT pg_database_size('appdb'); pg_database_size ------------------ 7787055 (1 row) Time: 1,750 ms postgres@postgres=# SELECT pg_size_pretty(pg_database_size('appdb')); pg_size_pretty ---------------- 7605 kB (1 row) Time: 1,247 ms

Вот мы и узнали размер пустой базы!

Работа со схемами

Список схем можно узнать с помощью команды \dn:

postgres@postgres=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)

Это не все схемы, здесь исключены служебные схемы!

Создадим новую схему, предварительно подключившись к нашей базе:

postgres@postgres=# \c appdb You are now connected to database "appdb" as user "postgres". postgres@appdb=# CREATE SCHEMA app; CREATE SCHEMA Time: 0,927 ms postgres@appdb=# \dn List of schemas Name | Owner --------+---------- app | postgres public | postgres (2 rows)

На путь поиска схем можно посмотреть с помощью search_path:

postgres@appdb=# SHOW search_path; search_path ----------------- "$user", public (1 row) Time: 0,248 ms

Это означает, что при создании таблицы, она попытается попасть в схему «$user» (postgres), но такой схемы нет. А затем попадет в схему public! И наоборот, при обращении к таблице она будет искаться в начале в «$user», а затем в public!

Дополнительно можем посмотреть текущие схемы, в этой базе данных с помощью функции current_schemas():

postgres@appdb=# SELECT current_schemas(true); current_schemas --------------------- (1 row) Time: 0,343 ms

Здесь мы видим служебную схему pg_catalog, но к ней нет доступа. Поэтому судя по пути поиска и по текущим схемам, можем сказать что по умолчанию таблицы будут создаваться в схеме public.

Теперь создадим таблицу «t«, в ней создадим строку и с помощью команды \dt посмотрим в какой схеме оказалась эта таблица:

postgres@appdb=# CREATE TABLE t(s text); CREATE TABLE Time: 4,398 ms postgres@appdb=# INSERT INTO t VALUES ('Я - таблица t'); INSERT 0 1 Time: 1,172 ms postgres@appdb=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres (1 row)

Таблицы можно перемещать между схемами с помощью ALTER TABLE . SET SCHEMA . . Если схемы нет в пути поиска, то к таблицам в этой схеме нужно обращаться по полному пути:

postgres@appdb=# ALTER TABLE t SET SCHEMA app; ALTER TABLE Time: 0,916 ms postgres@appdb=# SELECT * FROM app.t; s --------------- Я - таблица t (1 row) Time: 0,372 ms postgres@appdb=# SELECT * FROM t; ERROR: relation "t" does not exist LINE 1: SELECT * FROM t; ^ Time: 0,226 ms

Выше мы видим, что не указав полный путь мы получили ошибку!

Установить путь поиска можно так:

postgres@appdb=# SET search_path = public, app; SET Time: 0,203 ms postgres@appdb=# SELECT * FROM t; s --------------- Я - таблица t (1 row) Time: 0,205 ms

Но это установит путь только для текущего сеанса!

Установить этот параметр для базы, а не для сеанса можно с помощью ALTER DATABASE . SET search_path = . :

appdb=# ALTER DATABASE appdb SET search_path = public, app; ALTER DATABASE

Выше команда означает, что при подключении к базе appdb будет выполняться команда SET search_path = public, app.

Теперь создадим временную таблицу с таким-же именем «t» и посмотрим что из этого выйдет:

postgres@appdb=# CREATE TEMP TABLE t(s text); CREATE TABLE Time: 1,908 ms postgres@appdb=# \dt List of relations Schema | Name | Type | Owner -----------+------+-------+---------- pg_temp_3 | t | table | postgres (1 row)

Мы видим только временную таблицу, а первую созданную таблицу уже не видим в списке баз!

Посмотрим на текущий путь поиска с помощью функции current_schemas (). А затем вставим строку во временную таблицу и прочитаем её. И далее прочитаем строки из обычной таблицы используя полный путь:

postgres@appdb=# SELECT current_schemas(true); current_schemas ----------------------------------- (1 row) Time: 0,202 ms postgres@appdb=# INSERT INTO t VALUES ('Я - временная таблица'); INSERT 0 1 Time: 0,608 ms postgres@appdb=# SELECT * FROM app.t; s --------------- Я - таблица t (1 row) Time: 0,191 ms postgres@appdb=# SELECT * FROM pg_temp.t; s ----------------------- Я - временная таблица (1 row) Time: 0,203 ms

При выходе из сеанса все объекты во временной схеме уничтожаются:

postgres@appdb=# \c appdb You are now connected to database "appdb" as user "postgres". postgres@appdb=# SELECT current_schemas(true); current_schemas ------------------------- (1 row) Time: 0,373 ms postgres@appdb=# SELECT * FROM t; s --------------- Я - таблица t (1 row) Time: 0,359 ms

Удаление схемы и базы

Схему нельзя удалить, если в ней есть какие-нибудь объекты. А для удаления схемы вместе с объектами нужно использовать опцию CASCADE:

postgres@appdb=# 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. Time: 0,612 ms postgres@appdb=# DROP SCHEMA app CASCADE; NOTICE: drop cascades to table t DROP SCHEMA Time: 1,175 ms

Базу данных можно удалить, если к ней нет активных подключений:

postgres@appdb=# \c postgres You are now connected to database "postgres" as user "postgres". postgres@postgres=# DROP DATABASE appdb; DROP DATABASE Time: 14,367 ms

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *