Skip to content

Introduction to Schemas in Postgresql

  • In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures and operators.

  • A database can contain one or multiple schemas and each schema belongs to only one database.

  • Two schemas can have different objects that share the same name.

For example, you may have sales schema that has staff table and the public schema which also has the staff table. When you refer to the staff table you must qualify it as follows:

public.staff

Or

sales.staff
  • PostgreSQL automatically creates a schema called public for every new database. Whatever object you create without specifying the schema name, PostgreSQL will place it into this public schema.

Create a new schema

create schema marketting;

Create a new schema for a user

create schema sales authorization prabin;

List all schema

\dn

    List of schemas
    Name    |  Owner
------------+----------
 marketting | postgres
 public     | postgres
 sales      | prabin
(3 rows)

Show the current schema

select current_schema();

 current_schema
----------------
 public
(1 row)

Rename schema

alter schema marketting rename to finance;

  List of schemas
  Name   |  Owner
---------+----------
 finance | postgres
 public  | postgres
 sales   | prabin
(3 rows)

Change the owener of schema

alter schema finance owner to prabin;

  List of schemas
  Name   |  Owner
---------+----------
 finance | prabin
 public  | postgres
 sales   | prabin
(3 rows)

Drop Schema

drop schema sales;

  List of schemas
  Name   |  Owner
---------+----------
 finance | prabin
 public  | postgres
(2 rows)