PostgreSQL command line cheatsheet
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 CSVUser 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__
.sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
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
tail -f /var/log/postgresql/postgresql-9.3-main.log
postgresql.conf
):log_line_prefix = '%t %u %d %a '
There are many CREATE
choices, like CREATE DATABASE __database_name__
, CREATE TABLE __table_name__
... Parameters differ but can be checked at the official documentation.
SELECT * FROM pg_proc WHERE proname='__procedurename__'
: List procedure/functionSELECT * 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 useSELECT pg_size_pretty(pg_database_size('__database_name__'));
: Show DB space in useshow statement_timeout;
: Show current user's statement timeoutSELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';
: Show table indexesSELECT
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
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
FROM pg_stat_activity
WHERE datname='__database_name__';
SELECT * FROM pg_stat_activity WHERE waiting='t'
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 nameQuery analysis:
EXPLAIN __query__
: see the query plan for the given queryEXPLAIN ANALYZE __query__
: see and execute the query plan for the given queryANALYZE [__table__]
: collect statisticstop
for PG. sudo apt-get install ptop
+ pg_top
$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc