• Blog
  • Common Postgresql Сonsole (psql) Сommands
Common Postgresql Сonsole (psql) Сommands
Volodymyr Hodiak
Volodymyr Hodiak
393 January, 4, 2020 8 min

Need a small cheat sheet? Here is the list of common Postgresql console (psql) commands to help you query data from the PostgreSQL database server more quickly and effectively.

Connect to a local database:

psql -d database -U user -W

If you want to connect to a specified host:

psql -h host -d database -U user -W

Get help on psql commands:


Switch connection to a new database:

\c dbname username

List available databases:


List available tables:


Describe a table:

\d table_name

List available schemas:


List available functions:


List available views:


List users and their roles:


Show the current version of PostgreSQL server:

SELECT version();

Execute the previous command:


Command history:


If you want to save the command history to a file:

\s filename

Execute psql commands from a file (import from file):

\i filename

Turn on query execution time:


Edit command in your default editor:


Quit psql:


Create a database:


Replace variables in "__var__" -> "myvar"

Create a user and assign it to a database

create user __dev__ with encrypted password '__my-encrypted-pass__';
grant all privileges on database foo to dev;

Another way:

Switch user to Postgres

sudo -u postgres psql

Create a user:

sudo -u postgres createuser __username__;

Create a database:

	sudo -u postgres createdb __dbname__

Give user a password:

	$ sudo -u postgres psql;
    psql=# alter user __username__ with encrypted password '__my-encrypted-pass__';

Grant all privileges on a database:

	psql=# grant all privileges on database __dbname__ to __username__;

Import dump to a database:

psql -h __hostname__ -d __dbname__ -U __username__ -f {/path/to/dump.sql}

Hope it will be helpful for you)