Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CREATE SCHEMA

Defines a new schema for the current database.

Syntax

Copy
CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION username ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

Parameters

IF NOT EXISTS

Clause that indicates that if the specified schema already exists, the command should make no changes and return a message that the schema exists, rather than terminating with an error.

This clause is useful when scripting, so the script doesn’t fail if CREATE SCHEMA tries to create a schema that already exists.

schema_name

Name of the new schema. For more information about valid names, see Names and Identifiers.

Note

The list of schemas in the search_path configuration parameter determines the precedence of identically named objects when they are referenced without schema names.

AUTHORIZATION

Clause that gives ownership to a specified user.

username

Name of the schema owner.

schema_element

Definition for one or more objects to be created within the schema.

Limits

Amazon Redshift enforces the following limits for schemas.

  • There is a maximum of 9900 schemas per database.

Examples

The following example creates a schema named US_SALES and gives ownership to the user DWUSER:

Copy
create schema us_sales authorization dwuser;

To view the new schema, query the PG_NAMESPACE catalog table as shown following:

Copy
select nspname as schema, usename as owner from pg_namespace, pg_user where pg_namespace.nspowner = pg_user.usesysid and pg_user.usename ='dwuser'; name | owner ----------+---------- us_sales | dwuser (1 row)

The following example either creates the US_SALES schema, or does nothing and returns a message if it already exists:

Copy
create schema if not exists us_sales;