Introduction to PostgreSQL List Databases
PostgreSQL list databases are defined as list all the databases from the database server; we can list the database by using the pg_database catalog table. We can also see the list of the databases by using the \l command; also, we can see the more description of the database by using the \l+ command in PostgreSQL. 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.
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 is used to list all databases with information of database name, owner of the database, encoding, collate, ctype, and access privileges of the database.
- \l+ – This command is used to list all databases with information of 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 of 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 of 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 is used to select a column from the pg_database table to list the databases. We can select specific or all column from the table to see the list of databases in PostgreSQL.
- Name of the column –This is defined as the name of the column for the table of pg_database. We can select a single or all column which was we want to display in the output.
- Pg_database –This is a catalog table of PostgreSQL that contains the information of all the database created by the user and contains the information of system databases.
How to list databases in PostgreSQL?
- We can list the databases are 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 database for user in PostgreSQL.
- In the above first example, we have checked the privileges of the user; the db_test user doesn’t have any privileges on the database server.
- In the second example, we use db_test user to list databases; using 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
- Basically, we have using the below command to list all the databases is 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 as well as user-created databases.
- We can list the database by using shell prompt as well as using database shell prompt in PostgreSQL.
- We can also use limited options to list the database by using the pg_database catalog table. Pg_database catalog table is very important to fetch 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
2. List the databases by using the \l+ command
The below example shows that list the databases by using the \l+ command.
\l+
3. List the databases by using \list command
The below example shows that list the databases by using \list command.
\list
4. List the databases by using \list+ command
The below example shows that list the databases by using \list+ command.
\list+
5. List the databases by using shell prompt
The below example shows that list the databases by using shell prompt. In the below example, we have listed all the databases by using the shell prompt using the command as \l+.
psql -U postgres -d postgres -c "\l+"
6. List the databases by using the pg_database catalog table
The below example shows that list the databases by using the pg_database catalog table.
select * from pg_database;
7. List the specific number of databases by using limit
The below example shows that list the specific number of databases by using limits.
select * from pg_database limit 3;
select * from pg_database limit 5;
8. Display only a list of the database name
The below example shows display only a list of database names present in the database server.
select datname from pg_database;
Recommended Articles
This is a guide to PostgreSQL List Databases. Here we discuss How to list databases in PostgreSQL along with the examples. You may also have a look at the following articles to learn more –
