PostgreSQL Command Cheat Sheet

Here is a summary of commonly used PostgreSQL commands:

Connect to database

bash command

$ psql -h HOST_NAME -p PORT_NUMBER -U USER_NAME -d DATABASE_NAME

From here on, I will list SQL commands.

Show list of databases

\l

Create Database

CREATE DATABASE DATABASE_NAME;

Drop Database

DROP DATABASE DATABASE_NAME;

Show Tables

\dt

Create user (create role)

CREATE ROLE USER_NAME WITH LOGIN PASSWORD 'PASSWORD';

Grant all privilege on DB to User 

GRANT ALL PRIVILEGES ON DATABASE DATABASE_NAME TO USER_NAME;

Grant access to schema

GRANT ALL ON SCHEMA public TO USER_NAME;

Change owner of database

ALTER DATABASE DATABASE_NAME OWNER TO USER_NAME;

Drop all tables of specific database

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO USER_NAME;