PostgreSQL's Schema Concept: An Introduction

Background
Examples
The 'public' schema
Practical schema usage
Permissions and security
Removing schemas
Schema funtions
Special schemas
Limitations
Documentation

Background

Often it is useful or necessary to be able to issue "cross-database" SQL statements such as:

  SELECT t1.qty, t2.qty
    FROM foo.widget t1,
         bar.widget t2

where foo and bar refer to seperate collections of database objects (tables, views, indexes, functions etc.). There are many reasons for wanting to do this: to divide up data for organisational and / or security reasons; to query data from different applications directly in the database backend; or to add custom tables to a commodity application without interfering with the application's database.

Until recently PostgreSQL had no inbuilt capability for this kind of operation [1]. The release of version 7.3 in November 2002 was a major step forward, introducing schemas, which enable database objects to be grouped together in distinct namespaces within the same database. While this does not provide true cross-database connectivity (e.g. between different databases in a PostgreSQL database cluster), schemas provide equivalent capability for many applications.

This article provides a brief introduction to schemas and their practical usage in the form of a quick reference complementing the more detailed main documentation at: http://www.postgresql.org/docs/current/static/ddl-schemas.html.

Examples

The following set of example statements - issued in a newly created database - demonstrate the basic functionality of schemas:

test=# CREATE SCHEMA foo;
CREATE SCHEMA

test=# CREATE TABLE foo.info (id INT, txt TEXT);
CREATE TABLE

test=# INSERT INTO foo.info VALUES(1, 'This is schema foo');
INSERT 23062 1

test=# CREATE SCHEMA bar;
CREATE SCHEMA

test=# CREATE TABLE bar.info (id INT, txt TEXT);
CREATE TABLE

test=# INSERT INTO bar.info VALUES(1, 'This is schema bar');
INSERT 23069 1

test=# SELECT foo.info.txt, bar.info.txt
test-#   FROM foo.info, bar.info
test-#  WHERE foo.info.id=bar.info.id;
        txt         |        txt
--------------------+--------------------
 This is schema foo | This is schema bar
(1 row)

test=# CREATE VIEW info_view AS
test-#  SELECT f.txt AS foo, b.txt AS bar
test-#    FROM foo.info f, bar.info b
test-#   WHERE f.id=b.id;
CREATE VIEW
test=# SELECT * FROM info_view;
        foo         |        bar
--------------------+--------------------
 This is schema foo | This is schema bar
(1 row)

The 'public' schema

In the example above, the view info_view was created without an explicit schema name. Which schema was it assigned to?

Assuming the example SQL statements were executed in a freshly initialized database with no additional schema settings, info_view was created in the public schema:

test=# \dv
         List of relations
 Schema |   Name    | Type | Owner
--------+-----------+------+-------
 public | info_view | view | test
(1 row)

The public schema is created by default; it exists for convenience and for backwards compatiblity enabling applications which are not schema-aware (i.e. designed for pre-7.3 PostgreSQL versions) to connect to a schema-enabled database.

Note that the public schema is not required for PostgreSQL and may be removed or renamed [2] if desired.

Practical schema usage

When the schema name is not provided for a particular database object, PostgreSQL refers to the "search path" which defines the order in which to search through schemas for an unqualified object name.

In the example above, info_view was created in the public schema because by default public is always contained in the search path. The current settings for the search path can be viewed using SHOW search_path:

test=# SHOW search_path;
 search_path
--------------
 $user,public

This is the default setting; $user is a place holder for the name of the current user, meaning the first schema to be searched will be one with the same name as the current user. As PostgreSQL does not automatically create a schema for each user (unlike databases such as Oracle), in the default setting public will be the schema to which all non-qualified object names refer.

A search path is maintained for each database connection; to change the search path use SET search_path TO ...:

test=# SET search_path TO foo;
SET

test=# \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 foo    | info | table | ian
(1 row)

To permanently alter the search path set on each connection, use

ALTER USER test SET search_path TO bar,foo;

This change will only take effect after reconnecting to the database.

To continue the above examples, setting the search path to bar, foo means now bar's table info will be selected by default:

test=# SET search_path TO bar, foo;
SET

test=# SELECT txt FROM info;
        txt
--------------------
 This is schema bar
(1 row)

test=# \d info
       Table "bar.info"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 txt    | text    |

