The PostgreSQL Book

Notes on how to use the database

How to install and connect to postgres using docker

// (1) Download the latest version of PostgresSQL from the Docker hub
debugme@desktop ~> docker pull postgres:latest

// (2) Start up a container based on the downloaded PostgreSQL image
debugme@desktop ~> sudo mkdir -p /var/lib/postgresql/data
debugme@desktop ~> docker run --name mypostgresbox -e POSTGRES_PASSWORD=password -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres

// (3) Check container logs to see if there were any issues
debugme@desktop ~> docker logs mypostgresbox 2>&1

// (4) Log into PostgreSQL on the container
debugme@desktop ~> docker exec -it mypostgresbox psql -U postgres

How to install and connect to postgres using docker-compose

// (1) define your docker-compose.yaml file
version: '3'
services:
  database:
    image: postgres
    environment:
      - POSTGRES_PASSWORD=password
      
// (2) start-up your containers
docker-compose up --build -d       
      
// (3) connect to psql shell inside container
docker exec -it postgres_database_1 psql -h database -U postgres

How to connect to a database directly

$ psql dvdrental

How to connect to a database indirectly

$ psql
debugme=# \c dvdrental

How to list all databases

$ psql
debugme=# \l

How to list all tables in active database

$ psql dvdrental
dvdrental=# \dt

How to toggle between expanded display

$ psql dvdrental
dvdrental=# \x

How to select all columns from a table

dvdrental=# select * from actor;

How to select specific columns from a table

dvdrental=# select
dvdrental-# first_name, last_name
dvdrental-# from actor;

How to find out what columns are in a table (Does not work if in expanded display mode)

dvdrental=# select * from actor limit 0;

How to get a list of unique values for a column

dvdrental=# select distinct(release_year) from film;

How to get a count of the number of rows in a table

dvdrental=# select count(*) from film;

How to get a count of the number of distinct values in a column

dvdrental=# select count(distinct(rating)) from film;

How to get rows that satisfy a query

dvdrental=# select email from customer 
dvdrental=# where first_name = 'Nancy' and last_name='Thomas';

How to order rows by one column and then within that, another column

dvdrental=# select store_id, first_name from customer 
dvdrental=# order by store_id desc, first_name asc;

How to limit the number of rows

dvdrental=# select * from film limit 5;

How to find all rows within an inclusive-inclusive range

dvdrental=# select * from payment
dvdrental=# where payment_date between '2007-02-01' and '2007-02-15';

How to find all rows whose value is in a list of values you are interested in

dvdrental=# select * from customer
dvdrental-# where first_name in ('John', 'Julie');

How to find all rows that match a pattern (_ for a single characters, % for zero or more characters)

dvdrental=# select * from customer
dvdrental-# where first_name ilike '__m%';

How to use some common aggregate functions (aggregate means multiple inputs, single output)

dvdrental=# select min(replacement_cost),
dvdrental-# max(replacement_cost),
dvdrental-# round(avg(replacement_cost), 2),
dvdrental-# count(replacement_cost),
dvdrental-# sum(replacement_cost) from film;

How to use group by (Note: group by can only be used with non-aggregate values)

dvdrental=# select date(payment_date), sum(amount) 
dvdrental=# from payment 
dvdrental=# group by date(payment_date) 
dvdrental=# order by date(payment_date);

How to use having (Note: having can only be used with aggregate values)

dvdrental=# select customer_id, sum(amount) 
dvdrental=# from payment 
dvdrental=# group by customer_id 
dvdrental=# having sum(amount) > 200;

How to rename columns (Note: aliases can only be referenced in the select clause)

dvdrental=# select store_id as StoreID, first_name as FirstName from customer; 

How to join columns between tables that have values in common (inner join)

dvdrental=# select payment_id, payment.customer_id, first_name  
dvdrental=# from payment inner join customer 
dvdrental=# on payment.customer_id = customer.customer_id;

How to join all columns between tables unconditionally (full outer join)

dvdrental=# select * from customer
dvdrental-# full outer join payment
dvdrental-# on customer.customer_id = payment.customer_id;

How to join columns between tables without values in common (full outer join minus inner join)

dvdrental=# select * from customer
dvdrental-# full outer join payment
dvdrental-# on customer.customer_id = payment.customer_id
dvdrental-# where customer.customer_id = null or payment.payment_id = null;

How to join columns in left table whose values are also in right table (left outer join)

dvdrental=# select film.film_id, title, inventory_id, store_id 
dvdrental=# from film
dvdrental=# left join inventory 
dvdrental=# on film.film_id = inventory.film_id;

How to join columns in left table whose values are not in right table (left outer join minus inner join)

dvdrental=# select film.film_id, title, inventory_id, store_id 
dvdrental=# from film
dvdrental=# left join inventory 
dvdrental=# on film.film_id = inventory.film_id
dvdrental=# where inventory.film_id is null;

