postgresql,

Basic and Common PostgreSQL commands

Jul 27, 2021 · 2 mins read · Post a comment
Basic and Common PostgreSQL commands

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: 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: Execute \o again if you want to turn it off.

Enable query execution time

postgres=#\timing
Timing is on.
postgres=# <execute_query_here>

Note: 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.
And, if you find this tutorial useful, follow our official channel on telegram.