SCHEMA CREATIONSchema : Schema is a named space that contains named objects such as tables, datatypes, functions and operators.A schema can also contain views, indexes, sequences. One database can have multiple schema. A schema helps in separation of data between different application. Assume we have a production database which has different department such as HR,Finance, transport, manufacturing and all the departments are accessing the Production database. Here we can create a schema where we can include all the table belonging to HR dept, by that way when we need a backup we can take the particular schema backup alone rather than taking full DB backup. In one database we can have multiple users accessing multiple schema without interfering with each other. There are several reasons why one might want to use schemas:
Syntax :
create schema < schema name>;
list out the schemas
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# create schema s1;
CREATE SCHEMA
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
public | postgres
s1 | postgres
(2 rows)
Setting the created schema as default schema so that all the objects created from now will be located in this schema Syntax :
Set search_path to < schema name>;
View the default Schema :
Show search_path;
Changing the schema
postgres=# set search_path to s1;
SET
postgres=# show search_path;
search_path
-------------
s1
(1 row)
Now let us create multiple objects with same name in different schema :
\dn to list out the schemas
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
2.Step to list out tables :
postgres=# \dt;
Did not find any relations. 3.creating table and insert values :
postgres=# create table vehicle(details varchar(50));
CREATE TABLE
postgres=# insert into vehicle values('this is a public car');
INSERT 0 1
postgres=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | vehicle | table | postgres
(1 row)
postgres=# select * from vehicle;
details
----------------------
this is a public car
(1 row)
4.creating a schema and creating a table inside the schema :
postgres=# create schema hr;
CREATE SCHEMA
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
hr | postgres
public | postgres
(2 rows)
5.creating a table inside the schema “hr” (method 1) :
postgres=# create table hr.vehicle (details varchar(50));
CREATE TABLE
postgres=# insert into hr.vehicle values('this is HR car');
INSERT 0 1
postgres=# set search_path to hr;
SET
postgres=# show search_path;
search_path
-------------
hr
(1 row)
postgres=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
hr | vehicle | table | postgres
(1 row)
postgres=# select * from vehicle;
details
----------------
this is HR car
(1 row)
6.creating a table inside the schema (method 2) :
postgres=# set search_path to hr;
SET
postgres=# show search_path;
search_path
-------------
hr
(1 row)
postgres=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
hr | vehicle | table | postgres
(1 row)
postgres=# create table bike (details varchar(50));
CREATE TABLE
postgres=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
hr | bike | table | postgres
hr | vehicle | table | postgres
(2 rows)
postgres=# insert into bike values('this is my bike');
INSERT 0 1
postgres=# select * from bike;
details
-----------------
this is my bike
(1 row)
7.Now when select from vehicle table it will show depending on schema :
postgres=# set search_path to public;
SET
postgres=# show search_path;
search_path
-------------
public
(1 row)
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
hr | postgres
public | postgres
(2 rows)
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | vehicle | table | postgres
(1 row)
postgres=# select * from pg_tables where tablename = 'vehicle';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
| rowsecurity
------------+-----------+------------+------------+------------+----------+------------
-+-------------
public | vehicle | postgres | | f | f | f
| f
hr | vehicle | postgres | | f | f | f
| f
(2 rows)
postgres=# select * from vehicle;
details
----------------------
this is a public car
(1 row)
8.Steps to Move table from one schema to another schema :
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
hr | postgres
public | postgres
(2 rows)
postgres=# \dt hr.*;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
hr | bike | table | postgres
hr | vehicle | table | postgres
(2 rows)
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | vehicle | table | postgres
(1 row)
postgres=# alter table hr.bike set schema public;
ALTER TABLE
postgres=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | bike | table | postgres
public | vehicle | table | postgres
(2 rows)
9.Steps to change Schema name :
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
hr | postgres
public | postgres
(2 rows)
postgres=# alter schema hr rename to hr_public;
ALTER SCHEMA
postgres=# \dn;
List of schemas
Name | Owner
-----------+----------
hr_public | postgres
public | postgres
(2 rows)
10.Steps to change schema owner:
postgres=# \du;
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user_db | | {}
postgres=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-----------+----------+----------------------+------------------------
hr_public | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
postgres=# alter schema hr_public owner to user_db;
ALTER SCHEMA
postgres=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-----------+----------+----------------------+------------------------
hr_public | user_db | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
11.Step drop schema :
postgres=# \dn;
List of schemas
Name | Owner
-----------+----------
hr_public | user_db
public | postgres
(2 rows)
11.Droping Schema :
postgres=# drop schema hr_public cascade;
NOTICE: drop cascades to table hr_public.vehicle
DROP SCHEMA
postgres=# \dn;
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
|