psql Commands - PostgreSQL Commands Cheat Sheet

This page gives you all the commonly used PostgreSQL commands and statements to work with PostgreSQL quickly and effectively.

PostgreSQL commands

1. Connect to a specific database

\c database_name

For example, the following command connects to the "employees" database:

mydb=# \c employees
You are now connected to database "employees" as user "postgres".
employees=#

2. To quit the psql shell

\q

3. List all databases in the PostgreSQL database server

\l

For example:

mydb=# \l
                                         List of databases
   Name    |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-----------+----------+----------+--------------------+--------------------+-----------------------
 employees | postgres | UTF8     | English_India.1252 | English_India.1252 |
 mydb      | postgres | UTF8     | English_India.1252 | English_India.1252 |
 postgres  | postgres | UTF8     | English_India.1252 | English_India.1252 |
 template0 | postgres | UTF8     | English_India.1252 | English_India.1252 | =c/postgres          +
           |          |          |                    |                    | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_India.1252 | English_India.1252 | =c/postgres          +
           |          |          |                    |                    | postgres=CTc/postgres
(5 rows)

4. List all schemas

\dn

Example:

employees=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

5. Lists all tables in a current database

\dt

Example:

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

6. Get detailed information on a table

\d+ table_name

Example:

mydb=# \d+ users
                                           Table "public.users"
  Column  |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id       | integer               |           | not null |         | plain    |              |
 name     | text                  |           | not null |         | extended |              |
 email    | character varying(50) |           |          |         | extended |              |
 country  | character varying(50) |           |          |         | extended |              |
 password | character varying(50) |           |          |         | extended |              |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

7. Show query output in the pretty-format

\x

Example: Execute the below command to expand query result:

mydb=# \x
Expanded display is on.
mydb=# select * from users;
-[ RECORD 1 ]--------------
id       | 1
name     | Tony
email    | tony@gmail.com
country  | US
password | secret
-[ RECORD 2 ]--------------
id       | 2
name     | Ramesh
email    | ramesh@gmail.com
country  | India
password | password123
-[ RECORD 3 ]--------------
id       | 3
name     | John
email    | john@gmail.com
country  | US
password | password123

Execute "\x" command to disable expand like:

mydb=# \x
Expanded display is off.
mydb=# select * from users;
 id |  name  |      email       | country |  password
----+--------+------------------+---------+-------------
  1 | Tony   | tony@gmail.com   | US      | secret
  2 | Ramesh | ramesh@gmail.com | India   | password123
  3 | John   | john@gmail.com   | US      | password123
(3 rows)

8. List all stored procedures and functions

\df

9. List all views

\dv

10. List all users

\du

Example:

mydb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Managing databases

1. Create a new database

CREATE DATABASE [IF NOT EXISTS] db_name;

2. Delete a database permanently

DROP DATABASE [IF EXISTS] db_name;

Managing tables

1. Create a new table or a temporary table

CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(
   pk SERIAL PRIMARY KEY,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   ...
);

2. Add a new column to a table

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

3. Drop a column in a table

ALTER TABLE table_name DROP COLUMN column_name;

4. Rename a column

ALTER TABLE table_name RENAME column_name TO new_column_name;

5. Set or remove a default value for a column:

ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]

6. Add a primary key to a table

ALTER TABLE table_name ADD PRIMARY KEY (column,...);

7. Remove the primary key from a table

ALTER TABLE table_name 
DROP CONSTRAINT primary_key_constraint_name;

8. Rename a table

ALTER TABLE table_name RENAME TO new_table_name;

Querying data from tables

1. Query all data from a table

SELECT * FROM table_name;

2. Query data from specified columns of all rows in a table:

SELECT column, column2….
FROM table;

3. Query data and select only unique rows:

SELECT DISTINCT (column)
FROM table;

4. Query data from a table with a filter

SELECT *
FROM table
WHERE condition;

5. Assign an alias to a column in the result set:

SELECT column_1 AS new_column_1, ...
FROM table;

5. Query data using the LIKE operator

SELECT * FROM table_name
WHERE column LIKE '%value%'

6. Query data using the BETWEEN operator

SELECT * FROM table_name
WHERE column BETWEEN low AND high;

7. Query data using the IN operator

SELECT * FROM table_name
WHERE column IN (value1, value2,...);

8. Constrain the returned rows with the LIMIT clause

SELECT * FROM table_name
LIMIT limit OFFSET offset
ORDER BY column_name;

Query data from multiple using the inner join, left join, full outer join, cross join and natural join:

INNER JOIN:

SELECT * 
FROM table1
INNER JOIN table2 ON conditions

LEFT JOIN:

SELECT * 
FROM table1
LEFT JOIN table2 ON conditions

FULL OUTER JOIN:

SELECT * 
FROM table1
FULL OUTER JOIN table2 ON conditions

CROSS JOIN:

SELECT * 
FROM table1
CROSS JOIN table2;

NATURAL JOIN:

SELECT * 
FROM table1
NATURAL JOIN table2;

Return the number of rows of a table

SELECT COUNT (*)
FROM table_name;

Sort rows in ascending or descending order

SELECT column, column2, ...
FROM table
ORDER BY column ASC [DESC], column2 ASC [DESC],...;