This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Most of the PostgreSQL Professionals are wondering about that why objects are stored in default public schema when they have not supplied schema identifier.
The Schema Search Path of the PostgreSQL:
The best practice is to provide a schema identifier for each and every database object, but also this is one of the important topic about schema identifier because sometimes specifying an object with the schema identifier is a tedious task.
The currently default schema search path is PUBLIC schema so whenever we are creating any object with schema identifier, it creates in the default PUBLIC schema.
Below is a script to check current search path:
|
1 2 3 4 5 6 7 |
SHOW search_path; /* Result search_path ------------------ "$user", public */ |
For example, I create one table without a schema identifier and see it stored in the default PUBLIC schema.
|
1 2 3 4 5 |
CREATE TABLE tbl_TestSearchPath ( ID INTEGER PRIMARY KEY ,Name CHARACTER VARYING(50) ); |
Below are two SELECT statements which have no any difference because when we are using the database object without the schema identifier, it fetches from the default PUBLIC schema.
|
1 2 |
SELECT *FROM tbl_TestSearchPath; SELECT *FROM public.tbl_TestSearchPath; |
If we are dealing with multiple schemas in a single database, I would suggest you to always provide Schema Identifier for each and every database object.
But when we are dealing with only one schema and if you want to change your default schema search path, you can use below script to change default schema search path.
|
1 |
SET search_path TO Schema_Name; |
