Common Postgresql Сonsole (psql) Сommands

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 🙂