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 PostgreSQL List Databases
 

PostgreSQL List Databases

Updated May 22, 2023

PostgreSQL List Databases

 

 

Introduction to PostgreSQL List Databases

PostgreSQL list databases list all the databases from the database server; we can list the database using the pg_database catalog table. We can also see the list of the databases by using the \l command; The PostgreSQL l+ tool allows us to view more database descriptions. To list the database in PostgreSQL is very important and useful to see all created databases on the PostgreSQL server. To see a list of databases in PostgreSQL, we need to connect to the database through psql; we can also see the database using shell prompt.

Watch our Demo Courses and Videos

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

Syntax

  • \l
  • \l+
  • \list
  • \list+
  • Select name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN from pg_database (name_of_table);

Below is the parameter description syntax of list databases in PostgreSQL.

  • \l –This command lists all databases with information on the database name, database owner, encoding, collate, ctype, and database access privileges.
  • \l+ – This command is used to list all databases with information on the database name, owner of the database, encoding, collate, ctype, and access privileges of the database, size of the database, database tablespace name, and description for the database.
  • \list –This command is the same work as the \l command in PostgreSQL. This command is used to list all databases with information on the database name, owner of the database, encoding, collate, ctype, and access privileges of the database.
  • \list+ – This command is the same work as \l+ command in PostgreSQL. This command is used to list all databases with information on the database name, owner of the database, encoding, collate, ctype, and access privileges of the database, size of the database, database tablespace name, description for the database, encoding, collate, ctype and access privileges for the databases.
  • Select –This operation selects a column from the pg_database table to list the databases. We can select specific or all columns from the table to see the list of databases in PostgreSQL.
  • Name of the column –This is defined as the column’s name for the table of pg_database. We can select a single or all columns we want to display in the output.
  • Pg_database –This is a catalog table of PostgreSQL that contains the information of all the databases created by the user and contains the information of system databases.

How to list databases in PostgreSQL?

  • We can list the databases as follows.
  • To list the databases in PostgreSQL, we do not need to provide any privileges to the user; any user can list the databases in PostgreSQL.
  • The below example shows that we do not need to provide any privilege to list databases for users in PostgreSQL.
  • In the first example above, we checked the user’s privileges; the db_test user has no rights on the database server.
  • In the second example, we use the db_test user to list databases; using the db_test user, it’s possible to list the databases in PostgreSQL because we do not need to be given any privileges to list the databases in PostgreSQL.
psql -U postgres
\du
psql -U db_test -d postgres
\l

PostgreSQL List Databases output 1

  • We have used the below command to list all the databases as follows.
  • \l
  • \l+
  • \list
  • \list+
  • Select * from pg_database;
  • We can use the metadata command and pg_database catalog table to list the databases in PostgreSQL.
  • Using the above command, we have a list system and user-created databases.
  • We can list the database by using the shell prompt and the database shell prompt in PostgreSQL.
  • We can also use limited options to list the database using the pg_database catalog table. The pg_database catalog table is very important for fetching the information related to all the databases.

Examples

Below is an example of a list of the databases.

1. List the databases by using \l command

The below example shows that list the databases by using \l command.

\l

PostgreSQL List Databases output 2

2. List the databases by using the \l+ command

The example below shows that list of the databases using the \l+ command.

\l+

PostgreSQL List Databases output 3

3. List the databases by using the \list command

The example below shows database lists using the \list command.

\list

PostgreSQL List Databases output 4

4. List the databases by using \list+ command

The below example shows that lists the databases by using \list+ command.

\list+

output 5

5. List the databases by using the shell prompt

The below example shows that lists the databases by using a shell prompt. In the below example, we have listed all the databases using the shell prompt command as \l+.

psql -U postgres -d postgres -c "\l+"

output 6

6. List the databases by using the pg_database catalog table

The example below shows that list of the databases using the pg_database catalog table.

select * from pg_database;

output 7

7. List the specific number of databases by using limit

The example below shows that lists the specific number of databases using limits.

select * from pg_database limit 3;
select * from pg_database limit 5;

output 8

8. Display only a list of the database name

The example below shows only a list of database names on the server.

select datname from pg_database;

output 9

Recommended Articles

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

  1. PostgreSQL enum
  2. Lock Table in PostgreSQL
  3. PostgreSQL EXCLUDE
  4. PostgreSQL Float

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