How to get various time and date related information

dvdrental=# show timezone               // get timezone as string type
dvdrental=# select now()                // get date, time, timezone as timestamp with timezone type
dvdrental=# select timeofday();         // get date, time, timezone as string type
dvdrental=# select current_time;        // get time, timezone as time with timezone type
dvdrental=# select current_date;        // get date as date type

How to extract and format different parts of a timestamp

dvdrental=# select 
dvdrental=# payment_date,
dvdrental=# extract(year from payment_date) as year,
dvdrental=# extract(month from payment_date) as month,
dvdrental=# extract(day from payment_date) as day,
dvdrental=# extract(quarter from payment_date) as quarter,
dvdrental=# age(payment_date),
dvdrental=# to_char(payment_date, 'dd mon yyyy')
dvdrental=# from payment; 

How to perform a subquery

// Example 1
dvdrental=# select * 
dvdrental=# from film 
dvdrental=# where rental_rate > 
dvdrental=# (select avg(rental_rate) from film);

// Example 2
dvdrental=# select title
dvdrental=# from film
dvdrental=# where film_id in
dvdrental=# (select inventory.film_id 
dvdrental=# from rental 
dvdrental=# inner join inventory
dvdrental=# on inventory.inventory_id = rental.inventory_id
dvdrental=# where return_date between '2005-05-29' and '2005-05-30');

// Example 3
dvdrental=# select first_name, last_name from customer as c 
dvdrental=# where exists
dvdrental=# (select * from payment as p where p.customer_id = c.customer_id and p.amount > 11);

How to perform self-join

dvdrental=# select f1.title, f2.title, f1.length 
dvdrental=# from film as f1 
dvdrental=# inner join film as f2 
dvdrental=# on f1.film_id != f2.film_id and f1.length = f2.length;

How to create a table

create table account(
	user_id serial primary key,
	username varchar(50) unique not null,
	password varchar(50) not null,
	email varchar(200) unique not null,
	created_on timestamp not null,
	last_login timestamp
)

create table job(
  job_id serial primary key,
  job_name varchar(200) unique not null	
)

create table account_job(
  user_id integer references account(user_id),
  job_id integer references job(job_id),
  hire_date timestamp
)

How to insert rows into a table

insert into account(username, password, email, created_on)
values('jose', 'password', '[e-mail]', current_timestamp);

insert into job(job_name) values('astronaut');
insert into job(job_name) values('president');

insert into account_job(user_id, job_id, hire_date)
values(1, 1, current_timestamp);

How to update values in a table

// (1) update using value from function
update account
set last_login = current_timestamp;

// (2) update using value from different column in same table
update account
set last_login = created_on;

// (3) update using value from column in another table
update account_job
set hire_date = account.created_on
from account
where account_job.user_id = account.user_id;

// (4) update and return values you are interested in
update account
set last_login = current_timestamp
returning email, created_on, last_login;

How to delete rows from a table

// (1) How to delete rows that satisfy a condition
delete from job
where job_name = 'astronaut'
returning job_id, job_name;

// (2) How to delete all rows from a table
delete from job;

How to alter a table

// (1) How to rename a table
alter table info
rename to information;

// (2) How to rename a column
alter table information
rename column peerson to person;

// (3) How to remove a constraint from a column
alter table information
alter column person
drop not null;

// (4) How to add a constraint to a column
alter table information
alter column person
set not null;

How to delete a column from a table

alter table information
drop column if exists person;

How to add check constraints

create table employees(
  employee_id serial primary key,
  first_name varchar(50) not null,
  last_name varchar(50) not null,
  birth_date date check(birth_date > '1900-01-01'),
  hire_date date check(hire_date > birth_date),
  salary integer check(salary > 0)
)

How to use case statements

// General Case Statement
select 
customer_id,
case
  when (customer_id <= 100) then 'Premium'
  when (customer_id between 100 and 200) then 'Plus'
  else 'Normal'
end as customer_class
from customer;

// Expression Case Statement
select 
customer_id,
case customer_id
  when 2 then 'winner'
  when 5 then 'second place'
  else 'other'
end as raffle_results
from customer; 

How to use coalesce function

// Handle null values in discount column using coalesce function
select 
price, 
(price - coalesce(discount, 0)) as finalprice
from payments;

How to use cast function and cast operator

select char_length(cast(inventory_id as varchar)) 
from rental;

How to create a view

// (1) Create a view for a query you intend to reuse elsewhere
create view customer_info AS
select first_name, last_name, address 
from customer
inner join address
on customer.address_id = address.address_id;

// (2) Use that view just like another table
select * from customer_info;

// (3) Amend the view definition
create or replace view customer_info AS
select first_name, last_name, address, district 
from customer
inner join address
on customer.address_id = address.address_id;

// (4) Rename the view 
alter view customer_info rename to customer_information;

// (5) Delete the view when you no longer need it
drop view if exists customer_info;