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 namepostgres=# \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
jpostgres=#
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 schemapostgres=# 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 schemaspostgres=# \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)
Comments
Post a Comment