Creating, altering, and deleting schemas - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Creating, altering, and deleting schemas

Any user can create schemas and alter or drop schemas they own.

You can perform the following actions:

  • To create a schema, use the CREATE SCHEMA command.

  • To change the owner of a schema, use the ALTER SCHEMA command.

  • To delete a schema and its objects, use the DROP SCHEMA command.

  • To create a table within a schema, create the table with the format schema_name.table_name.

To view a list of all schemas, query the PG_NAMESPACE system catalog table:

select * from pg_namespace;

To view a list of tables that belong to a schema, query the PG_TABLE_DEF system catalog table. For example, the following query returns a list of tables in the PG_CATALOG schema.

select distinct(tablename) from pg_table_def
where schemaname = 'pg_catalog';