Create a gist now

Instantly share code, notes, and snippets.

Embed
PostgreSQL command line cheatsheet

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition including triggers
  • \l: List databases
  • \dy: List events
  • \df: List functions
  • \di: List indexes
  • \dn: List schemas
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \dv: List views
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables
  • \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV

User Related:

  • \du: List users
  • \du __username__: List a username if present.
  • create role __test1__: Create a role with an existing username.
  • create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
  • set role __test__;: Change role for current session to __test__.
  • grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.

Configuration

  • Service management commands:
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
  • Changing verbosity & querying Postgres log:
    1) First edit the config file, set a decent verbosity, save and restart postgres:
sudo vim /etc/postgresql/9.3/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart
  1. Now you will get tons of details of every statement, error, and even background tasks like VACUUMs
tail -f /var/log/postgresql/postgresql-9.3-main.log
  1. How to add user who executed a PG statement to log (editing postgresql.conf):
log_line_prefix = '%t %u %d %a '

Create command

There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... Parameters differ but can be checked at the official documentation.

Handy queries

  • SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function
  • SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition)
  • SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use
  • SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use
  • show statement_timeout;: Show current user's statement timeout
  • SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Show table indexes
  • Get all indexes from all tables of a schema:
SELECT
   t.relname AS table_name,
   i.relname AS index_name,
   a.attname AS column_name
FROM
   pg_class t,
   pg_class i,
   pg_index ix,
   pg_attribute a,
    pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)
   AND t.relnamespace = n.oid
    AND n.nspname = 'kartones'
ORDER BY
   t.relname,
   i.relname
  • Execution data:
    • Queries being executed at a certain DB:
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='__database_name__';
  • Get all queries from all dbs waiting for data (might be hung):
SELECT * FROM pg_stat_activity WHERE waiting='t'
  • Currently running queries with process pid:
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, 
  pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Casting:

  • CAST (column AS type) or column::type
  • '__table_name__'::regclass::oid: Get oid having a table name

Query analysis:

  • EXPLAIN __query__: see the query plan for the given query
  • EXPLAIN ANALYZE __query__: see and execute the query plan for the given query
  • ANALYZE [__table__]: collect statistics

Tools

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc
  • PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way. Highly recommended.
  • A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup.
@samayo

This comment has been minimized.

Show comment
Hide comment

samayo commented Apr 27, 2015

moaaar

@fiunchinho

This comment has been minimized.

Show comment
Hide comment
@fiunchinho

fiunchinho Mar 2, 2016

Nice gist! I'd add this trick to have unix-like reverse search in psql

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc

Nice gist! I'd add this trick to have unix-like reverse search in psql

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc
@Kartones

This comment has been minimized.

Show comment
Hide comment
@Kartones

Kartones Mar 2, 2016

Many thanks for the addition @fiunchinho! And glad that is helpful 💃

Owner

Kartones commented Mar 2, 2016

Many thanks for the addition @fiunchinho! And glad that is helpful 💃

@H4medRostami

This comment has been minimized.

Show comment
Hide comment

goood

@colbygk

This comment has been minimized.

Show comment
Hide comment
@colbygk

colbygk Aug 2, 2016

User related:

  • \du: List users
  • \du username: list only username
  • create role test1: Create a role with username test1.
  • create role test2 noinherit login password 'TooManySecrets';: Create a role with username test2 and a password.
  • set role test;: Change role for this session to test1.
  • grant test2 to test1;: Allow test1 to set its role as test2.

colbygk commented Aug 2, 2016

User related:

  • \du: List users
  • \du username: list only username
  • create role test1: Create a role with username test1.
  • create role test2 noinherit login password 'TooManySecrets';: Create a role with username test2 and a password.
  • set role test;: Change role for this session to test1.
  • grant test2 to test1;: Allow test1 to set its role as test2.
@Kartones

This comment has been minimized.

Show comment
Hide comment
@Kartones

Kartones Aug 27, 2016

Thanks @colbygk, added them!

Owner

Kartones commented Aug 27, 2016

Thanks @colbygk, added them!

@gardenunez

This comment has been minimized.

Show comment
Hide comment
@gardenunez

