10+ Essential PostgreSQL Commands
The table below contains the essential commands for PostgreSQL. These commands will help you easily manage your PostgreSQL databases.
| Function | Command Syntax | Comments |
|---|---|---|
| Connect to a database under a specific user | psql -d database -U user -W |
Replace database and user |
| Connect to a database on another hots | psql -h host -d database -U user -W |
Replace the host, database and user |
| Switch connection | \c dbname username |
Replace with the name of the database & the username. If the username is left empty, it will automatically connect with current user |
| List available databases | \l |
|
| List available schemas | \dn |
|
| List available tables | \dt |
To list tables from all schemas, add *.*. To list from a specific schema, add schema_name.*(ex. \dt information_schema.* |
| Describe a table | \d table_name |
|
| List available views | \dv |
|
| List indexes | \di |
|
| List data types | \dT+ |
|
| List users | \du |
This command will also list the roles of the users |
| Access command history | \s |
To save the command history to a file, specify the filename after the command |
| Get help on commands | \? |
This will show a list with all the commands available in PostgreSQL. |
| Get help on a specific statement | \h statement |
This will show you details about a specific PostgreSQL statement. (ex. \h CREATE TABLE) |
| Quit | \q |