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)
