PostgreSQL command line cheatsheet

postgres-cheatsheet.md

PostgreSQL

Links

• Tutorial
• Docs

Selected Commands

Command	Description	Link
CREATE DATABASE "database";	Creates a database.
createdb "database"	psql command to create a database.
DROP DATABASE IF EXISTS "database";	Destroys a database. 'IF EXISTS' checks for existence first.
dropdb "database"	psql command to create a database.
CREATE TABLE . . .	Creates a table.	https://www.postgresql.org/docs/9.5/static/tutorial-table.html
DROP TABLE IF EXISTS tablename;	Destroys a table. 'IF EXISTS' checks for existence first.
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');	Adds a record.
COPY weather FROM '/home/user/weather.txt';	Copies data from a text file to the database.
UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';	Update existing rows.	https://www.postgresql.org/docs/9.5/static/tutorial-update.html
DELETE FROM weather WHERE city = 'Hayward';	Remove record(s).	https://www.postgresql.org/docs/9.5/static/tutorial-delete.html
ALTER SEQUENCE books_id_seq RESTART WITH 1;	Restart SERIAL index at 1

Magic words:

psql -U postgres

If run with -E flag, it will describe the underlaying queries of the \ commands (cool for learning!).

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

• \h: Get help
• \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
• \df+ __function__ : Show function SQL code.
• \x: Pretty-format query results instead of the not-so-useful ASCII tables
• \?: List commands

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

2. 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

3. How to add user who executed a PG statement to log (editing postgresql.conf):

log_line_prefix = '%t %u %d %a '

Query basics

• SELECT DISTINCT gets unique values, ORDER BY sorts the data

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

Types of joins:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in EITHER ONE of the tables

If there is no join type stated, it's an inner join.

SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE weather.city = cities.name;

Aggregate functions

We can get min / max / average / etc.

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

We can filter aggregate functions with HAVING. (https://www.postgresql.org/docs/9.5/static/tutorial-agg.html)

The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'
    GROUP BY city
    HAVING max(temp_lo) < 40;

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 E.g.: SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
• '__table_name__'::regclass::oid: Get oid having a table name

Tools

• pg-top: top for PG. sudo apt-get install ptop + pg_top
• Unix-like reverse search in psql:

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