In some of the previous posts, I’ve written about How to Backup and Restore a PostgreSQL Database and How To Show Database and table size in PostgreSQL. These are pretty common, basic and practical commands too, so let’s see a few more of them that could be quite handy.
Basic and Practical commands
Connect to a database
psql -h <hostname> -P <port> -U <username> -d <database> -W
Switch connection to another database
\df for functions,
\dv for views.
Note: Make sure you are connected to the database you want to list the tables from.
List users and roles
List available commands
Execute previous command
List command history
Output query results to a file
postgres=#\o output.txt postgres=# <execute_query_here>
\o again if you want to turn it off.
Enable query execution time
postgres=#\timing Timing is on. postgres=# <execute_query_here>
\timing again if you want to turn it off.
Get PostgreSQL version
Count the numbers of rows in a table
SELECT count(*) FROM table;
Show MAX connection parameter
Quit / Exit
These are most commonly used ones, but if you got any other to add, feel free to write them down in the comments below.
And, if you find this tutorial useful, follow our official channel on telegram.