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:
For example, I create one table without a schema identifier, and see it stores in the default PUBLIC schema.
CREATE TABLE tbl_TestSearchPath
ID INTEGER PRIMARY KEY
,Name CHARACTER VARYING(50)
Below are a 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.
SELECT *FROM tbl_TestSearchPath;
SELECT *FROM public.tbl_TestSearchPath;
If we are dealing with multiple schema in a single database, I 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.
SET search_path TO Schema_Name;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.