Schema in PostgreSQL

May 21, 2021

Schema in PostgreSQL

In PostgreSQL, a schema is a namespace that contains database objects such as tables, views, indexes, data types, functions, stored procedures. A database can contain one or multiple schemas and each schema belongs to only one database.

PostgreSQL automatically creates a public schema for every new database. Whatever DB objects are created without specifying the schema name, PostgreSQL will place it into this public schema.

Please note by default, every user has the CREATE and USAGE on the public schema.

Role/Users can only access objects in the schemas that they own. It means they cannot access any objects in the different schema which is not owned by you. To allow role/users to access the objects in the schema that they do not own, you must grant the required privilege of the schema to the role/users. Click here to know more about Create, Drop, Alter Roles in PostgreSQL

Step 1. Login to PostgreSQL database server: Login to PostgreSQL using the below command use \dn+ command to list existing schema in a database. As you can see we have only a public schema which is created by default in our sample DB dvdrental. Click here to know more about How To Load PostgreSQL Sample Database.

[root@test-machine02 ~]#  psql -htest-machine02 -Upostgres -W dvdrental
Password:
psql (13.2)
Type "help" for help.

dvdrental=#
dvdrental=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

Create New Schema

Step 2. Create Schema: Use the command “create schema” to create a new schema. In the below example, we will create a new schema called sale, and it’s owned by role/user Postgres.

dvdrental=# create schema sale;
CREATE SCHEMA
dvdrental=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
 sale   | postgres |                      |
(2 rows)

dvdrental=#

Step 3. Set Schema Search Path: Let create a sample table in DB and you will notice it’s created in public schema this is because role Postgres is set to use public schema you can use the command SELECT current_schema() to verify it.

When you access a table using its name only, PostgreSQL searches for the table by using the schema search path, which is a list of schemas to look in. Use the command show search_path to know the current setting.

The first schema in the search path is called the current schema. Note that when you create a new object without explicitly specifying a schema name, PostgreSQL will also use the current schema for the new object.

The “$user” specifies that the first schema that PostgreSQL will use to search for the object, which has the same name as the current user/role.

For example, if you use the Postgres role/user to login and access the product table. PostgreSQL will search for the product table in the Postgres schema. If it cannot find any object like that, it continues to look for the object in the public schema.

To add the new schema to the search path, you use the following command SET search_path TO

Now, if you create a new table named orders without specifying the schema name, PostgreSQL will put this orders table into the sale schema.

dvdrental=#
dvdrental=# create table product (
dvdrental(#    id int generated always as identity,
dvdrental(#    first_name varchar(100) not null,
dvdrental(#    last_name varchar(100) not null,
dvdrental(#    email varchar(255) not null unique,
dvdrental(#    phone varchar(25) not null,
dvdrental(#    primary key(id)
dvdrental(# );
CREATE TABLE

dvdrental=# \dt product
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | product | table | postgres
(1 row)


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


dvdrental=#  show search_path;
   search_path
-----------------
 "$user", public
(1 row)

dvdrental=#
dvdrental=# SET search_path TO sale, public;
SET

dvdrental=#  show search_path;
  search_path
---------------
 sale, public
(1 row)

dvdrental=# SELECT current_schema();
 current_schema
----------------
 sale
(1 row)

dvdrental=# create table orders (
dvdrental(#    id int generated always as identity,
dvdrental(#    first_name varchar(100) not null,
dvdrental(#    last_name varchar(100) not null,
dvdrental(#    email varchar(255) not null unique,
dvdrental(#    phone varchar(25) not null,
dvdrental(#    primary key(id)
dvdrental(# );
CREATE TABLE
dvdrental=# \dt orders
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 sale   | orders | table | postgres
(1 row)

dvdrental=#

dvdrental=#
dvdrental=#
dvdrental=# select count(*) from orders;
 count
-------
     0
(1 row)
dvdrental=#

Step 4. Create a user with the AUTHORIZATION option: You can use the option AUTHORIZATION to grant ownership of schema to another role. In the below example we logged in with Postgres role/user but provided ownership to test2_role.

dvdrental=# create schema test2_schema AUTHORIZATION  test2_role;
CREATE SCHEMA
dvdrental=# \dn+
                              List of schemas
     Name     |   Owner    |  Access privileges   |      Description
--------------+------------+----------------------+------------------------
 public       | postgres   | postgres=UC/postgres+| standard public schema
              |            | =UC/postgres         |
 sale         | postgres   |                      |
 test2_schema | test2_role |                      |
(3 rows)

dvdrental=#

Alter Schema

Step 5. Alter Schema: Use command alter schema to alert attributes of a schema like ownership or name. In the below example we change the name of schema from test2_schema to test2_new and provide ownership to postgres.

dvdrental=#

dvdrental=# ALTER SCHEMA test2_schema  RENAME TO test2_new;
ALTER SCHEMA
dvdrental=#

dvdrental=# ALTER SCHEMA test2_new   OWNER TO postgres;
ALTER SCHEMA
dvdrental=#
dvdrental=#  \dn+
                           List of schemas
   Name    |  Owner   |  Access privileges   |      Description
-----------+----------+----------------------+------------------------
 public    | postgres | postgres=UC/postgres+| standard public schema
           |          | =UC/postgres         |
 sale      | postgres |                      |
 test2_new | postgres |                      |
(3 rows)

dvdrental=#

Drop Schema

Step 6. Drop Schema: Use the command drop schema to drop any schema. If you try to drop schema containing objects you will receive the below error. You can use option CASCADE to drop it anyway.

dvdrental=# drop schema sale;
ERROR:  cannot drop schema sale because other objects depend on it
DETAIL:  table orders depends on schema sale
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
dvdrental=#
dvdrental=# drop schema sale cascade;
NOTICE:  drop cascades to table orders
DROP SCHEMA
dvdrental=#

Click here for Create, Drop, Alter Roles in PostgreSQL

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

PGADMIN, POSTGRESQL, SCHEMA

Schema in PostgreSQL

Related Articles