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.
Prerequisites
- PostgreSQL
Basic and Practical commands
Connect to a database
psql -h <hostname> -P <port> -U <username> -d <database> -W
Switch connection to another database
\c <database>
List databases
\l
List schemas
\dn
Use \df for functions, \dv for views.
List tables
\dt
Note(s): Make sure you are connected to the database you want to list the tables from.
Describe table
\d <table_name>
List users and roles
\du
List available commands
\?
Execute previous command
\g
List command history
\s
Output query results to a file
postgres=#\o output.txt
postgres=# <execute_query_here>
Note(s): Execute \o again if you want to turn it off.
Enable query execution time
postgres=#\timing
Timing is on.
postgres=# <execute_query_here>
Note(s): Execute \timing again if you want to turn it off.
Get PostgreSQL version
SELECT version();
Count the numbers of rows in a table
SELECT count(*) FROM table;
Show MAX connection parameter
SHOW max_connections;
Quit/Exit
\q
Conclusion
These are most commonly used ones, but if you got any other to add, feel free to write them down in the comments below. If you find this tutorial useful, follow our official channel on Telegram.