PostgreSQL CREATE SCHEMA
Summary: in this tutorial, you will learn how to use the PostgreSQL CREATE SCHEMA statement to create a new schema in a database.
PostgreSQL CREATE SCHEMA statement overview
The CREATE SCHEMA statement allows you to create a new schema in the current database.
The following illustrates the syntax of the CREATE SCHEMA statement:
- First, specify the name of the schema after the CREATE SCHEMA keywords. The schema name must be unique within the current database.
- Second, optionally use IF NOT EXISTS to conditionally create the new schema only if it does not exist. Attempting to create a new schema that already exists without using the IF NOT EXISTS option will result in an error.
Note that to execute the CREATE SCHEMA statement, you must have the CREATE privilege in the current database.
You can also create a schema for a user:
In this case, the schema will have the same name as the username ;
PostgreSQL also allows you to create a schema and a list of objects such as tables and views using a single statement as follows:
Notice that each subcommand does not end with a semicolon (;).
PostgreSQL CREATE SCHEMA examples
Let’s take some examples of using the CREATE SCHEMA statement to get a better understanding.
1) Using CREATE SCHEMA to create a new schema example
The following statement uses the CREATE SCHEMA statement to create a new schema named marketing :
The following statement returns all schemas from the current database:
This picture shows the output:
2) Using CREATE SCHEMA to create a schema for a user example
First, create a new role with named john :
Second, create a schema for john :
Third, create a new schema called doe that will be owned by john :
3) Using CREATE SCHEMA to create a schema and its objects example
The following example uses the CREATE SCHEMA statement to create a new schema named scm . It also creates a table named deliveries and a view named delivery_due_list that belongs to the scm schema:
In this tutorial, you have learned how to use the PostgreSQL CREATE SCHEMA statement to create a new schema in a database.
Как создать схему в postgresql
Кластер баз данных PostgreSQL содержит один или несколько именованных экземпляров баз. На уровне кластера создаются роли и некоторые другие объекты. При этом в рамках одного подключения к серверу можно обращаться к данным только одной базы — той, что была выбрана при установлении соединения.
Примечание
Пользователи кластера не обязательно будут иметь доступ ко всем базам данных этого кластера. Тот факт, что роли создаются на уровне кластера, означает только то, что в кластере не может быть двух ролей joe в разных базах данных, хотя система позволяет ограничить доступ joe только некоторыми базами данных.
База данных содержит одну или несколько именованных схем, которые в свою очередь содержат таблицы. Схемы также содержат именованные объекты других видов, включая типы данных, функции и операторы. Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1 , и myschema могут содержать таблицы с именем mytable . В отличие от баз данных, схемы не ограничивают доступ к данным: пользователи могут обращаться к объектам в любой схеме текущей базы данных, если им назначены соответствующие права.
Есть несколько возможных объяснений, для чего стоит применять схемы:
Чтобы одну базу данных могли использовать несколько пользователей, независимо друг от друга.
Чтобы объединить объекты базы данных в логические группы для облегчения управления ими.
Схемы в некотором смысле подобны каталогам в операционной системе, но они не могут быть вложенными.
5.9.1. Создание схемы
Для создания схемы используется команда CREATE SCHEMA . При этом вы определяете имя схемы по своему выбору, например так:
Чтобы создать объекты в схеме или обратиться к ним, указывайте полное имя, состоящее из имён схемы и объекта, разделённых точкой:
Этот синтаксис работает везде, где ожидается имя таблицы, включая команды модификации таблицы и команды обработки данных, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но всё это распространяется и на другие типы именованных объектов, например, типы и функции.)
Есть ещё более общий синтаксис
но в настоящее время он поддерживается только для формального соответствия стандарту SQL. Если вы указываете базу данных, это может быть только база данных, к которой вы подключены.
Таким образом, создать таблицу в новой схеме можно так:
Чтобы удалить пустую схему (не содержащую объектов), выполните:
Удалить схему со всеми содержащимися в ней объектами можно так:
Стоящий за этим общий механизм описан в Разделе 5.14.
Часто бывает нужно создать схему, владельцем которой будет другой пользователь (это один из способов ограничения пользователей пространствами имён). Сделать это можно так:
Вы даже можете опустить имя схемы, в этом случае именем схемы станет имя пользователя. Как это можно применять, описано в Подразделе 5.9.6.
Схемы с именами, начинающимися с pg_ , являются системными; пользователям не разрешено использовать такие имена.
5.9.2. Схема public
До этого мы создавали таблицы, не указывая никакие имена схем. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему « public » . Она содержится во всех создаваемых базах данных. Таким образом, команда:
5.9.3. Путь поиска схемы
Везде писать полные имена утомительно, и часто всё равно лучше не привязывать приложения к конкретной схеме. Поэтому к таблицам обычно обращаются по неполному имени, состоящему просто из имени таблицы. Система определяет, какая именно таблица подразумевается, используя путь поиска, который представляет собой список просматриваемых схем. Подразумеваемой таблицей считается первая подходящая таблица, найденная в схемах пути. Если подходящая таблица не найдена, возникает ошибка, даже если таблица с таким именем есть в других схемах базы данных.
Возможность создавать одноимённые объекты в разных схемах усложняет написание запросов, которые должны всегда обращаться к конкретным объектам. Это также потенциально позволяет пользователям влиять на поведение запросов других пользователей, злонамеренно или случайно. Ввиду преобладания неполных имён в запросах и их использования внутри PostgreSQL , добавить схему в search_path — по сути значит доверять всем пользователям, имеющим право CREATE в этой схеме. Когда вы выполняете обычный запрос, злонамеренный пользователь может создать объекты в схеме, включённой в ваш путь поиска, и таким образом перехватывать управление и выполнять произвольные функции SQL как если бы их выполняли вы.
Первая схема в пути поиска называется текущей. Эта схема будет использоваться не только при поиске, но и при создании объектов — она будет включать таблицы, созданные командой CREATE TABLE без указания схемы.
Чтобы узнать текущий тип поиска, выполните следующую команду:
В конфигурации по умолчанию она возвращает:
Первый элемент ссылается на схему с именем текущего пользователя. Если такой схемы не существует, ссылка на неё игнорируется. Второй элемент ссылается на схему public, которую мы уже видели.
Первая существующая схема в пути поиска также считается схемой по умолчанию для новых объектов. Именно поэтому по умолчанию объекты создаются в схеме public. При указании неполной ссылки на объект в любом контексте (при модификации таблиц, изменении данных или в запросах) система просматривает путь поиска, пока не найдёт соответствующий объект. Таким образом, в конфигурации по умолчанию неполные имена могут относиться только к объектам в схеме public.
Чтобы добавить в путь нашу новую схему, мы выполняем:
(Мы опускаем компонент $user , так как здесь в нём нет необходимости.) Теперь мы можем обращаться к таблице без указания схемы:
И так как myschema — первый элемент в пути, новые объекты будут по умолчанию создаваться в этой схеме.
Мы можем также написать:
Тогда мы больше не сможем обращаться к схеме public, не написав полное имя объекта. Единственное, что отличает схему public от других, это то, что она существует по умолчанию, хотя её так же можно удалить.
В Разделе 9.26 вы узнаете, как ещё можно манипулировать путём поиска схем.
Как и для имён таблиц, путь поиска аналогично работает для имён типов данных, имён функций и имён операторов. Имена типов данных и функций можно записать в полном виде так же, как и имена таблиц. Если же вам нужно использовать в выражении полное имя оператора, для этого есть специальный способ — вы должны написать:
Такая запись необходима для избежания синтаксической неоднозначности. Пример такого выражения:
На практике пользователи часто полагаются на путь поиска, чтобы не приходилось писать такие замысловатые конструкции.
5.9.4. Схемы и права
По умолчанию пользователь не может обращаться к объектам в чужих схемах. Чтобы изменить это, владелец схемы должен дать пользователю право USAGE для данной схемы. По умолчанию все пользователи имеют это право для схемы public . Чтобы пользователи могли использовать объекты схемы, может понадобиться назначить дополнительные права на уровне объектов.
Пользователю также можно разрешить создавать объекты в схеме, не принадлежащей ему. Для этого ему нужно дать право CREATE в требуемой схеме. В базах данных, обновлённых с PostgreSQL 14 или более ранней версии, все имеют это право в схеме public . Некоторые шаблоны использования требуют запретить это:
(Первое слово « public » обозначает схему, а второе означает « каждый пользователь » . В первом случае это идентификатор, а во втором — ключевое слово, поэтому они написаны в разном регистре; вспомните указания из Подраздела 4.1.1.)
5.9.5. Схема системного каталога
В дополнение к схеме public и схемам, создаваемым пользователями, любая база данных содержит схему pg_catalog , в которой находятся системные таблицы и все встроенные типы данных, функции и операторы. pg_catalog фактически всегда является частью пути поиска. Если даже эта схема не добавлена в путь явно, она неявно просматривается до всех схем, указанных в пути. Так обеспечивается доступность встроенных имён при любых условиях. Однако вы можете явным образом поместить pg_catalog в конец пути поиска, если вам нужно, чтобы пользовательские имена переопределяли встроенные.
Так как имена системных таблиц начинаются с pg_ , такие имена лучше не использовать во избежание конфликта имён, возможного при появлении в будущем системной таблицы с тем же именем, что и ваша. (С путём поиска по умолчанию неполная ссылка будет воспринята как обращение к системной таблице.) Системные таблицы будут и дальше содержать в имени приставку pg_ , так что они не будут конфликтовать с неполными именами пользовательских таблиц, если пользователи со своей стороны не будут использовать приставку pg_ .
5.9.6. Шаблоны использования
Схемам можно найти множество применений. Для защиты от влияния недоверенных пользователей на поведение запросов других пользователей предлагается шаблон безопасного использования схем, но если этот шаблон не применяется в базе данных, пользователи, желающие безопасно выполнять в ней запросы, должны будут принимать защитные меры в начале каждого сеанса. В частности, они должны начинать каждый сеанс с присваивания пустого значения переменной search_path или каким-либо другим образом удалять из search_path схемы, доступные для записи обычным пользователям. С конфигурацией по умолчанию легко реализуются следующие шаблоны использования:
Ограничить обычных пользователей личными схемами. Для реализации этого подхода сначала убедитесь, что ни у одной схемы нет права CREATE . Затем для каждого пользователя, который будет создавать не временные объекты, создайте схему с его именем, например CREATE SCHEMA alice AUTHORIZATION alice . (Как вы знаете, путь поиска по умолчанию начинается с имени $user , вместо которого подставляется имя пользователя. Таким образом, если у всех пользователей будет отдельная схема, они по умолчанию будут обращаться к собственным схемам.) Этот шаблон позволяет безопасно использовать схемы, только если никакой недоверенный пользователь не является владельцем базы данных и не имеет права CREATEROLE . В противном случае безопасное использование схем невозможно.
В PostgreSQL 15 и выше этот подход использования поддерживается конфигурацией по умолчанию. В предыдущих версиях или при использовании базы данных, обновлённой с предыдущей версии, вам необходимо удалить право CREATE из схемы public (выполнить REVOKE CREATE ON SCHEMA public FROM PUBLIC ). Затем проверьте, нет ли в схеме public объектов с такими же именами, как у объектов в схеме pg_catalog .
Удалить схему public из пути поиска по умолчанию, изменив postgresql.conf или выполнив команду ALTER ROLE ALL SET search_path = «$user» . Затем следует предоставить права на создание объектов в схеме public. Выбираться объекты в этой схеме будут только по полному имени. Тогда как обращаться к таблицам по полному имени вполне допустимо, обращения к функциям в общей схеме всё же будут небезопасными или ненадёжными. Поэтому если вы создаёте функции или расширения в схеме public, применяйте первый шаблон. Если же нет, этот шаблон, как и первый, безопасен при условии, что никакой недоверенный пользователь не является владельцем базы данных и не имеет права CREATEROLE .
При любом подходе, устанавливая совместно используемые приложения (таблицы, которые нужны всем, дополнительные функции сторонних разработчиков и т. д.), помещайте их в отдельные схемы. Не забудьте дать другим пользователям права для доступа к этим схемам. Тогда пользователи смогут обращаться к этим дополнительным объектам по полному имени или при желании добавят эти схемы в свои пути поиска.
5.9.7. Переносимость
Стандарт SQL не поддерживает обращение в одной схеме к разным объектам, принадлежащим разным пользователям. Более того, в ряде реализаций СУБД нельзя создавать схемы с именем, отличным от имени владельца. На практике, в СУБД, реализующих только базовую поддержку схем согласно стандарту, концепции пользователя и схемы очень близки. Таким образом, многие пользователи полагают, что полное имя на самом деле образуется как имя_пользователя . имя_таблицы . И именно так будет вести себя PostgreSQL , если вы создадите схемы для каждого пользователя.
В стандарте SQL нет и понятия схемы public . Для максимального соответствия стандарту использовать схему public не следует.
Конечно, есть СУБД, в которых вообще не реализованы схемы или пространства имён поддерживают (возможно, с ограничениями) обращения к другим базам данных. Если вам потребуется работать с этими системами, максимальной переносимости вы достигнете, вообще не используя схемы.
PostgreSQL – схема
Схема – это именованная коллекция таблиц. Схема также может содержать представления, индексы, последовательности, типы данных, операторы и функции. Схемы аналогичны каталогам на уровне операционной системы, за исключением того, что схемы не могут быть вложенными. Оператор PostgreSQL CREATE SCHEMA создает схему.
Синтаксис
Основной синтаксис CREATE SCHEMA следующий:
Где имя – это имя схемы.
Синтаксис для создания таблицы в схеме
Основной синтаксис для создания таблицы в схеме следующий:
пример
Давайте посмотрим пример для создания схемы. Подключитесь к базе данных testdb и создайте схему myschema следующим образом –
Сообщение «CREATE SCHEMA» означает, что схема создана успешно.
Теперь давайте создадим таблицу в приведенной выше схеме следующим образом:
Это создаст пустую таблицу. Вы можете проверить таблицу, созданную с помощью команды, приведенной ниже –
Это даст следующий результат –
Синтаксис для удаления схемы
Чтобы удалить схему, если она пуста (все объекты в ней удалены), используйте команду –
Чтобы удалить схему, включающую все содержащиеся в ней объекты, используйте команду –
Это позволяет многим пользователям использовать одну базу данных, не мешая друг другу.
Он организует объекты базы данных в логические группы, чтобы сделать их более управляемыми.
Сторонние приложения могут быть помещены в отдельные схемы, чтобы они не конфликтовали с именами других объектов.
Как создать схему в postgresql
CREATE SCHEMA enters a new schema into the current database. The schema name must be distinct from the name of any existing schema in the current database.
A schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names can duplicate those of other objects existing in other schemas. Named objects are accessed either by “ qualifying ” their names with the schema name as a prefix, or by setting a search path that includes the desired schema(s). A CREATE command specifying an unqualified object name creates the object in the current schema (the one at the front of the search path, which can be determined with the function current_schema ).
Optionally, CREATE SCHEMA can include subcommands to create objects within the new schema. The subcommands are treated essentially the same as separate commands issued after creating the schema, except that if the AUTHORIZATION clause is used, all the created objects will be owned by that user.
Parameters
The name of a schema to be created. If this is omitted, the user_name is used as the schema name. The name cannot begin with pg_ , as such names are reserved for system schemas.
The role name of the user who will own the new schema. If omitted, defaults to the user executing the command. To create a schema owned by another role, you must be a direct or indirect member of that role, or be a superuser.
An SQL statement defining an object to be created within the schema. Currently, only CREATE TABLE , CREATE VIEW , CREATE INDEX , CREATE SEQUENCE , CREATE TRIGGER and GRANT are accepted as clauses within CREATE SCHEMA . Other kinds of objects may be created in separate commands after the schema is created.
Do nothing (except issuing a notice) if a schema with the same name already exists. schema_element subcommands cannot be included when this option is used.
Notes
To create a schema, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)
Examples
Create a schema:
Create a schema for user joe ; the schema will also be named joe :
Create a schema named test that will be owned by user joe , unless there already is a schema named test . (It does not matter whether joe owns the pre-existing schema.)
Create a schema and create a table and view within it:
Notice that the individual subcommands do not end with semicolons.
The following is an equivalent way of accomplishing the same result:
Compatibility
The SQL standard allows a DEFAULT CHARACTER SET clause in CREATE SCHEMA , as well as more subcommand types than are presently accepted by PostgreSQL .
The SQL standard specifies that the subcommands in CREATE SCHEMA can appear in any order. The present PostgreSQL implementation does not handle all cases of forward references in subcommands; it might sometimes be necessary to reorder the subcommands in order to avoid forward references.
According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on their schema to someone else, or a superuser chooses to create objects in it.
The IF NOT EXISTS option is a PostgreSQL extension.
See Also
| Prev | Up | Next |
| CREATE RULE | Home | CREATE SEQUENCE |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.