The PostgreSQL cheat sheet provides you with the common PostgreSQL commands and statements that enable you to work with PostgreSQL quickly and effectively.
Download PostgreSQL cheat sheet
We provides you with a 3-page PostgreSQL cheat sheet in PDF format. You can download and print it out for a quick reference of the most commonly used statements in PostgreSQL:
PostgreSQL commands
Access the PostgreSQL server from psql with a specific user:
1 | psql -U [username]; |
For example, the following command uses the postgres user to access the PostgreSQL databse server:
1 | psql -U postgres |
Connect to a specific database:
1 | \c database_name; |
For example, the following command connects to the dvdrental database:
1 2 | \c dvdrental; You are now connected to database "dvdrental" as user "postgres". |
To quite the psql:
1 | \q |
List all databases in the PostgreSQL database server
1 | \l |
List all schemas:
1 | \dn |
List all stored procedures and functions:
1 | \df |
List all views:
1 | \dv |
Lists all tables in a current database.
1 | \dt |
Or to get more information on tables in the current database:
1 | \dt+ |
Get detailed information on a table.
1 | \d+ table_name |
Show a stored procedure or function code:
1 | \df+ function_name |
Show query output in the pretty-format:
1 | \x |
List all users:
1 | \du |
Create a new role:
1 | CREATE ROLE role_name; |
Create a new role with a username and password:
1 | CREATE ROLE username NOINHERIT LOGIN PASSWORD password; |
Change role for current sesion to the new_role:
1 | SET ROLE new_role; |
Allow role_1 to set its role as role_2:
1 | GRANT role_2 TO role_1; |
Managing databases
1 | CREATE DATABASE [IF NOT EXISTS] db_name; |
Delete a database permanently:
1 | DROP DATABASE [IF EXISTS] db_name; |
Managing tables
Create a new table or a temporary table
1 2 3 4 5 6 | CREATE [TEMP] TABLE [IF NOT EXISTS] table_name( pk SERIAL PRIMARY KEY, c1 type(size) NOT NULL, c2 type(size) NULL, ... ); |
Add a new column into a table:
1 | ALTER TABLE table_name ADD COLUMN new_column_name TYPE; |
Drop a column in a table:
1 | ALTER TABLE table_name DROP COLUMN column_name; |
1 | ALTER TABLE table_name RENAME column_name TO new_column_name; |
Set or remove a default value for a column:
1 | ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT] |
Add a primary key to a table.
1 | ALTER TABLE table_name ADD PRIMARY KEY (column,...); |
Remove the primary key from a table.
1 2 | ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint_name; |
1 | ALTER TABLE table_name RENAME TO new_table_name; |
Drop a table and its dependent objects:
1 | DROP TABLE [IF EXISTS] table_name CASCADE; |
Manging views
1 2 | CREATE OR REPLACE view_name AS query; |
1 2 | CREATE RECURSIVE VIEW view_name(columns) AS SELECT columns; |
1 2 3 4 | CREATE MATERIALIZED VIEW view_name AS query WITH [NO] DATA; |
Refresh a materialized view:
1 | REFRESH MATERIALIZED VIEW CONCURRENTLY view_name; |
Drop a view:
1 | DROP VIEW [ IF EXISTS ] view_name; |
Drop a materialized view:
1 | DROP MATERIALIZED VIEW view_name; |
Rename a view:
1 | ALTER VIEW view_name RENAME TO new_name; |
Managing indexes
Creating an index with the specified name on a table
1 2 | CREATE [UNIQUE] INDEX index_name ON table (column,...) |
Removing a specified index from table
1 | DROP INDEX index_name; |
Querying data from tables
Query all data from a table:
1 | SELECT * FROM table_name; |
Query data from specified columns of all rows in a table:
1 2 | SELECT column, column2…. FROM table; |
Query data and select only unique rows:
1 2 | SELECT DISTINCT (column) FROM table; |
Query data from a table with a filter:
1 2 3 | SELECT * FROM table WHERE condition; |
Set an alias for a column in the result set:
1 2 | SELECT column_1 AS new_column_1, ... FROM table; |
Query data using the LIKE operator:
1 2 | SELECT * FROM table_name WHERE column LIKE '%value%' |
Query data using the BETWEEN operator:
1 2 | SELECT * FROM table_name WHERE column BETWEEN low AND high; |
Query data using the IN operator:
1 2 | SELECT * FROM table_name WHERE column IN (value1, value2,...); |
Constrain the returned rows with LIMIT clause:
1 2 3 | 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:
1 2 3 | SELECT * FROM table1 INNER JOIN table2 ON conditions |
1 2 3 | SELECT * FROM table1 LEFT JOIN table2 ON conditions |
1 2 3 | SELECT * FROM table1 FULL OUTER JOIN table2 ON conditions |
1 2 3 | SELECT * FROM table1 CROSS JOIN table2; |
1 2 3 | SELECT * FROM table1 NATURAL JOIN table2; |
Return the number of rows of a table.
1 2 | SELECT COUNT (*) FROM table_name; |
Sort rows in ascending or descending order
1 2 3 | SELECT column, column2, ... FROM table ORDER BY column ASC [DESC], column2 ASC [DESC],...; |
Group rows using GROUP BY clause.
1 2 3 | SELECT * FROM table GROUP BY column_1, column_2, ...; |
Filter groups using the HAVING clause.
1 2 3 4 | SELECT * FROM table GROUP BY column_1 HAVING condition; |
Set operations
Combine the result set of two or more queries with UNION operator:
1 2 3 | SELECT * FROM table1 UNION SELECT * FROM table2; |
Minus a result set using EXCEPT operator:
1 2 3 | SELECT * FROM table1 EXCEPT SELECT * FROM table2; |
Get intersection of the result sets of two queries:
1 2 3 | SELECT * FROM table1 INTERSECT SELECT * FROM table2; |
Modifying data
Insert a new row into a table:
1 2 | INSERT INTO table(column1,column2,...) VALUES(value_1,value_2,...); |
Insert multiple rows into a table:
1 2 3 4 | INSERT INTO table_name(column1,column2,...) VALUES(value_1,value_2,...), (value_1,value_2,...), (value_1,value_2,...)... |
Update data for all rows:
1 2 3 | UPDATE table_name SET column_1 = value_1, ...; |
Update data for a set of rows specified by a condition in WHERE clause.
1 2 3 4 | UPDATE table SET column_1 = value_1, ... WHERE condition; |
Delete all rows of a table:
1 | DELETE FROM table_name; |
Delete specific rows based on a condition:
1 2 | DELETE FROM table_name WHERE condition; |
Performance
Show the query plan for a query:
1 | EXPLAIN query; |
Show and execute the query plan for a query:
1 | EXPLAIN ANALYZE query; |
Collect statistics:
1 | ANALYZE table_name; |