- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.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:


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:


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:


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 cannot be created by users.

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition