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
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', 'jose@mail.com', 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;
