Database

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Installing PSequel

PSequel, a PostgreSQL GUI tool, provides a clean and simple interface for you to perform common PostgreSQL tasks quickly.

Installing Postgres via Brew

Pre-Reqs

$ brew doctor
$ brew update

Installing

$ brew install postgresql

PostgreSQL Commands

Database Commands

Start PostgreSQL Service

brew services start postgresql

Stop PostgreSQL Service

brew services stop postgresql

Create database

createdb <database_name>

We can connect to the <database_name> using PSequel. By default it runs on localhost port:5432.

Connect to the database / Enter into the database

psql <dtabase_name>

List databases

psql -U postgres -l

Show tables in database

psql -U postgres -d <database_name>

Drop database

dropdb <database_name>

Restart database

dropdb <database_name> && createdb <database_name>

Useful commands

command	desc
\l	list available database
\dt	list available tables
\dn	list available schemas of the currently connected database
\du	list users and their roles
\g	executes the previous command again
\s	display command history
\?	get help on psql commands ex. \h ALTER TABLE
\q	quit `psql

DDL (Data Definition Language) commands

Create a table

CREATE TABLE table_name (
   column_name TYPE column_constraint,
   table_constraint table_constraint
) INHERITS existing_table_name;

DML (Data Manipulation Language) Commands

Common Data Types

• smallint
• int
• bigint
• boolean
• date
• money
• numeric
• text

Resources

• PostgreSQL: SQL Commands
• Github: PostgreSQL commands