Schema is nothing but user ownes
- Tables
- Views
- Sequences
- Functions
- Domains
- Packages
- Procedures
- Synonyms
- Database Links
- Procedures
Benefits of Schems:
- A database can contains one or more named sachems.
- By default, all databases contain a public schema.
There are several reasons why one might want to use sachems:
- 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 cannot collide with the names of other objects.
Schema search path:
- Qualified names are tedious to write, so we use table names directly in queries.
- If no schema name is given, the schema search path determines which schemas are searched for matching table names
Example:
SELECT * FROM employee
SELECT * FROM employee
This statement will find the first employee table from the schemas listed in the search path.
- The first schema named in the search path is called the current schema if that named schema exists.
- 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.
- To show the current search path, use the following command:
SHOW search_path;
- To put our new schema in the path, we use:
SET search_path TO myschema, public;
- The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored.
- The second element refers to the public schema that we have seen already. To put our new schema in the path, we use:
SET search_path TO myschema, public;
Default Schema:
postgres=# show search_path;
search_path
----------------
"$user",public
(1 row)
postgres=#
To put our new schema in the path, we use:
SET search_path TO myschema, public;
SET search_path TO myschema, public;
Example:
postgres=# set search_path TO myschema,public;
SET
postgres=# create table cust(id integer, name varchar(10));
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
----------+--------------------------+----------+----------
myschema | cust | table | postgres
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(3 rows)
postgres=# set search_path to "$user",public;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+----------
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(2 rows)
SET
postgres=# create table cust(id integer, name varchar(10));
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
----------+--------------------------+----------+----------
myschema | cust | table | postgres
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(3 rows)
postgres=# set search_path to "$user",public;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+----------
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(2 rows)
also see below
