Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Databases»PostgreSQL»How To List Databases and Tables in PostgreSQL

    How To List Databases and Tables in PostgreSQL

    RahulBy RahulJanuary 20, 20153 Mins ReadUpdated:July 30, 2021

    While working with PostgreSQL database servers, there is much useful application available for administering databases like pgAdmin4, phpPgAdmin, and SQL Workbench. A Postgres process contains multiple databases and is stored in a separate set of files under the server’s data directory.

    The Postgres server also comes with psql utility for database administration via command-line. This article will help you to how to list databases in PostgreSQL server along with all tables in a single database.

    Listing Databases in PostgreSQL

    First connect to PostgreSQL terminal with psql command as user “postgres”. The default installation doesn’t require any password until specified manually. You can execute sudo command as below to directly connect to psql terminal as postgres user:

    sudo -u postgres psql 
    

    Once you are connected to the psql terminal, type \l to list all available databases.

    postgres=# \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)

    You can also use \list to find similar results, but to find extended details use \l+\list+.

    postgres=# \list+
    

    With this command, you will find the additional details of the PostgreSQL database like size, tablespace, and description.

    The postgres also provides an traditional SQL like statement to show all databases in Postgres. To determine the set of existing databases, fetch records from the pg_database system catalog, for example:

    postgres=# SELECT datname FROM pg_database;
    
    Output:
    datname ----------- postgres template1 template0 myDatabase (4 rows)

    Listing Tables in Postgres Database

    To list tables of any database first you connect to that database and then view tables in that database. The first command will connect you with the database (example: myDatabase) to which you want to see tables:

    postgres=# \c myDatabase
    

    Once you’ve connected to a database, you will see the database name in the prompt. Then execute the \dt meta-command to list all the tables in current database.

    myDatabase=# \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)

    Conclusion

    In this quick guide, you have learned to list databases in the PostgreSQL database server. Also found instructions to connect the database and list available tables in the database.

    postgres PostgreSQL psql SQL
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous ArticleHow to Exclude Files from a Zip Archive
    Next Article How to Find PostgreSQL Database Size

    Related Posts

    How To Configure PostgreSQL to Allow Remote Connections

    3 Mins Read

    How to Check the PostgreSQL Version

    Updated:August 6, 20212 Mins Read

    How To Restore SQL Server Database

    Updated:July 12, 20213 Mins Read

    How to Install pgAdmin4 on Ubuntu 20.04

    Updated:April 9, 20214 Mins Read

    How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04

    Updated:July 1, 20215 Mins Read

    How to Use LIKE Statement in MongoDB

    Updated:May 28, 20213 Mins Read

    Leave A Reply Cancel Reply

    Recent Posts
    • How to Install JAVA on Ubuntu 22.04
    • Switching Display Manager in Ubuntu – GDM, LightDM & SDDM
    • Changing the Login Screen Background in Ubuntu 22.04 & 20.04
    • How To Install PHP (8.1, 7.4 or 5.6) on Ubuntu 22.04
    • (Resolved) Please install all available updates for your release before upgrading
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.