PostgreSQL CLI (psql) Commands

In article Install PostgreSQL on WSL, I showed the steps to install PostgreSQL in WSL (Windows Subsystem for Linux). This page provides some of the commonly used commands in psql CLI.

Connect to psql

The following commands connects to PostgreSQL server via psql command:

psql -U postgres -W -p 5432 -h localhost

Output should look like the following:

$ psql -U postgres -W -p 5432 -h localhost
Password for user postgres:
psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Show databases

\l

Example output:

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(4 rows)

Use database

Use the following command to change default database:

 \c dbname;

Example output:

postgres=# \c testdb
Password:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "testdb" as user "postgres".
testdb=#

List tables

To list tables in the database, run the following commands:

\dt

Example output:

testdb=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | test_table | table | postgres
(1 row)

Run select statement

To run SQL statements, simply type them and end with semi-comma(;).

select * from test_table;

Example output:

testdb=# select * from test_table;
 id | value
----+-------
  1 | ABC
  2 | DEF
(2 rows)

Quit psql CLI

To quit from the command line windows, type the following command:

\q