and the previously created info_view is no longer 'visible':

test=# SELECT * FROM info_view;
ERROR:  Relation "info_view" does not exist
test=# SELECT * FROM public.info_view;
        foo         |        bar
--------------------+--------------------
 This is schema foo | This is schema bar
(1 row)

Permissions and security

Schemas can only be created by superusers, e.g. any user with permission to create other users. (Note that users with only permission to create databases may not create schemas).

To create a schema for another user use:

CREATE SCHEMA tarzan AUTHORIZATION tarzan;

or

CREATE SCHEMA AUTHORIZATION tarzan;

By default only the owner of a schema or superusers have access to objects contain therein.

The USAGE privilege determines whether a user can perform any operations on another user's schema:

GRANT USAGE ON SCHEMA tarzan TO jane;
REVOKE USAGE ON SCHEMA tarzan TO jane;

Once the USAGE privilege has been granted, priviliges on both existing and newly created schema objects must be granted explicitly.

GRANT SELECT ON tarzan.banana_inventory TO jane;
REVOKE SELECT ON tarzan.banana_inventory FROM jane;

If USAGE is revoked, no objects, including those the user has privileges on, can be accessed. If USAGE is granted again, any previously set object priviliges are automatically reactivated.

The CREATE privilige on a schema enables a user to create objects in another user's schema:

GRANT CREATE ON SCHEMA tarzan TO jane;
REVOKE CREATE ON SCHEMA tarzan TO jane;

Use the reserved username PUBLIC when granting or revoking privileges from all users:

GRANT ALL ON SCHEMA tarzan TO PUBLIC;

(Here ALL refers to the USAGE and CREATE privileges.)

Note that even if no access has been granted, the structure of any objects in a particular schema can be viewed by any user by querying the system tables in the special pg_catalog schema (see below).

As always the public schema is an exception. All users are automatically granted USAGE and CREATE priviliges on this schema.

Removing schemas

This is simple:

DROP SCHEMA tarzan;

or if tarzan is already populated (which is usually the case):

DROP SCHEMA tarzan CASCADE;

to remove all dependent objects.

Schema funtions

There are two inbuilt functions for extracting schema information in the current session:

• current_schema()
  Returns the name of the current schema (first schema in the search path), as set by SET search_path TO .... Note that it will resolve to the first existing schema in the search path, not necessarily the first. If no schema is found, NULL is returned.
• current_schemas(boolean)
  Returns all schemas in the search path as an array; if called with TRUE, implicit schemas (special schemas such as pg_catalog which are added to the search path automatically if not explicitly specified) are also returned.

Special schemas

Each PostgreSQL database contains a number of special schemas required by the backend and which may not be removed or altered. All begin with pg_. Note that schema names beginning with pg_ are not allowed.

The special schemas are:

• pg_catalog: Contains the system tables, functions and views holding meta-information about the database;
• pg_temp_x: Contains temporary tables which are only visible to a particular database connection
• pg_toast: Contains butter and assorted jams.

From PostgreSQL 7.4 there will also be an information_schema consisting of predefined views containing descriptive information about the current database. This information is presented in a format defined in the SQL standard and provides consistant, standardized information about the database and to some extent the database's capabilities. The information_schema is for compatibility purposes and will probable not be relevant for most applications.

Limitations

It is not currently possible to "transfer" objects between schemas. Possible workarounds:

• use

  CREATE TABLE new_schema.mytable AS SELECT * FROM old_schema.mytable

  to transfer the data and to recreate all associated constraints, indexes, sequences etc. manually.
  
  
• create the table in the new schema with the existing definition and use

  INSERT INTO new_schema.mytable SELECT * FROM old_schema.mytable;

  to populate the table.

Renaming of schemas will be introduced in PostgreSQL 7.4 using the ALTER SCHEMA .. RENAME TO ... command.

Documentation

For further reference see:

• http://www.postgresql.org/docs/current/static/ddl-schemas.html
• http://www.postgresql.org/docs/current/static/sql-createschema.html
• http://www.postgresql.org/docs/current/static/sql-dropschema.html
• http://www.postgresql.org/docs/current/static/functions-misc.html

---

[1] For true cross-database connections see contrib/db_link
[2] Ability to rename schemas will be available in PostgreSQL 7.4