psql Commands - PostgreSQL Commands Cheat Sheet

The PostgreSQL cheat sheet page provides you with the common PostgreSQL commands and statements that enable you 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],...;

Comments