List Databases
The commands to list databases and tables within the postgresql command line
We can't find the internet
Attempting to reconnect
Something went wrong!
Hang in there while we get back on track
These are just shortcut reminders for the commands.
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
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=#
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 |
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 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 | {}