3.21. Schemas

In the object hierarchy under every database are Schemas. Schemas allow Database Administrators to classify data and arrange permissions in a better manner, allowing them to restrict access in a more controlled manner.

Except for languages and casts, all objects in a PostgreSQL 7.3 database exist under schemas. All system objects exist under a schema named pg_catalog. In addition to this, PostgreSQL 7.3 also creates a public schema under which you should place common user objects.

You can access the CREATE SCHEMA dialog from any schemas node under any database by right-clicking and selecting CREATE SCHEMA.

At the top of the CREATE SCHEMA dialog is an entry field for the new schema name. Below it is the Owner name, which is optional. The CREATE SCHEMA syntax allows for specification of either one of schema name or owner, or both when creating a schema. Refer to the PostgreSQL 7.3 Reference Manual for more information.

Below the Owner name there is the Schema Elements field. Schemas have an option to be created along with objects that reside under them. This creates the objects when the schema is created. The objects can be dropped later if needed, or more can be added as required. Currently, the objects that can be created with a schema are limited to tables and views. In addition, you can use the GRANT statement when creating a schema, which enables you to specify permissions. The CREATE and GRANT statements go into the Schema Elements field. Optionally, you can also define the elements and export them, or import them from another file using the import/export buttons at the bottom.

For this example you will create a schema called web_store_schema with a table inventory specified as a schema element.

1. A schema is an object under a database, so to create a schema, first expand the database under which you want to create the schema (click the [O-] beside the database name).

2. Right-click on Schemas and select CREATE SCHEMA.

3. For schema name, type: web_store_schema

4. Leave the Owner field empty.

5. For the Schema Elements field, type:

   CREATE TABLE inventory (item_id int4, quantity int4, PRIMARY KEY (item_id));

6. Click OK to create the schema along with the table inventory.

Note that the schema web_store_schema has been added under the Schema node in the Tree View.

Administrator also enables you to perform the following operations on an existing schema: