PostgreSQL Schema -1

• A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions.
• Each user have their own schema and multiple schema
• The schema search path can be altered so that each user find their tables first and access the other user tables also.
• schema as a namespace for tables that also provides security.
• By creating a schema for users and granting them rights on that schema,
• The user will create tables on that schema instead of public.
• schema search path is set which users objects  set in postgreSQL database.
• default for all users $user, public.
• search path can be set on a users basis, to look thorough any random list of schemas.

Schema advantage:

• 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.
• Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Syntax:

create schema schema_name;
CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ];
CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ];
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ];
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name;

PRACTICAL 1.CREATING SCHEMA

--To create "benz" schema
postgres=# create schema benz;
CREATE SCHEMA

--to  create a "dept" table in spcefic schema of "benz" as user default
postgres=# create table benz.emp(dept_id integer,department varchar);       
CREATE TABLE

what is search path:
search path which consist of just the table name. The system determines which 
table is meant by following a search path, which is a list of schemas to look in. 
The first matching table in the search path is taken to be the one wanted. 
If there is no match in the search path, an error is reported, even if matching 
table names exist in other schemas in the database.
The first schema named in the search path is called the current schema. Aside 
from being the first schema searched, it is also the schema in which new tables 

will be created if the CREATE TABLE command does not specify a schema name.

--Check the current search_path,By default such tables (and other objects) are 
automatically put into a schema named "public"

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

Below command is search the "emp" table in current search path that's why it shown 
error if you want to see "emp" table you need to specify ("benz.emp") schema name 
after that specify table name

postgres=# \d emp
Did not find any relation named "dept".
Note:you cannot view the (emp table)another schema table from current schema 
without specify schema name like following command

postgres=# \d benz.emp;
              Table "benz.emp"
   Column   |       Type        | Modifiers 
------------+-------------------+-----------
  dept_id   | integer           | 

PRACTICAL 2.SCHEMA VIEWS

Here we gave some example for list down the roles,current Search path,Who is the owner of the table,user information and which schema is taken that table this is for just understanding purpose

--List down the role & roles privilege 
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        |                                                | {}

--show the current search path
postgres=# show search_path;
 "$user",public

--List down the table owner,schemaname,tablename,and tablespace name
postgres=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='emp';                          
  schemaname | tablename | tableowner | tablespace 
------------+-----------+------------+------------
 benz       | emp       | postgres   | 
(1 row)

--List down user & schema
postgres=# select schemaname,relname from pg_stat_user_tables;           
 schemaname |    relname    
------------+---------------
 public     | school_bk2
 public     | school_bk
 public     | us_snail_addy
 benz       | dept
 public     | student
 benz       | emp
 public     | school
(7 rows)

--insert some data into the "emp" table

postgres=# insert into benz.emp values (1,'nijam');
INSERT 0 1
postgres=# insert into benz.emp values (1,'nijam');
INSERT 0 1
postgres=# insert into benz.emp values (2,'john'); 
INSERT 0 1

postgres=# select * from benz.emp;
 dept_id | department 
---------+------------
       1 | nijam
       1 | nijam
       2 | john
(3 rows 

PRACTICAL 3.CREATE SAME NAME TABLE INTO THE DIFFRENT SCHEMA:

you can create already exists table name but schema should be different inside the single schema you cannot create more than one table with same name

--create dept table to benz2 schema
postgres=# create table benz2.dept (dept_id integer, department varchar);
CREATE TABLE

--insert some data
postgres=# insert into benz2.dept values(1002,'HR');
INSERT 0 1
postgres=# insert into benz2.dept values(1013,'Manager');
INSERT 0 1

--view the dept table from benz2 schema
postgres=# select * from benz2.dept;
 dept_id | department 
---------+------------
    1002 | HR
    1013 | Manager
(2 rows)

--Create another dept table into the benz schema
postgres=# create table benz.dept (dept_id integer, department varchar);
CREATE TABLE

--Check the "dept" table for which schema that is own
postgres=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='dept';
 schemaname | tablename | tableowner | tablespace 
------------+-----------+------------+------------
 benz       | dept      | postgres   | 
 benz2      | dept      | postgres   | 
(2 rows)

--fetch the two table
postgres=# select * from benz.dept;
 dept_id | department 
---------+------------
(0 rows)

postgres=# select * from benz2.dept;
 dept_id | department 
---------+------------
    1002 | HR
    1013 | Manager
(2 rows
Note:- A user can create two table as similar name but different schemas 

PRACTICAL 4.LISTING THE SCHEMA

--We can get a list of all tables in all schemas
postgres=# \dt *.*
                        List of relations
       Schema       |          Name           | Type  |  Owner   
--------------------+-------------------------+-------+----------
 benz2              | dept                    | table | postgres
 benz2              | t1                      | table | u7
 benz2              | t10                     | table | u3
 benz2              | t11                     | table | u5
 benz2              | t6                      | table | u2
 benz2              | t9                      | table | u2
 books              | databases               | table | u2
 information_schema | sql_features            | table | postgres
 information_schema | sql_implementation_info | table | postgres
 information_schema | sql_languages           | table | postgres
 information_schema | sql_packages            | table | postgres
 information_schema | sql_parts               | table | postgres
 information_schema | sql_sizing              | table | postgres
 information_schema | sql_sizing_profiles     | table | postgres
 pg_catalog         | pg_aggregate            | table | postgres
 pg_catalog         | pg_am                   | table | postgres
 pg_catalog         | pg_amop                 | table | postgres
 pg_catalog         | pg_amproc               | table | postgres
 pg_catalog         | pg_attrdef              | table | postgres
 pg_catalog         | pg_attribute            | table | postgres
 pg_catalog         | pg_auth_members         | table | postgres
 pg_catalog         | pg_authid               | table | postgres
 pg_catalog         | pg_cast                 | table | postgres
 pg_catalog         | pg_class                | table | postgres
 pg_catalog         | pg_collation            | table | postgres
 pg_catalog         | pg_constraint           | table | postgres
-

--WE can get a list of all tables In a particular schema:
postgres-# \dt benz2.* 
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 benz2  | dept | table | postgres
 benz2  | t1   | table | u7
 benz2  | t10  | table | u3
 benz2  | t11  | table | u5
 benz2  | t6   | table | u2
 benz2  | t9   | table | u2
(6 rows)

--USING VIEWS
postgres=# select * from pg_tables where schemaname='benz2';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
------------+-----------+------------+------------+------------+----------+-------------
 benz2      | dept      | postgres   |            | f          | f        | f
 benz2      | t6        | u2         |            | f          | f        | f
 benz2      | t9        | u2         |            | f          | f        | f
 benz2      | t10       | u3         |            | f          | f        | f
 benz2      | t1        | u7         |            | f          | f        | f
 benz2      | t11       | u5         |            | f          | f        | f
(6 rows)

--We can get a list of particular tables In a particular schema with expression
postgres-# \dt (benz2|sara).(t*|t*)
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 benz2  | t1   | table | u7
 benz2  | t10  | table | u3
 benz2  | t11  | table | u5
 benz2  | t6   | table | u2
 benz2  | t9   | table | u2
 sara   | t1   | table | u2
(6 rows)