Abdullah Şamil Güser

PostgreSQL

What is a Database?

What is SQL and Relational Database?

What is PostgreSQL AKA Postgres?

PostgreSQL Installation (MacOS)

GUI Clients vs Terminal/CMD Clients

Setup PSQL (MacOS)

Create Database

Connect to Database

Delete a Database

Create Table

Create Tables With Constraints

Drop Table

Insert Data into Table

Generate 1000 People with Mockaroo

Select From

SELECT * FROM person;
SELECT first_name, last_name FROM person;

Order By

SELECT * FROM person ORDER BY first_name; -- it's ASC by default
SELECT * FROM person ORDER BY first_name DESC;

Distinct

SELECT DISTINCT countr_of_birth FROM person ORDER BY countr_of_birth;

WHERE Clause and AND

SELECT * FROM person WHERE gender = 'Female';
SELECT * FROM person WHERE gender = 'Female' AND country_of_birth = 'Poland';

Comparison Operators

SELECT 1 = 1;  -- equals
SELECT 1 <> 1; -- not equals
SELECT 1 > 1;  -- greater than
SELECT 1 < 1;  -- less than
SELECT 1 >= 1; -- greater than or equal
SELECT 1 <= 1; -- less than or equal

Limit, Offset, Fetch

SELECT * FROM person LIMIT 10;
SELECT * FROM person OFFSET 10 LIMIT 10;
SELECT * FROM person OFFSET 10 FETCH FIRST 10 ROW ONLY; -- this is original way of limiting in sql

In

SELECT * FROM person WHERE country_of_birth IN ('Poland', 'Germany');

Between

SELECT * FROM person WHERE date_of_birth BETWEEN DATE '1990-01-01' AND DATE '1999-12-31';

Like and ILike

SELECT * FROM person WHERE email LIKE '%@bloomberg.com';
SELECT * FROM person WHERE email LIKE '_______@%'; -- 7 characters before @
SELECT * FROM person WHERE email ILIKE '%@bloomberg.com'; -- case insensitive

Group By & Group By Having

SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth;
SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*) > 5;

-- ORDER BY must come after HAVING
SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*) > 5 ORDER BY country_of_birth;

Max, Min, Avg

SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;
SELECT make, model, MIN(price) FROM car GROUP BY make, model;

Sum

SELECT SUM(price) FROM car;
SELECT make, SUM(price) FROM car GROUP BY make;

Basic Arithmetic Operations

SELECT 1 + 1;  -- addition
SELECT 1 - 1;  -- subtraction
SELECT 1 * 1;  -- multiplication
SELECT 1 / 1;  -- division
SELECT 1 ^ 1;  -- power
SELECT 1!;     -- factorial
SELECT 1 % 1;  -- modulo

Round

SELECT ROUND(1.5);         -- 2
SELECT ROUND(1.43465, 2);  -- 1.43

Alias

SELECT first_name AS name FROM person;

Coalesce

SELECT COALESCE(NULL, 'Amigoscode');        -- Amigoscode
SELECT COALESCE(NULL, NULL, 'Amigoscode');  -- Amigoscode

NULLIF

Timestamps and Dates

SELECT NOW();        -- 2021-01-09 16:00:00.000000
SELECT NOW()::DATE;  -- 2021-01-09
SELECT NOW()::TIME;  -- 16:00:00.000000

Adding and Subtracting Dates

SELECT NOW() - INTERVAL '1 YEAR';
SELECT NOW() - INTERVAL '10 YEARS';
SELECT NOW() - INTERVAL '1 MONTH';
SELECT NOW() - INTERVAL '1 DAY';
SELECT (NOW() - INTERVAL '1 DAY')::DATE;

Extracting Fields from Timestamp

SELECT EXTRACT(YEAR FROM NOW());  -- 2021
SELECT EXTRACT(MONTH FROM NOW()); -- 1
SELECT EXTRACT(DAY FROM NOW());   -- 9
SELECT EXTRACT(DOW FROM NOW());   -- 6

Age Function

SELECT AGE('1990-01-01');  -- 31 years 8 days
SELECT AGE(NOW(), date_of_birth) AS age FROM person;

Primary Keys

Drop Primary Key

ALTER TABLE person DROP CONSTRAINT person_pkey;

Adding Primary Key

ALTER TABLE person ADD PRIMARY KEY (id);

Unique Constraints

Check Constraints

Delete Records

DELETE FROM person WHERE id = 1;

Update Records

UPDATE person SET first_name = 'Anne' WHERE id = 2;

On Conflict Do Nothing

INSERT INTO person (id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (2017, 'Russ' "Ruddoch', 'Male', 'rruddoch7@hhs.gov', DATE 1952-09-25', 'Norway')
ON CONFLICT (id) DO NOTHING;

Upsert

INSERT INTO person (id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (2017, 'Russ' "Ruddoch', 'Male', 'rruddoch7@hhs.gov', DATE 1952-09-25', 'Norway')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, date_of_birth = EXCLUDED.date_of_birth;

Foreign Keys, Joins, Relationships

create table car (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    price NUMERIC(19, 2) NOT NULL
);

create table person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(7) NOT NULL,
    email VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    country_of_birth VARCHAR(50) NOT NULL,
    car_id BIGINT REFERENCES car(id),
    UNIQUE(car_id)
);

insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Fernanda', 'Beardon', 'Female', 'fernandabais.gd', '1953-10-28', 'Finland');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Omar', 'Colomore', 'Male', null, '1921-04-03', 'Finland');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Adriana', 'Matuschek', 'Female', 'amatuschek2@feedburner.com', null, null);

insert into car (make, model, price) values ('Land Rover', 'Sterling', '87665.38');
insert into car (make, model, price) values ('GMC', 'Acadia', '17662.69');

Update Foreign Key Columns

UPDATE person SET car_id = 1 WHERE id = 1;

Inner Join

Left Join

Delete Records with Foreign Keys

Exporting Query Results to CSV

Extensions & UUID

SELECT * FROM pg_available_extensions;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();  --random uuid

UUID as Primary Key

Alternative way of Joining

SELECT * FROM person, 
JOIN car USING (car_uuid);

References