Show Menu

postgresql Cheat Sheet by

This is a postgresql cheat sheet.


list databases
list databases with more details
\c <db­_na­me>
Connects to a given database
gives help on creating a database
Creates a database called mytest


\d <TA­BLE­_NA­ME>
Shows inform­ation on the given table_name
\d+ <TA­BLE­_NA­ME>
Gives more detailed info on a given table
List tables from a given schema
\dt .
List tables from all schemas
\dt <na­me-­of-­sch­ema­>.*
List tables for a given schema
\copy (SELECT * FROM <my­_ta­ble­>) TO 'file_­pat­h_a­nd_­nam­e.csv' WITH CSV
Copies data from a table to a CSV file
ANALYZE [__tab­le__]
Shows statistics about a table
SELECT * FROM pg_indexes WHERE tablen­ame­='_­_ta­ble­_na­me__' AND schema­nam­e='­__s­che­ma_­nam­e__';
Show indexes on a table
Comment on table employee is 'Stores employee records';
Add a comment about a table
Comment on column employ­ee.ssn is 'Employee Social Security Number';
Add a comment about a column in a table

Command line

-d, --dbna­me=­DBNAME
database name to connect to (default: "­fam­ily­")
-f, --file­=FI­LENAME
execute commands from file, then exit
-c, --comm­and­=CO­MMAND
run only single command (SQL or internal) and exit
-h, --host­=HO­STNAME
database server host
-p, --port­=PORT
database server port (default: "­543­2")
-U, --user­nam­e=U­SERNAME
database user name (default: "­fam­ily­")
-H, --html
HTML table output mode


show all
Display config­uration parameters
select * from pg_set­tings;
Display config­uration parameters using sql
show config­_file;
Show location of config file

User Commands

\password <us­ern­ame>
Change the password for given username
select * from pg_user;
Show all users
DROP USER <us­ern­ame>
Removes a given user
CREATE USER name [ [ WITH ] option [ ... ] ]

Backup and Restore

$ pg_dump mydb > mydb.b­ak.sql

// Create a backup for a database “mydb” in plain-text SQL Script file (mydb.b­ak.sql) (pg_dump)

$ pg_dump -c -C -F p -f mydb.b­ak.sql mydb

// Creates a backup for a database “mydb” in plain text format with drop & create database commands included in output file mydb.b­ak.sql (pg_dump)

psql -U username -f filena­me.sql

// restores from a set of SQL commands as given above


No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          VBA for Excel Cheat Sheet
          Kitchen Reminders Cheat Sheet
          The Basics of Accounting Cheat Sheet