Schemas

Andrey Aksenov

Contents

Overview
Default schemas
Create new schemas
Alter schemas
View information about schemas
- psql meta-commands
- SQL commands
Schema search path
Drop schemas

Schemas are used to organize database objects, such as tables, views, or indexes. Schemas allow you to have more than one object with the same name in one database without conflicts — if these objects belong to different schemas.

This topic describes how to view information about schemas, create new schemas and drop the existing ones, and use search paths to access schema objects.

A schema is a logical union of objects within a single database. Unlike databases, schemas are not strictly isolated — users with the necessary privileges can query objects across multiple schemas within the same database.

A newly initialized Greengage DB cluster includes a predefined set of default schemas. For example, the gp_toolkit administrative schema allows you to query system catalogs, log files, and the operating environment for system status information. public is the default schema where new database objects are created.

You can also create new schemas that help to:

Allow multiple users to access a database without conflicts by using user-private schemas.
Organize database objects into logical groups for easier management, especially when setting access privileges.
Isolate third-party applications in separate schemas to prevent naming conflicts.

NOTE

Note that schemas cannot be nested.

The following schemas are created in a Greengage DB cluster by default:

public

The default schema used for each new database object you create unless you explicitly specify another schema or change the default schema search path. All database roles have the CREATE and USAGE privileges in the public schema. If you create a new schema, you need to grant privileges to your users to allow access to this schema.
gp_toolkit

An administrative schema allowing you to query system catalogs, log files, and the operating environment for system status information.
pg_catalog

Contains information about database objects: system catalog tables, built-in data types, functions, and operators. Note that this schema is always included in the schema search path, even if not explicitly specified.
information_schema

Contains a standardized set of views that provide information about the database objects. These views get information from the system catalog tables according to the ANSI SQL 2008 standard.
pg_aoseg

Stores append-optimized table objects. Greengage DBMS uses this schema internally.
pg_bitmapindex

Stores bitmap index objects, such as lists of values. Greengage DBMS uses this schema internally.
pg_toast

Stores large objects, such as records exceeding the page size (Oversized-Attribute Storage Technique, TOAST). Greengage DBMS uses this schema internally.

Prerequisites

To execute commands described in the following sections, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql.

This section shows how to create new schemas.

The following commands create and connect to a new books_store database:

CREATE DATABASE books_store;
\c books_store

To create schemas in this database, use the CREATE SCHEMA command:

CREATE SCHEMA catalog;
CREATE SCHEMA sales;

You can optionally add comments for the created schemas using the COMMENT command:

COMMENT ON SCHEMA catalog IS 'Schema for storing book metadata';
COMMENT ON SCHEMA sales IS 'Schema for storing sales-related data';

To change the definition of the created schemas, use the ALTER SCHEMA command.

The command below creates a new role:

CREATE ROLE bob WITH LOGIN;

The following command sets the created role as the sales schema owner:

ALTER SCHEMA sales OWNER TO bob;

This section demonstrates how to view information about schemas using psql meta-commands.

To view user schemas, execute the \dn meta-command:

\dn+

The result should look like this:

                                  List of schemas
    Name    |  Owner  | Access privileges  |              Description
------------+---------+--------------------+---------------------------------------
 catalog    | gpadmin |                    | Schema for storing book metadata
 gp_toolkit | gpadmin | gpadmin=UC/gpadmin+|
            |         | =U/gpadmin         |
 public     | gpadmin | gpadmin=UC/gpadmin+| standard public schema
            |         | =UC/gpadmin        |
 sales      | bob     |                    | Schema for storing sales-related data

The Access privileges column is empty for new schemas, meaning no specific privileges are set. See Roles and privileges to learn how to grant privileges to your users to allow access to these schemas.

To view all database schemas, use the \dnS meta-command:

\dnS+

The result should look like this:

                                                 List of schemas
        Name        |  Owner  | Access privileges  |                         Description
--------------------+---------+--------------------+-------------------------------------------------------------
 catalog            | gpadmin |                    | Schema for storing book metadata
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 information_schema | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 pg_aoseg           | gpadmin |                    | Reserved schema for Append Only segment list and eof tables
 pg_bitmapindex     | gpadmin |                    | Reserved schema for internal relations of bitmap indexes
 pg_catalog         | gpadmin | gpadmin=UC/gpadmin+| system catalog schema
                    |         | =U/gpadmin         |
 pg_toast           | gpadmin |                    | reserved schema for TOAST tables
 public             | gpadmin | gpadmin=UC/gpadmin+| standard public schema
                    |         | =UC/gpadmin        |
 sales              | bob     |                    | Schema for storing sales-related data

The commands below demonstrate how to view the available schemas by querying system catalogs.

Get information about schemas from the pg_catalog.pg_namespace table:

SELECT *
FROM pg_catalog.pg_namespace
ORDER BY nspname;

The result should look like this:

      nspname       | nspowner |              nspacl
--------------------+----------+----------------------------------
 catalog            |       10 |
 gp_toolkit         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 information_schema |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 pg_aoseg           |       10 |
 pg_bitmapindex     |       10 |
 pg_catalog         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 pg_toast           |       10 |
 public             |       10 | {gpadmin=UC/gpadmin,=UC/gpadmin}
 sales              |    57418 |

Get information about schemas from the information_schema.schemata table:

SELECT *
FROM information_schema.schemata
ORDER BY schema_name;

