Postgres cheatsheet

PostgreSQL commands

psql -U [username];

Access the PostgreSQL server from psql with a specific user

\c database_name;

Connect to a specific database

\q

Quit the psql

\l

List all databases in the PostgreSQL database server

\dn

List all schemas

\df

List all stored procedures and functions

\dv

List all views

\dt

Lists all tables in the current database

\dt+

Get more information on tables in the current database

\d+ table_name

Get detailed information on a table

\df+ function_name

Show a stored procedure or function code

\x

Show query output in the pretty-format

\du

List all users

CREATE ROLE role_name;

Create a new role

CREATE ROLE username NOINHERIT LOGIN PASSWORD password;

Create a new role with a username and password

SET ROLE new_role;

Change role to the new_role for the current session

GRANT role_2 TO role_1;

Allow role_1 to set its role as role_2

Managing databases

CREATE DATABASE [IF NOT EXISTS] db_name;

Create a new database

DROP DATABASE [IF EXISTS] db_name;

Delete a database permanently

Managing tables

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

Create a new table or a temporary table

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

Add a new column to a table

ALTER TABLE table_name DROP COLUMN column_name;

Drop a column in a table

ALTER TABLE table_name RENAME column_name TO new_column_name;

Rename a column

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

Set or remove a default value for a column

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

Add a primary key to a table

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;

Remove the primary key from a table

ALTER TABLE table_name RENAME TO new_table_name;

Rename a table

DROP TABLE [IF EXISTS] table_name CASCADE;

Drop a table and its dependent objects

Managing views

CREATE OR REPLACE view_name AS
query;

Create a view

CREATE RECURSIVE VIEW view_name(column_list) AS
SELECT column_list;

Create a recursive view

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

Create a materialized view

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Refresh a materialized view

DROP VIEW [ IF EXISTS ] view_name;

Drop a view

DROP MATERIALIZED VIEW view_name;

Drop a materialized view

ALTER VIEW view_name RENAME TO new_name;

Rename a view

Managing indexes

CREATE [UNIQUE] INDEX index_name
ON table (column,...)

Create an index with the specified name on a table

DROP INDEX index_name;

Remove a specified index from a table

Querying data from tables

SELECT * FROM table_name;

Query all data from a table

SELECT column_list
FROM table;

Query data from the specified columns of all rows in a table

SELECT DISTINCT (column)
FROM table;

Query data and select only unique rows

SELECT *
FROM table
WHERE condition;

Query data from a table with a filter

SELECT column_1 AS new_column_1, ...
FROM table;

Assign an alias to a column in the result set

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

Query data using the LIKE operator

SELECT * FROM table_name
WHERE column BETWEEN low AND high;

Query data using the BETWEEN operator

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

Query data using the IN operator

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

Constrain the returned rows with the LIMIT clause

SELECT *
FROM table1
INNER JOIN table2 ON conditions

Query data from multiple tables using the inner join

SELECT COUNT (*)
FROM table_name;

Return the number of table rows

SELECT select_list
FROM table;

Sort rows in ascending or descending order

SELECT *
FROM table
GROUP BY column_1, column_2, ...;

Group rows using the GROUP BY clause

SELECT *
FROM table
GROUP BY column_1
HAVING condition;

Filter groups using the HAVING clause

Set operations

SELECT * FROM table1
UNION
SELECT * FROM table2;

Combine the result set of two or more queries with the UNION operator

SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

Minus a result set using the EXCEPT operator

SELECT * FROM table1
INTERSECT
SELECT * FROM table2;

Get intersection of the result sets of two queries

Modifying data

INSERT INTO table(column1,column2,...)
VALUES(value_1,value_2,...);

Insert a new row into a table

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...),
(value_1,value_2,...),
(value_1,value_2,...)...

Insert multiple rows into a table

UPDATE table_name
SET column_1 = value_1,
...;

Update data for all rows

UPDATE table
SET column_1 = value_1,
...
WHERE condition;

Update data for the set of rows specified by the condition in the WHERE clause

DELETE FROM table_name;

Delete all rows of a table

DELETE FROM table_name
WHERE condition;

Delete specific rows based on a condition

Performance

EXPLAIN query;

Show the query plan for a query

EXPLAIN ANALYZE query;

Show and execute the query plan for a query

ANALYZE table_name;

Collect statistics

Found a mistake? Seleсt text and press Ctrl+Enter to report it