Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Postgres Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

psql client options

-l
List all available databases
 
-d __data­base__
Connect to database
 
-U __user__
Connect with user
 
-W __password
Connect with password
 
-h __host­name__
Connect to host
 
-p __port__
Connect via port
 
-c '__com­mand'
Run command
 
-f
Run file
 
-L __logf­ile__
Output query output to log file and stdout
 
-o __file__
Output query output to log file
 
-t
Print tuples only (no header­s/f­ooters)

psql commands

Command overview
 
\?
Exit
 
\q
Connect to database
 
\c __data­base__
Show table definition
 
\d __table__
List tables
 
\dt
List databases
 
\l
List schemas
 
\dn
List functions
 
\df
List views
 
\dv
Show function SQL code
 
\df+ __func­tion__
Pretty­-format query results
 
\x

Query analysis

See the query plan for the given query
EXPLAIN __query__
See and execute the query plan for the given query
EXPLAIN ANALYZE __query__
Collect statistics
ANALYZE [__tab­le__]
Get statistics for table
SELECT * from pg_sta­t_u­ser­_tables where relnam­e=_­_ta­ble__
Get statistics for table in current transa­ction
SELECT * from pg_sta­t_x­act­_us­er_­tables where relnam­e=_­_ta­ble__

Refere­nce­s/C­redits

DDL

Create database
 
CREATE DATABASE <da­tab­ase­_na­me> WITH OWNER <us­ern­ame­>;
Delete database
 
DROP DATABASE IF EXISTS <da­tab­ase­_na­me>;
Rename database
 
ALTER DATABASE <ol­d_n­ame> RENAME TO <ne­w_n­ame­>;
Create table
 
CREATE TABLE <table_name>(
  <column_name> SERIAL PRIMARY KEY,
  <column_name> <co­lum­n_t­ype> );
Delete table
 
ROP TABLE IF EXISTS <ta­ble­_na­me> CASCADE;
Add column
 
ALTER TABLE <ta­ble­_na­me> IF EXISTS
  ADD <co­lum­n_n­ame> <da­ta_­typ­e> [<c­ons­tra­int­s>];
Alter column
 
ALTER TABLE <ta­ble­_na­me> IF EXISTS
  ALTER <co­lum­n_n­ame> TYPE <da­ta_­typ­e> [<c­ons­tra­int­s>];
Delete column
 
ALTER TABLE <ta­ble­_na­me> IF EXISTS
  DROP <co­lum­n_n­ame­>;

Constr­aints

Add primary key
ALTER TABLE __table__
  ADD CONSTRAINT __cons­tra­int­name__
  PRIMARY KEY(__­col__, __col__);
Add unique
ALTER TABLE __table__
  ADD CONSTRAINT __cons­tra­int­name__
  UNIQUE(__col__, __col__);
Add foreign key
ALTER TABLE __table__
  ADD CONSTRAINT __cons­tra­int­name__
  FOREIGN KEY(__­col__, __col__)
  REFERENCES __tabl­e__­(__­col__, __col__);
Add check
ALTER TABLE __table__
  ADD CONSTRAINT __cons­tra­int­name__
  CHECK(__condition__);
Drop
ALTER TABLE __table__
  DROP CONSTRAINT __cons­tra­int__;
 

Handy queries

List proced­ure­/fu­nction
 
SELECT * FROM pg_proc
 WHERE pronam­e='­__p­roc­edu­ren­ame__'
Show DB table space in use
 
SELECT pg_siz­e_p­ret­ty(­pg_­tot­al_­rel­ati­on_­siz­e('­__t­abl­e_n­ame­__'));
Show DB space in use
 
SELECT pg_siz­e_p­ret­ty(­pg_­dat­aba­se_­siz­e('­__d­ata­bas­e_n­ame­__'));
Show current user's statement timeout
 
show statem­ent­_ti­meout;
Queries being executed
 
SELECT
   datname,
   application_name,
   pid,
   backend_start,
   query_start,
   state_change,
   state,
   query
 FROM
   pg_stat_activity
 WHERE
  ­ ­dat­nam­e='­__d­ata­bas­e_n­ame__';
Get all queries from all dbs waiting for data
 
SELECT * FROM pg_sta­t_a­ctivity WHERE waitin­g='t';
Currently running queries with process pid
 
SELECT
  ­ ­pg_­sta­t_g­et_­bac­ken­d_p­id(­s.b­ack­endid) AS procpid,
  ­ ­pg_­sta­t_g­et_­bac­ken­d_a­cti­vit­y(s.ba­cke­ndid) AS current_query
 FROM
  ­ ­(SELECT pg_sta­t_g­et_­bac­ken­d_i­dset() AS backendid) AS s;

Users

Add user
ADD USER __user__;
Drop user
DROP USER __user__;
Add user with password
ADD USER __user__
WITH PASSWORD __pass­word__;
Grant permis­sions for table
GRANT INSERT, UPDATE, DELETE
ON TABLE __table__ TO __user__;
Grant permis­sions for all tables
GRANT INSERT, UPDATE, DELETE
ON ALL TABLES TO __user__;
Grant permis­sions for sequence
GRANT USAGE
ON SEQUENCE __sequ­ence__ TO __user__;
Grant permis­sions for all sequences
GRANT USAGE
ON ALL SEQUENCES TO __user__;
Grant permis­sions for function
GRANT EXECUTE
ON FUNCTION __func­tion__ TO __user__;
Grant permis­sions for all functions
GRANT EXECUTE
ON ALL FUNCTIONS TO __user__;

Common Functions

Current date
curren­t_date
Current time
curren­t_time
Current timestamp
localt­ime­stamp
Get date part of column
date(_­_col__)
Get time part of column
__col_­_::time
Add interval to timestamp
__col__ + interval '1 hour'
Return serial number
INSERT ... RETURNING LASTVAL();
Condit­ional expres­sions
CASE expression
  WHEN value THEN result
  [WHEN ...]
  [ELSE result]
END
Null values
COALES­CE(­__v­alue__,
  __default1__, __defa­ult2__)
Cast
__valu­e__­::_­_type__
Execute function returning value
SELECT __func­tion__;
Execute function returning table
SELECT * FROM __func­tion__;