The result should look like this:

 catalog_name |    schema_name     | schema_owner | default_character_set_catalog | default_character_set_schema | default_character_set_name | sql_path
--------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+----------
 books_store  | catalog            | gpadmin      |                               |                              |                            |
 books_store  | gp_toolkit         | gpadmin      |                               |                              |                            |
 books_store  | information_schema | gpadmin      |                               |                              |                            |
 books_store  | pg_aoseg           | gpadmin      |                               |                              |                            |
 books_store  | pg_bitmapindex     | gpadmin      |                               |                              |                            |
 books_store  | pg_catalog         | gpadmin      |                               |                              |                            |
 books_store  | pg_toast           | gpadmin      |                               |                              |                            |
 books_store  | public             | gpadmin      |                               |                              |                            |
 books_store  | sales              | bob          |                               |                              |                            |

Greengage DB provides three ways to access database objects:

database.schema.object — in this form, the path to an object includes a database and schema names. This form is supported for ANSI SQL compliance because you can only access one database at a time. If you use this form to access a database object, the database name must match the one you are currently connected to.
schema.object — in this form, the path to an object specifies a schema name explicitly.
object — in this form, a schema name is omitted. This syntax uses a schema search path to find the required object.

A search path is an ordered comma-separated list of schemas used to locate objects when a schema is not explicitly specified. The requested object is searched in the first schema of the search path, in case of failure — in the second one, and so on. An error is reported if there is no match in the search path.

Below is an example of a schema search path:

catalog, sales, public

In this case, an object is searched in the following order: catalog → sales → public.

NOTE

The pg_catalog system catalog schema is always searched, even if not included in the path.

A schema listed first in the search path is the current schema. The current schema is used for creating new objects if no schema is specified.

To configure a search path, use the search_path parameter. A search path can be set at different levels, which are applied with the following precedence, from lowest to highest:

Globally (at the cluster level), by using a corresponding server configuration parameter.
At the database level.
For the specified role.
At runtime, for example, inside a session.

To show the current schema search path, use the SHOW command:

SHOW search_path;

By default, this command returns the following:

  search_path
----------------
 "$user",public

"$user" identifies a schema with the same name as the current user’s name. If no such schema exists, the entry is ignored.
public is a predefined schema used by default for new database objects you create.

To get the current schema, use the current_schema() function:

SELECT current_schema();

By default, it returns public:

 current_schema
----------------
 public

This means that a schema with the same name as the current user’s name does not exist.

To determine the search path used at the cluster level, use the gpconfig -s command:

$ gpconfig -s search_path

The result might look like this:

Values on all segments are consistent
GUC          : search_path
Master  value: "$user",public
Segment value: "$user",public

To change the search path, use gpconfig -c:

$ gpconfig -c search_path -v '"$user"'

Then, reload the configuration using gpstop to apply the changes:

$ gpstop -u

To set the search path at the database level, use the ALTER DATABASE command:

ALTER DATABASE books_store SET search_path TO catalog, sales, public;

To apply the changes, reconnect to the database using the \c meta-command:

\c books_store

Then, check the search path using SHOW:

SHOW search_path;

The result should look like this:

      search_path
------------------------
 catalog, sales, public

The first catalog is a current schema used to create new objects. Create a new books table without specifying the schema name:

CREATE TABLE books
(
    book_id SERIAL
)
    DISTRIBUTED BY (book_id);

Create the customers table with the sales schema explicitly specified:

CREATE TABLE sales.customers
(
    customer_id SERIAL
)
    DISTRIBUTED BY (customer_id);

Check that the required schemas are used for both tables using the \dt meta-command:

\dt

The result should look like this:

                List of relations
 Schema  |   Name    | Type  |  Owner  | Storage
---------+-----------+-------+---------+---------
 catalog | books     | table | gpadmin | heap
 sales   | customers | table | gpadmin | heap

Before specifying the search path for a role, create a role and grant them the USAGE privilege on the schema:

CREATE ROLE alice WITH LOGIN;
GRANT USAGE ON SCHEMA sales TO alice;

To set the search path for the role, use the ALTER ROLE command:

ALTER ROLE alice SET search_path TO sales;

To apply the changes, reconnect to the database under this role using the \c meta-command:

\c books_store alice

Then, check the search path using SHOW:

SHOW search_path;

The result should look like this:

 search_path
-------------
 sales

Note that the alice role’s search path takes precedence over the books_store database’s search path specified in the Set a search path for a database section.

You can set a search path at runtime using the SET command. For example, you can change a search path at the session level.

Reconnect to the books_store database under the gpadmin user:

\c books_store gpadmin

Then, use the SET command to change the search path:

SET search_path TO public;

Check the result using SHOW:

SHOW search_path;

You can reset the search path to the default value using the RESET command:

RESET search_path;

If you execute SHOW search_path again, the output should show that the search path has been restored to the one used for the books_store database:

      search_path
------------------------
 catalog, sales, public

By default, the schema must be empty before you can drop it. You need to delete schema objects manually before dropping the schema:

DROP TABLE catalog.books;
DROP SCHEMA catalog;

To drop a schema and all of its objects, use DROP SCHEMA with the CASCADE option:

DROP SCHEMA sales CASCADE;

Tablespaces

Table storage types

Schemas

Overview

Default schemas

Create new schemas

Alter schemas

View information about schemas

psql meta-commands

SQL commands

Schema search path

Overview

Default search path

Set a search path globally

Set a search path for a database

Set a search path for a role

Change a search path at runtime

Drop schemas