PostgreSQL Schema Management

POSTGRESQL SCHEMA:

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:

1.     To allow many users to use one database without interfering with each other.
2.     To organize database objects into logical groups to make them more manageable.
3.     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 ;

      nspname       | nspowner |               nspacl                

--------------------+----------+-------------------------------------

 pg_toast           |       10 | 

 pg_temp_1          |       10 | 

 pg_toast_temp_1    |       10 | 

 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}

 public             |       10 | {postgres=UC/postgres,=UC/postgres}

 information_schema |       10 | {postgres=UC/postgres,=U/postgres}

(6 rows)

Find current schema:

pearl=# show search_path ;

   search_path   

-----------------

 "$user", public

(1 row)

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 ;

SET

pearl=# show search_path ;

   search_path   

-----------------

 "$user", public

(1 row)

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 ;

ALTER TABLE

pearl=# \dt public.*

         List of relations

 Schema | Name  | Type  |  Owner   

--------+-------+-------+----------

 public | mms   | table | postgres

 public | muthu | table | postgres

(2 rows)

How to change Schema name:

pearl=# alter schema s1 rename to s2;

ALTER SCHEMA

pearl=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description       

--------+----------+----------------------+------------------------

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         | 

 s2     | postgres |                      | 

(2 rows)

How to change schema owner:

pearl=# alter schema s2 owner to u1;

ALTER SCHEMA

pearl=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description       

--------+----------+----------------------+------------------------

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         | 

 s2     | u1       |                      | 

(2 rows)

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

pearl=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description       

--------+----------+----------------------+------------------------

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         | 

(1 row)