|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
3.7.1 Creating a Schema
To create a schema, use the
CREATE SCHEMA command. Give the schema a name
of your choice. For example:
CREATE SCHEMA myschema;
To create or access objects in a schema, write a qualified name consisting of the schema name and table name separated by a dot:
This works anywhere a table name is expected, including the table modification commands and the data access commands discussed in the following chapters. (For brevity we will speak of tables only, but the same ideas apply to other kinds of named objects, such as types and functions.)
Actually, the even more general syntax
can be used too, but at present this is just for pro forma compliance with the SQL standard. If you write a database name, it must be the same as the database you are connected to.
So to create a table in the new schema, use
CREATE TABLE myschema.mytable ( ... );
To drop a schema if it's empty (all objects in it have been dropped), use
DROP SCHEMA myschema;
To drop a schema including all contained objects, use
DROP SCHEMA myschema CASCADE;
See section 3.11 Dependency Tracking for a description of the general mechanism behind this.
Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces). The syntax for that is:
CREATE SCHEMA schemaname AUTHORIZATION username;
You can even omit the schema name, in which case the schema name will be the same as the user name. See section 3.7.6 Usage Patterns for how this can be useful.
Schema names beginning with
pg_ are reserved for
system purposes and may not be created by users.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|