PostgreSQL Create Schema

In this article, we will see how to Create schema in PostgreSQL Server. CREATE SCHEMA is the command used to create schema in PostgreSQL. To create a schema, the invoking user must have the CREATE privilege for the current database.

• A schema is essentially a namespace: it contains named objects whose names can duplicate those of other objects existing in other schemas.
• Named objects are accessed either by “qualifying” their names with the schema name as a prefix, or by setting a search path that includes the desired schema(s).

PostgreSQL Create Schema Syntax:

PostgreSQL Create Schema Examples:

1. Create Schema sales.

create schema sales;

2. Create a schema for user james ; the schema will also be named as james:

 CREATE SCHEMA AUTHORIZATION james;

3. Create a schema and create a table within it.

CREATE schema school
CREATE TABLE student(name varchar(10) sno int);

4. To get list of schemas use meta command \dn.

r2schools=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 james  | james
 public | postgres
 sales  | postgres
 school | postgres

We cant drop a schema, if there are objects in the schema.

5. Drop schema.
Before drop a schema, we must have taken backup of schema objects.
To drop empty schema

drop schema hr;

To drop non-empty schema:

drop schema hr cascade;