Psql — Postgresql cheat sheet
Psql is an interactive terminal-based postgresql client. It’s an important part of Postgresql tooling and so comes with postgresql installation by default. In this article I will list out the most commonly used psql command that are essential for anyone to manage their PostgreSQL server via psql.
Note: Text that is Bold and italic denotes user-input values. Also, I’d like to suggest writing SQL keywords in CAPITAL form.
PostgreSQL Server Login, Check Connection Status and Getting Help
psql -U postgres -h localhost -p 5432 [-d database_name]
#prompts for password, the password of 'postgres' user is 'postgres' by default
Now the most useful commands inside psql console are :
\q
Quit psql console
\?
Display list of all psql commands
\conninfo
Gives information on current database connection
\c my_db my_db_user host.com 5432
Connect to database ‘my_db’ as ‘my_db_user’ to database hosted in ‘host.com’ at port ‘5432’
\h CREATE DATABASE
Get help on SQL commands, ‘create database’ in this case
Database Table and Schema Listing
\l
List all the databases, and their owners. Use \l+
for additional info.
\du
List all database users
\dt
Display all tables in the database, use \dt+
for extra info
\d
Display all tables and sequences in the database, use \d+
for extra info
\d Employee
Describe a table ‘Employee’, use \d+ Employee
for extra info
\dn
List all schema in the database, use \dn+
for extra info
\z
List all tables, sequences and views in the database
\di
List indexes
\dv
List views
Extras
\! ls
Execute command in shell, ‘ls’ in this case
\set foo bar
& \echo :foo
Setting and retrieving variables and their values
Query Optimization and Profiling
\timing [on|off]
Use timing to find out query execution time (in ms) of subsequent queries
\r
Clears the query buffer, useful when profiling queries.
\x
Pretty-print query results in console
SELECT * FROM table_name;
Query the database and print in console
\copy (SELECT * FROM table_name) to '/path/to/file.csv' with csv;
Export select query results to csv file
EXPLAIN SELECT * FROM table_name;
Get details on query planning and execution.
EXPLAIN ANALYZE SELECT * FROM table_name;
Additional info on query planning and execution
These are my most used psql commands whenever I have to take a quick look into Postgres database. I will add a postgresql user management guide as a continuation to this article.
Was this cheat sheet helpful ? Please leave feedback in the comments section.