gardenunez Oct 26, 2016

This is great! Here are my two cents.
Performance basics:

  • EXPLAIN query: see the query plan for the given query
  • EXPLAIN ANALYZE query: see and execute the query plan for the given query
  • ANALYZE [table]: collect statistics

This is great! Here are my two cents.
Performance basics:

  • EXPLAIN query: see the query plan for the given query
  • EXPLAIN ANALYZE query: see and execute the query plan for the given query
  • ANALYZE [table]: collect statistics
@morteza-mori

This comment has been minimized.

Show comment
Hide comment

thank you

@Dr-Denzy

This comment has been minimized.

Show comment
Hide comment
@Dr-Denzy

Dr-Denzy Aug 24, 2017

Gut Gemacht!

Gut Gemacht!

@Mulperi

This comment has been minimized.

Show comment
Hide comment
@Mulperi

Mulperi Oct 12, 2017

How about create database?

Mulperi commented Oct 12, 2017

How about create database?

@Kartones

This comment has been minimized.

Show comment
Hide comment
@Kartones

Kartones Oct 13, 2017

@Mulperi to be sincere I don't know any tricks regarding create database... I usually create them from tools (PGAdmin, AWS console, etc.), and for tests and the like just import a SQL dump. Sorry!

Owner

Kartones commented Oct 13, 2017

@Mulperi to be sincere I don't know any tricks regarding create database... I usually create them from tools (PGAdmin, AWS console, etc.), and for tests and the like just import a SQL dump. Sorry!

@codeninja55

This comment has been minimized.

Show comment
Hide comment
@codeninja55

codeninja55 Nov 26, 2017

This is awesome. Thank you from a new user of Postgres

This is awesome. Thank you from a new user of Postgres

@frousterz

This comment has been minimized.

Show comment
Hide comment
@frousterz

frousterz Dec 8, 2017

Awesome! Thanks @Kartones

Awesome! Thanks @Kartones

@AL6981

This comment has been minimized.

Show comment
Hide comment
@AL6981

AL6981 Jan 7, 2018

Super helpful doc. Thanks!

AL6981 commented Jan 7, 2018

Super helpful doc. Thanks!

@riyadhalnur

This comment has been minimized.

Show comment
Hide comment
@riyadhalnur

riyadhalnur Jan 25, 2018

\di to list created indexes

\di to list created indexes

@Kartones

This comment has been minimized.

Show comment
Hide comment
Owner

Kartones commented Jan 25, 2018

Added, thanks @riyadhalnur

@kojikawamura

This comment has been minimized.

Show comment
Hide comment
@kojikawamura

kojikawamura Feb 9, 2018

To create a database
CREATE DATABASE new_database

To create a database
CREATE DATABASE new_database

@gsondur

This comment has been minimized.

Show comment
Hide comment
@gsondur

gsondur Feb 22, 2018

This has been super helpful! Thank you!

\copy to output csv format
source: https://stackoverflow.com/a/1517692

gsondur commented Feb 22, 2018

This has been super helpful! Thank you!

\copy to output csv format
source: https://stackoverflow.com/a/1517692

@Kartones

This comment has been minimized.

Show comment
Hide comment
@Kartones

Kartones Feb 25, 2018

Thanks for contributing @kojikawamura and @gsondur, added your suggestions 😃

Owner

Kartones commented Feb 25, 2018

Thanks for contributing @kojikawamura and @gsondur, added your suggestions 😃

@SKpracownia1

This comment has been minimized.

Show comment
Hide comment
@SKpracownia1

SKpracownia1 Apr 29, 2018

Just what i've been looking for. Thank you sir.

\dy to list triggers

SKpracownia1 commented Apr 29, 2018

Just what i've been looking for. Thank you sir.

\dy to list triggers

@danielmontenegro

This comment has been minimized.

Show comment
Hide comment
@danielmontenegro

danielmontenegro May 29, 2018

Thanks for sharing! Just wanted to say I can use ctrl + r to reverse-i-search on PostgreSQL 9.6.

Thanks for sharing! Just wanted to say I can use ctrl + r to reverse-i-search on PostgreSQL 9.6.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment