PostgreSQL is a powerful open-source relational database management system, known for its robustness and extensibility. As a database administrator or developer, one of the fundamental tasks you’ll often need to perform is listing databases and tables within PostgreSQL. In this step-by-step tutorial, we will walk you through the process of listing databases and tables using various methods and PostgreSQL commands.
Listing Databases in PostgreSQL
- Open your terminal or command prompt.
- To access the PostgreSQL command-line interface (psql), type the following command, replacing your_username with your actual PostgreSQL username:
psql -U your_username
You will be prompted to enter your password. Once authenticated, you will enter the PostgreSQL interactive shell.
- To list all the databases in PostgreSQL, use the following SQL command:
\l
Output:List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | en_IN | en_IN | myDatabase| tecadmin | UTF8 | en_IN | en_IN | =CTc/tecadmin + | | | | | tecadmin=CTc/tecadmin template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) - Alternatively, you can use the following SQL query:
SELECT datname FROM pg_database;
Listing Tables in a PostgreSQL Database
Now, let’s see how to list the tables within a specific database.
- After connecting to psql as described in the previous section, select a specific database using the following command, replacing your_database_name with the name of the database you want to explore:
\c your_database_name
- To list all the tables in the current database, use the following SQL command:
\dt
Output:List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | results | table | tecadmin public | roles | table | tecadmin public | employee | table | tecadmin public | users | table | tecadmin (4 rows) - Alternatively, you can query the information schema to get the list of tables:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Conclusion
In this step-by-step tutorial, we’ve shown you how to list databases and tables in PostgreSQL using both the command-line interface (psql). These fundamental tasks are essential for any PostgreSQL administrator or developer, as they provide insight into the structure of your PostgreSQL environment.