Part 11 - Postgres: What is Schema in PostgreSQL

hello and welcome to email tech skills video tutorial in this video tutorial we will learn what is post grace equal schema so in this slide we will learn now what is poster a schema so if you have already installed post Cree sequel software either on Linux or Windows system you will know that by default there will be one instance if you wanted to have more than one instance you can do that so at the top level you have instance and in the instance you have databases so you can say that a poster a sequel database cluster contains one or more name databases and in turn or database can have one or more objects which are called as schemas what is the schema schemas in turn can have tables or different types of objects or it is just a container which can have different types of objects in it so if I say in a particular database if I am connected to I can create multiple schema let's say I create a schema one I can create a schema two or n number of schemas I can content so what will happen have over there let's say I have created a table which is called as actor in schema one and another table with the same name in another schema so let's say the name is again actor in schema so schema two so two schemas with with different name schema one and a schema - but same table name which is actor so it is good to organize the tables in different different schemas and so if you talk about the schema is it synonymous to anything so it is something like directory or folder at the operating system level there is nothing more than that in in that folder there will be a different objects like tables or views or what do you say the functions etc so this is a brief introduction of a schema schema is nothing just a logical container which can have different tables and data types so why do we need is to use a schema is it is it something new so no it is not something new so a schema allows you to organize database objects let's say I have a database with the name DVD rental and if I have two schemas let's say schema 1 and schema - and I have one table which is known as actor if I wanted to access it through two different schemas I can do that so it is easier to organize the objects in the schema and if I want it to take a backup of the particularly schema if it is if I have to restore it then it is it is very easy to do that and organization in particular a schema will be easier what I can do is I can restrict the user to work on to a particular schema and I can say you don't have access on other schema so it is good to have schemas in place it is something like mandatory you should you should use it there is something called as public schema when we talk about poster a public doesn't mean that it is available for entire public or all the users which are connected to the database now sorry I mean to the mean to the instance so when you when you create a database in PostScript by default there will be a schema which is the only schema present which is known as public that means if you have not created any user defined schema then all the objects which you create will be there in the public schema so any role which has access to the database or user which has access to the database can create the table in public schema and public schema is is available by default in all the databases which you create and that is the only schema which is available so there is something called as search but what happens if I say if I'm connected to DVD rental let's say my database name is DVD rental and if I try if I say select star from actor so in which a ski might is going to look for so there is something called as public schema so by default it is going to look into the public schema if you say show search path then it will search either in user dollar there is something called as user dollar and the other one is public so if if the schema name is equal to the user name then the the object will be searched in that particular schema if not then it is it will be searched in the public schema so what is the building block of poster a so we have a poster a sequel cluster that means there will be one set of software let's say post create well why I have installed so by default there will be one instance if I wanted to create another instance then I can do that so each instance in post crea will have its own set of background processes plus memory area and in each instance you can create different databases and each database will have the container which is called as schema and a schema can contain different types of objects so this is the hierarchy so you have post Cree instance or the cluster you have inside cluster or instance you have databases inside databases you have a schemas under schemas you have objects it is just like folders that's it so let's let's come back to the Jambo so um I'll show you like already connected to a PG admin for this is the IP address of my server 192 168 1.61 I have six databases I'm going to use DVD rental so if you click this you will be able to see different options and by default there is one schema which is with the name public it doesn't mean that it is available for the entire users no it is it is not like that so by default when you create or select the object it comes from the public schema so what we will do is so as of now there are there is only one schema let's create two more schemas I will create two schema with the name schema one and other again with the same schema to name a schema - okay and what I'm going to do is now either you can use the command prompt or PG admin for and let's - okay already connected over here so I create a two schema schema one and a schema two and in my public schema there is a table called as actor so if I simply fire one statement like select star from actor okay let me do one thing this is my command prompt for 192 168 1 dot 61 so I am connected as moba Eckstrom so what I will do is connected as Postgres user so I just say psql connected to a postscript prompt so if I say switch to database the databases which I have as these many so I wanted to connect to DVD rental I'm connected to DVD rental and if I say select star from actor so I can I'll be able to see the data right so this is my actor table which is available in public schema and I have created two more schema schema one and a schema two so what I'm going to do is I'm going to create the structure in these two schemas for our demo by default I click this icon so ok let's go to the query editor I'll go to a schema one expand this you can see the public schema just go to a schema one and say query tool and I will create a blank table with the name actor just execute this so this is a blank table alright create another table in the schema - let's create that as well all right so if I have to select the data from individual tables so what I will have to do is I will say if I say select start from actor by default it comes from the it comes from your public schema and if I have to say the specific schema I have to say schema one dart actor so I'll say select star from schema one dot actor so you can see that we created just a blank table and there is no row and similarly we did it for the schema two also if I say schema two dot actor so it is coming as again blank there is no data because we created the structure only and if I say select star from vector it it displays the data from public schema with the table actor so how this all is happening we will see and if you want it to drop a scheme I just say drop a schema a schema name and if there is any object you will have to say drop schema schema name and cascade there is something called as a schema search path so when I am saying select star from actor or or select star from a schema one daughter actor how it is actually doing or if I'm just saying select star from vector is there any parameter which is deciding like from where it has to pick the information so there is something called as search path so if we go to psql and say show search path it will show the two output one is dollar user and another is public that means the user which is connected so by I'm connected through the post Kray user so when I am going to create any object or select any object inside this particular user by default it will look for post Cree user schema if it is present it is going to create object or select object from there if that particular schema is not present then it will go to public so if I UPS again I click this so this is this is my DVD rental database so I have three schemas by default I'm connected through the post Cree user if that but if the schema is not presented with that name it will look for public why because that that thing has been mentioned in search part either the schema should available with the username or the second option is public so if the first option is not available it is going to look for the public schema what if I wanted to say select star from actor and it picks the information from a schema dot so what we have to do is we have to tell to the instance you go and look into a schema one so we will have to change the settings how do we do that we can if I say show search path we have user public so instead of user by default I wanted it to come from a schema one so I have to set that so we'll say set search path to schema one comma public so that means it will first look for a schema one if that is available it is going to show the data from there let's verify it yes and if I say select star from actor so I have a blank table available in a schema one so it should show that so you can see that and if I have to search from the public schema I have to explicitly mention select star from public dot actor so you can see that so however everything is being controlled everything is being controlled through search path and you have some schema and privileges as well so by default users do not access into the schemas which are which they do not own that is true you have to give the permission so what is the usage pattern you should have issue revoke create on public schema from public that means you should not allow any user to create or select the object from there selection is fine that that that's not a problem if you wanted them to create the object inside that that should not be allowed what if there are multiple users and they wanted to create the object with the same name so it will it is going to be a problem so what you should do is you should remove the public schema from default search path so what we did right now is we changed it to from user Dollar comma public to schema one comma public so you should do similar for the application user right remove the public schema from default search path by issuing alter role all set search path equal to so it is a good practice to have the schema name with the same name so that they can create the objects over there so the conclusion of this is you should not use the public schema so just a recap of what we what we did is we learned what is a schema schema is just a kind of folder or container which contains objects which you can see in sorry I clicked ok so I have a database which oops where is the screen ok so I have read aways DVD rental inside DVD rental I created by default there is one public schema so everyone will be able to so those who have access to this particular DVD rental database can select from public schema and they will be able to create the objects over there what you should do is you should create the named schema where in the application owner can can create their objects or they they are the owner of this schema and how to modify that you should change the search path so that by default it doesn't go if that user dollar is not available that means if the schema name which is not equal to the connected user is available that will go to public so you should remove that in that way that way you can organize your objects better and there if you just right-click on one of the schema then you can see you can do lot of things you can create the script you can backup you can restore you can grant the wizard you can search objects there are a lot of things you can do with that so a schema is a great great object in in your poster a and you should use it wisely I hope this is going to help and thanks for watching

Channel: E-MultiSkills Database Tutorials

Views: 7,526

Keywords: create schema in postgresql, what is schema in postgresql, postgresql schema tutorial, postgresql schema design, postgresql schema vs database

Id: DAmv-XPj2jE

Length: 18min 41sec (1121 seconds)

Published: Sat Jul 25 2020