Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \l: List databases
  • \dn: List schemas
  • \df: List functions
  • \dv: List views
  • \di: List indexes
  • \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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.