PostgreSQL Schema Management

A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions.

 

There are several reasons why one might want to use schemas:

To allow many users to use one database without interfering with each other.
To organize database objects into logical groups to make them more manageable.
Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

Default Schema:

public

Examples:

Current schema list of table:

pearl=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
——–+——-+——-+———-+——-+————-
public | muthu | table | postgres | 16 kB |
(1 row)

Current database list of schemas:

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

pearl=# select * from pg_namespace ;

Find current schema:

pearl=# show search_path ;

How to create schema:

pearl=# create schema s1;
CREATE SCHEMA

how to change schema:

pearl=# set search_path TO s1;
SET
pearl=# create table siva(id int,name text);
CREATE TABLE

pearl=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
——–+——+——-+———-+————+————-
s1 | siva | table | postgres | 8192 bytes |
(1 row)

How to set Default schema:

pearl=# set search_path TO DEFAULT ;

pearl=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
——–+——-+——-+———-+——-+————-
public | muthu | table | postgres | 16 kB |
(1 row)

How to create table on particular schema:

pearl=# create table s1.mms(id int,name text);
CREATE TABLE

pearl=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
——–+——-+——-+———-+——-+————-
public | muthu | table | postgres | 16 kB |
(1 row)

List of particular schema tables:

pearl=# \dt s1.*
List of relations
Schema | Name | Type | Owner
——–+——+——-+———-
s1 | mms | table | postgres
s1 | siva | table | postgres
(2 rows)

pearl=# \dt public.*
List of relations
Schema | Name | Type | Owner
——–+——-+——-+———-
public | muthu | table | postgres
(1 row)

Move table for one schema to another schema:

pearl=# alter table s1.mms set schema public;

How to change Schema name:

pearl=# alter schema s1 rename to s2;

How to change schema owner:

pearl=# alter schema s2 owner to u1;

How to drop schema:

pearl=# drop schema s2;
ERROR: cannot drop schema s2 because other objects depend on it
DETAIL: table s2.siva depends on schema s2
HINT: Use DROP … CASCADE to drop the dependent objects too.
Any objects depending on schema to drop:

pearl=# drop schema s2 cascade;
NOTICE: drop cascades to table s2.siva
DROP SCHEMA