Creating Schemas

A schema is a namespace within a database. There is a default available schema name in each database, called as public. All objects created without any prefix to the object name are created inside the public schema. One can create more schemas and new objects can be created inside non-public schemas. So, a schema is a named collection of objects (tables, views, indexes, sequences, data types, operators, and functions and procedures).

Creating a simple schema

Creating a schema needs CREATE ON DATABASE privilege. If one gets an error of permission denied then, a superuser has to run below to grant the privilege to the user.

# CREATE SCHEMA fin;

appdb=> CREATE SCHEMA fin;
ERROR:  permission denied for database appdb
appdb=>

Run below from superuser.

# GRANT CREATE ON DATABASE appdb TO mkm;

Create a new schema and create a new table inside that schema.

# CREATE SCHEMA fin;

# CREATE TABLE fin.cash_box(id integer, name text);

    SELECT * from city WHERE pop > 10000;

# CREATE INDEX idx_city_high_name ON city(name);

# \dn

# \dv city_high

# \d+ city_high

appdb=> CREATE VIEW city_high as
appdb->     SELECT * from city WHERE pop > 10000;
CREATE VIEW
appdb=> CREATE INDEX idx_city_high_name ON city(name);
CREATE INDEX
appdb=> \dv
         List of relations
 Schema |   Name    | Type | Owner
--------+-----------+------+-------
 public | city_high | view | manoj
(1 row)

appdb=> \dv city_high
         List of relations
 Schema |   Name    | Type | Owner
--------+-----------+------+-------
 public | city_high | view | manoj
(1 row)

appdb=> \d+ city_high
                           View "public.city_high"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description
---------+---------+-----------+----------+---------+----------+-------------
 city_id | integer |           |          |         | plain    |
 name    | text    |           |          |         | extended |
 pop     | integer |           |          |         | plain    |
View definition:
 SELECT city.city_id,
    city.name,
    city.pop
   FROM city
  WHERE city.pop > 10000;

appdb=>

One can create schema and objects in a single statement.

CREATE SCHEMA hr
    CREATE TABLE hr_emp (id integer, name text);

appdb=> CREATE SCHEMA hr
appdb->     CREATE TABLE hr_emp (id integer, name text);
CREATE SCHEMA
appdb=>

Creating a schema with authorization creates a schema for a given user.

The below statement will create a schema john for an already existing user john.

CREATE SCHEMA AUTHORIZATION john;

The below statement will create a schema doe for an already existing user john.

CREATE SCHEMA IF NOT EXISTS doe AUTHORIZATION john;

Switching to another schema

By default, the current connection points to public schema. So, if you access any objects without any schema prefix, then it will look into public schema.

You can use SHOW search_path command to see which schemas are looked into when looking for objects you are trying to access.

You can set more schemas in the current search path or switch to another schema as shown below.

First, check the current schema.

# SELECT current_schema;

appdb=# SELECT current_schema;
 current_schema 
----------------
 public
(1 row)

appdb=#

Then, check the current search path.

# SHOW search_path;

appdb=# SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

appdb=#

Add more schemas to the search path.

# SET search_path TO fin, public;

appdb=# SET search_path TO fin, public;
SET
appdb=# SHOW search_path;
 search_path 
-------------
 fin, public
(1 row)

appdb=#