PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 1 Buy the book here! >>> only $49.95

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:

schema.table

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

database.schema.table

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.