EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Postgres List Schemas
 

Postgres List Schemas

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 15, 2023

Postgres List Schemas

 

 

Introduction to Postgres List Schemas

The database administrator and manager need to be aware of the database server’s environment. In the case of PostgreSQL, the server can handle data from multiple databases at a single time. The database administrator needs to know about all the databases and the tables, and schema related data about the database server. This article will learn how to check and view the information in Postgres List Schemas.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

We will begin by knowing what schema is in the database. The schema in any database is a collection of views that stores all information about the managed objects. The information schema is stable and portable because it is defined inside the SQL standards. However, the system catalogs are variable as they contain specific information and are modelled after PostgreSQL implementation. The schema is accessible to the first user of the database and has all the privileges to handle and operate on the schema, including dropping it.

Datatypes – Postgres List Schemas

The views in the information schema use a special type of data types, which are simple domains created over the built-in data types. We should not use these data types in our external work with databases, as the information schema view exclusively uses them. However, when the information is fetched from the information schema, you need to handle them in your application. All columns in information schema views can belong to one of the following datatypes.

Data type Description
cardinal_number It is a non-negative, i.e., positive integer
character_data It specifies the string of characters with no maximum length specified, which is used for fields containing data other than SQL identifiers.
sql_identifier It specifies the string of characters with no maximum length specified, used for SQL identifiers.
time_stamp It is the domain defined over timestamp, which also considers time zones.
yes_or_no The string of characters can either have YES or NO value in it. It is kept for backward compatibility of information schema as it was created and used even before the boolean data type was added in the SQL standards, which had either true/false value.

MetaCommands

Instead of using raw SQL queries to list the data from the database, we can use short and precise metacommands that can be used with psql. Psql evaluates these metacommands and, if issued in the server’s system tables, translates them to SQL raw commands. A backslash recognizes metacommands and the command keyword followed by the parameters, if any if you want to pass to the query.

When you create a PostgreSQL server, it comes with three default databases: template0, template1, and Postgres. The CREATE DATABASE command uses the basic databases, template, and template1 for its operations. We refer to these two databases as skeleton databases. The default database that is selected and displayed is the Postgres database. After that, you can create databases of your choice and switch to them to create and manipulate tables in your databases. All databases can be retrieved and listed using the metacommand \list or \l and switched from one to another using \connect or \c. We can list out all the tables using the metacommand \dt command.

How to Use Postgres List Schemas?

Now, we will see how to list databases using the psql command.\list, or \l can be used.

Open your PostgreSQL command prompt and then type SQL to get its command prompt. No type \list and press enter. You will see these output “Three default databases of PostgreSQL”.

Code:

sudo su - postgres
psql

Output:

psql command

Let us create one new database named demo by using the createdb command. Exit the psql by typing \q, type createdb demo and press enter. Then again, enter \list and press enter to list out all the databases after typing psql to get the command prompt of psql. The output will be as follows –

Code:

\q
createdb demo
psql
\list

Output:

psql command

Schemas

Three types of schemas are present in any PostgreSQL database: information schemas, temporary schemas, and default pg_* schemas. Besides the user-defined and public schemas, one more type of schema in PostgreSQL is the pg_catalog schema. This schema contains information about the current database’s system tables, operators, data types, and functions. We will create a query to retrieve from the pg_catalog schema to list out all the user-related schemas.

Code:

SELECT * FROM pg_catalog.pg_namespace;

Output:

Postgres List Schemas - 3

The field nspname displays the names of the schemas. These are all the schemas present in our database right now. The npsowner field stores the user id which owns that schema. We can see that the user with id 10 owns all schemas. To get the information about the user with id 10, we can query the pg_user table.

Code:

SELECT * FROM pg_catalog.pg_user where usesysid = 10;

Output:

Postgres List Schemas - 4

We can say that the user with usesysid =10 is postgres which owns all the above-retrieved schemas. If we want to retrieve the name os the schema and the person owning it in a single output, we can create a join on pg_namespace and pg_user, as shown below.

Code:

SELECT s.nspname AS table_schema, u.usename AS OWNER FROM pg_catalog.pg_namespace s JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner ORDER BY table_schema;

Output: 

Postgres List Schemas - 5

Conclusion

PostgreSQL databases provide us with compact and immensely useful metacommands for database administrators and managers to check the database environments and structure related information faster and more effectively during their daily routines. Schemas can be retrieved from the system tables of pg_catalog schema, which is present in PostgreSQL databases. pg_namespace and pg_user are two main tables that convey schema related information to us.

Recommended Articles

We hope that this EDUCBA information on “Postgres List Schemas” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. GRANT PostgreSQL
  2. PostgreSQL CASE Statement
  3. For Loop PostgreSQL
  4. PostgreSQL While Loop

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW