• Blog
  • Common Postgresql Сonsole (psql) Сommands
Common Postgresql Сonsole (psql) Сommands
Volodymyr Hodiak
Volodymyr Hodiak
492 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:

\l

List available tables:

\dt

Describe a table:

\d table_name

List available schemas:

\dn

List available functions:

\df

List available views:

\dv

List users and their roles:

\du

Show the current version of PostgreSQL server:

SELECT version();

Execute the previous command:

\g

Command history:

\s

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:

\timing

Edit command in your default editor:

\e

Quit psql:

\q

Create a database:

CREATE DATABASE __foo__;

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)