PostgreSQL: Top PSQL 5 Commands for 90% of the time
The following command is what I’ve been using 90% of the time.
This is specific to psql command (for PostgreSQL)
You will usually encounter when you’re
- Fixing something over SSH
- Quickly managing something in the terminal
- Writing the script
1. Navigate to the databases
List all databases
\lChange database
equivalent to SQL USE <database_name>
\c <database_name>Bonus: select schema
\dn - list schemas
set search_path to <schema_name>
show search_path
\lChange database
equivalent to SQL USE <database_name>
\c <database_name>Bonus: select schema
\dn - list schemas
set search_path to <schema_name>
show search_path
2. Seeing stuff in the databases
\dt -- list tables
\dn -- list schema
\du -- list rolesDescribe table
\d <table_name>
\dn -- list schema
\du -- list rolesDescribe table
\d <table_name>
3. Seeing stuff in the table
TABLE <table_name>;
This is equivalent to SELECT * FROM <table_name>;
4. Good output
For long input, page one-by-one with
\x auto
Non formatted dump the output
This is usually for copying purpose
\x off
\pset format wrapped
\pset pager off
do not use 'less' to view result just print out
\pset format wrapped
\pset pager off
do not use 'less' to view result just print out
5. Clear the screen
\! clear
Bonus: getting help
\?
Hope this helps.
Cheers!
