Psql

Psql is a PostgreSQL command line client.

Psql by examples

Psql list all databases

$ psql -U postgres
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 bar       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 foo       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres

Psql use/set a database

$ psql -U postgres
postgres=# \c foo
You are now connected to database "foo" as user "postgres".
foo=#

Psql describe table

$ psql -U postgres
postgres=# \d table_name

Psql list tables

Lists all tables of public schema:

$ psql -U postgres
postgres=# \du

Lists all tables of all schemas:

$ psql -U postgres
postgres=# \du *.*

Lists all tables, views and materialized views of public schema:

$ psql -U postgres
postgres=# \dmvt

Psql list users

$ psql -U postgres
postgres=# \du

Psql exit

$ psql -U postgres
postgres=# \q

In versions of PostgreSQL >= 11:

$ psql -U postgres
postgres=# exit

Psql PostgreSQL version

$ psql -U postgres --version
psql (PostgreSQL) 11.6 (Debian 11.6-1.pgdg90+1)

Recommended books to expand your PostgreSQL knowledge:

Psql posts


Loading Comments

DevOps books:









Cloud providers:



DigitalOcean offers affordable pricing for VMs and many other public cloud services. You can sign up for DigitalOcean and receive a $100 free credit using this referral link.