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

command	desc
brew services start postgresql	start PostgreSQL Service
brew services stop postgresql	stop PostgreSQL Service
createdb <database_name>	create database
psql <dtabase_name>	connect to the database / enter into the database
psql -U postgres -l	list databases
psql -U postgres -d <database_name>	show tables in database
dropdb <database_name>	drop database
dropdb <database_name> && createdb <database_name>	restart database

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

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;

-- example

CREATE TABLE account(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP
);

Drop a table

DROP TABLE users;

Modifying Tables

Adding a column

ALTER TABLE products ADD COLUMN description text;

--- example
ALTER TABLE users ADD COLUMN score smallint;

DML (Data Manipulation Language) Commands

Inserting Data

INSERT INTO users (name, age, birthday) VALUES ('Andrei', 31, '1970-01-25');

Viewing data

SELECT * FROM users;

Updating data

UPDATE products SET price = 10 WHERE price = 5;

Deleting data

DELETE FROM users WHERE name = 'John';

Queries

The where clause

SELECT * FROM users WHERE name LIKE 'R%'; -- name starts with 'R'

Sort

SELECT * FROM users ORDER BY score DESC;

Functions

Mathematical Functions and Operators

SELECT AVG(score) FROM users;
SELECT SUM(age) FROM users;
SELECT COUNT(age) FROM users;

Data Types

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

Resources

• PostgreSQL: SQL Commands
• Github: PostgreSQL commands