Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Schema -2

PRACTICAL 5.VIEW CREATION INTO THE SCHEMA

--Create a view from "benz2.dept" table
postgres=# create view dept_v as select * from benz2.dept;
CREATE VIEW

postgres=# select * from dept_v;
 dept_id | department 
---------+------------
    1011 | Research
    1012 | Sales
(2 rows)

postgres=# \dv
         List of relations
 Schema |  Name  | Type |  Owner   
--------+--------+------+----------
 public | dept_v | view | postgres
(1 row)
-- first grant the superuser privilege to "u2" user
postgres=# alter user u2 superuser;
ALTER ROLE

--connect as u2 user 
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2: 
--Create a schema and create a table and view within it as a super user
postgres=# CREATE SCHEMA books
    CREATE TABLE databases (title text, release date, rating text)
    CREATE VIEW postgres_view AS SELECT title, release FROM databases WHERE rating IS NOT NULL;
CREATE SCHEMA
--set the book search path for objects read
postgres=# set search_path to books;
SET
postgres=# show search_path;
 search_path 
-------------
 books
(1 row)

postgres=# \dv                      
          List of relations
 Schema |    Name     | Type | Owner 
--------+-------------+------+-------
 books  | postgres_view | view | u2
(1 row)

postgres=# \dt
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 books  | databases | table | u2
(1 row)
PRACTICAL 6.CHANGING SEARCH_PATH

--Change the search path from "public" to "benz" First check the search path 
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | school        | table | postgres
 public | school_bk     | table | postgres
 public | school_bk2    | table | postgres
 public | student       | table | postgres
 public | us_snail_addy | table | postgres
(5 rows)
--set  search_path as benz
postgres=# set search_path to benz;
SET

--see what search path you are currently using
postgres=# show search_path;
 search_path 
-------------
 benz
(1 row)
--list down benz schema's table
postgres=# \dt 
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 benz   | dept | table | postgres
 benz   | emp  | table | postgres
(2 rows)
--Now you can select emp table without pointing dot(benz.emp) but if you want 
see (public or benz2) another  schema table you need to specify schema name 
like "benz2.dept" 

postgres=# select * from emp; 
 dept_id | department 
---------+------------
       1 | nijam
       1 | nijam
       2 | john
(3 rows)
PRACTICAL 7.ASSIGN SEARCH_PATH PERMANANTLY AND PARTICULAR USER:
--List down the users
postgres-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        | Superuser                                      | {}
 u3        |                                                | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
 u8        |  
--check the user u6 serach path after connected u6 user
postgres=> show search_path ;
  search_path   
----------------
 "$user",public
(1 row)
--connect as super user(u2)then assign search path permanently for a user,here 
we are assigning two search path (schema) to particular user u6
postgres=# alter user u6 set search_path to sara,benz2;
ALTER ROLE
postgres=# GRANT create ON SCHEMA sara TO u6; 
GRANT

It's from u6 user
postgres=# \q
[root@r1 bin]# ./psql -U u6 -d db2

postgres=> show search_path;
 search_path 
-------------
 sara, benz2
(1 row)


postgres=# \conninfo
You are connected to database "postgres" as user "u6" via socket in "/tmp" at port "5432".
postgres=# \q
--ReConnect  as "U6" user and check the search path correctly assigned or not
[postgres@r1 ~]$ psql -U u6 -d postgres
Password for user u6: 

postgres=> show search_path ;
 search_path 
-------------
 sara, benz2
(1 row)
postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=UC/postgres      +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 books   | u2       |                      | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       | u3=UC/u3            +| 
         |          | u5=C/u3             +| 
         |          | u6=C/u3              | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(8 rows)
PRACTICAL 8.MULTIPLE SCHEMA ASSIGNING CURRENT USER:

--connect as u2 user using command line
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2: 
--check the search path of u2 user
postgres=# show search_path;
 search_path 
-------------
 books
(1 row)
--create  two schema names are schema,schema2
postgres=# create schema schema1;        
CREATE SCHEMA
postgres=# create schema schema2;
CREATE SCHEMA
--Now assigning multiple schema for current user
postgres=# set search_path to schema1,schema2;
SET
--Now u2 user got two schema's search path
postgres=# show search_path;
   search_path    
------------------
 schema1, schema2
(1 row)
--if u2 user search the table in the above schema the user did'nt see any 
table becouse schema1 and schema2 are new schema currently nobody did not 
create objects(tables,views...etc)

postgres=# \dt
No relations found.

Also, since schema1 is the first element in the path, new objects would by 
default be created in it


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL