Creating, altering, and deleting schemas - Amazon Redshift

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';