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.