Wednesday 13 July 2016

postgreSQL what is schema?

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
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;

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)



also see below

No comments:

Post a Comment