Postgres (useful commands)

README.md

PostgreSQL

Connecting to database

psql -h <host> -U <username> <database-name>

or

psql -h <host> -U <username> -l

Show available databases

\l

Switch database

\c dbname

Listing schemas in current database

\dn

Listing tables in a schema

\dt myschema.*

Showing indexes

\di

Describing a table

\d table

Listing users

\du

Create user

CREATE USER user_name WITH PASSWORD 'password';

Granting permissions to a database

GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name;

Granting permissions to a schema

GRANT ALL PRIVILEGES ON SCHEMA schema_name TO user_name;

Change user password

ALTER USER user_name WITH PASSWORD 'new_password';

Delete user

DROP USER IF EXISTS user_name;

View size of all databases

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;