Basic and common PostgreSQL commands

Jul 27, 2021 · 2 mins read · Post a comment

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.


  • 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


List schemas


Use \df for functions, \dv for views.

List tables


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


List available commands


Execute previous command


List command history


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

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;




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.