Install PostgreSQL on your system and configure it. After successful installation and the configuration of Postgresql, confirm it by checking its version on the psql. When you have installed the database, now you can use it because you have privileges to access the features present in it. If you have some knowledge of databases, then it will be favorable for you to understand the schemas. Now open psql and apply the password for the user for the server connectivity.
The first method shows the use of “\dn” in the psql terminal. This method does not show all the names of the schemas. It shows the owner of the schemas and their description, either public or temporary created.
This output shows that the schema is public and the owner of the schema is “Postgres”.
The second method is the use of the “select” statement in the command. This query is used to display all the names of schema currently present in PostgreSQL. This query fetches the name of schema from the main data storage of all the schemas.
This query helps in displaying all the schemas. Now, 4 schemas are present here.
Similarly, if you are willing to know about the schema of the temporary table, one thing should be mentioned here that we don’t create a separate schema for the temporary table because it is itself created by Postgres. We can show it by using the below-cited command
The resultant show the names of tables with the schema. For the relation books, the schema is public, which means that it is a permanently created table. And for table “table1”, it is stored in pg_temp_10, which means that the table is temporary. All the temporary tables are stored in the “pg_temp_10” schema.
Another way is to display the schema with ids and roles allocated to the schema. “Join” is used to link two tables here. This join is done at the point where the system id is equivalent to the owner id.
All schemas are displayed by showing the roles applied to them, which is the ‘postgres’. If you want to change the owner, it can be done by creating a new user and granting all privileges to them.
Another way to enlist the schema can be obtained by applying a conditional statement where the schema name should not be present in all other schemas like pg_catalog etc., so the name that is left is mentioned in the output.
So the schema that was left behind is “abc”. The output also shows the role and id_schema. Some are user-defined tables. These tables are listed in the “public” schema.
To display all the user-created tables in the schema, here we use a “select” statement and “where” clause that checks the condition of not been included in “pg_catalog” and “information_schema”. As the publically created database is “abc”, so it is not included here. This command will also show other features like row security and tablespace, but now these are not our concern.
To show the name of the current schema, use the following simple command.
This shows that the current schema is “public”.
To search for the owner or to mention the roles that control the schema, we use a search path to display the current schema with the owner’s name.
If you want to know how the new schema is created, it is quite simple.
After the new schema is created, now set the path to the newly created schema. This is done by assigning the schema path from the public to the new schema.
Now check the working of the schema by creating a new table “n_tb”
To select the data from the new table you created, we use the select statement. You must be familiar with using select statements directly on the table. But this table can be accessed by the name of schema following the table name.
Now transfer back all the privileges from the new schema to the public one.
We want to allow the “create” statement to the new schema with the user name. You can also use all other statements on which you want to apply the privileges.
After working on the schema, if you want to remove it from the database, then we need a “drop” command. Using the “drop” command directly on the schema will show an error because we don’t have an empty schema.
A relation is present in that schema. We need to remove all the content to remove all the traces of the schema
Via pgAdmin
Open the dashboard “pgAdmin”. Make a connection with the server by providing the password.
Firstly before applying the queries, we will show the schema that we have created recently. Move to the left sidebar of the dashboard. Expand the “Postgres” database. Further, expand the option “schemas”. Now select the newly created schema “new”.
In the “new” schema, you will see many options here. Now select the tables and expand them. You will see the “n_tb” that we create.
As we have described earlier, there are two types: user-defined and the other is system schemas. When we expand the database, we come with both schemas. To see the system schemas, then further expand the option of catalogs. This list of schemas is system schema. Whereas for the user schemas, expand the schemas options. The one that is marked as “green” in the below picture.
To create a new schema in pgAdmin, click on the schemas, select the first option “create” and select “schema” again.
A dialogue box will appear. Fill in by providing the details.
Conclusion
“HOW DO I LIST ALL ACHEMAS IN POSTGRESQL” provides us the solution to find the list of currently in used schemas. The information regarding present schemas and the procedure of creating new schemas is described in this article. Whereas, the dashboard allows the user to create a schema by the simple “GUI” method. I hope this article will be the best guide for your future perspective.
