All
ADH
ADPG
ADBSC
ADB
ADS
ADQM
ADCM
Showing 5 of 16 results. See all results...

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