List Databases

postgres – Aug 27 2018

The commands to list databases and tables within the postgresql command line

• • •

These are just shortcut reminders for the commands.

Listing Databases

To view all the databases on the server you can use \list or \l

potgres=#\l
                                      List of databases
       Name        |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------------+----------+----------+-------------+-------------+-----------------------
 postgres          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                   |          |          |             |             | postgres=CTc/postgres
 template1         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                   |          |          |             |             | postgres=CTc/postgres

Switching Databases

In order to switch databases you can use the \c with a database name to connect to the database.

Most postgres servers have three databases defined by default: template0, template1 and postgres. template0 and template1 are skeleton databases that are or can be used by the CREATE DATABASE command. postgres is the default database you will connect to before you have created any other databases.

potgres=# \c exercises
You are now connected to database "exercises" as user "potgres".
exercises=#

Listing Tables

Once connected to a database table you will want to see the tables. This command \dt will list out the tables that are within the database that you are connected to.

exercises=# \dt
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | categories        | table | postgres
 public | item_tags         | table | postgres
 public | items             | table | postgres

NOTE: Adding `+` to get more information on tables in the current database

exercises=# \dt+

Schema |       Name        | Type  |  Owner   |    Size    | Description 
--------+-------------------+-------+----------+------------+-------------
 public | categories        | table | postgres | 16 kB      | 
 public | item_tags         | table | postgres | 16 kB      | 
 public | items             | table | postgres | 16 kB      | 

Details on a Table

To see the details on a single table \d <TABLE NAME>

exercises=# \d categories

                                       Table "public.categories"
   Column    |              Type              |                        Modifiers                        
-------------+--------------------------------+---------------------------------------------------------
 id          | bigint                         | not null default nextval('categories_id_seq'::regclass)
 description | character varying(255)         | 
 name        | character varying(255)         | not null
 inserted_at | timestamp(0) without time zone | not null
 updated_at  | timestamp(0) without time zone | not null
Indexes:
    "categories_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "items" CONSTRAINT "items_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)

List Users

List all users

exercises=# \du

List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | alen
Attributes | Superuser, Create role, Create DB
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}

To be continued

This blog post has reference material from source, source

Follow on Twitter
Follow on Github