PostgreSQL Workshop

day1.sql

-- DROP TABLE books;

CREATE TABLE books
(
    id   SERIAL PRIMARY KEY,
    name CHARACTER VARYING NOT NULL
);

CREATE TABLE editions
(
    id      SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books (id),
    name    CHARACTER VARYING
)

ALTER TABLE editions
    ALTER COLUMN book_id SET NOT NULL;

CREATE TABLE genres
(
    id         SERIAL PRIMARY KEY,
    name       CHARACTER VARYING NOT NULL,
    created_at TIMESTAMP         NOT NULL DEFAULT NOW()
)

INSERT INTO genres (name, created_at)
VALUES ('horror', DEFAULT),
       ('romance', '2018-01-01')

CREATE TABLE books_genres
(
    id       SERIAL PRIMARY KEY,
    book_id  INTEGER   NOT NULL REFERENCES books (id),
    genre_id INTEGER   NOT NULL REFERENCES genres (id),
    added_on TIMESTAMP NOT NULL DEFAULT NOW()
)

ALTER TABLE books_genres
    ADD CONSTRAINT books_genres_uniqueness
        UNIQUE (book_id, genre_id)

ALTER TABLE books_genres
    ADD CONSTRAINT genres_books_uniqueness
        UNIQUE (genre_id, book_id)

CREATE TABLE users
(
    id    SERIAL PRIMARY KEY,
    name  character varying,
    email character varying UNIQUE
);

CREATE TABLE shelves
(
    id   SERIAL PRIMARY KEY,
    name character varying
);

-- This naming is not recommended as it should be alphabetic
--
-- ALTER TABLE users_shelves
--     DROP CONSTRAINT users_shelves_uniqueness;
--
-- ALTER TABLE users_shelves
--     DROP CONSTRAINT shelves_users_uniqueness;
--
-- DROP TABLE  users_shelves;

CREATE TABLE shelves_users
(
    id       SERIAL PRIMARY KEY,
    user_id  INTEGER   NOT NULL REFERENCES users (id),
    shelf_id INTEGER   NOT NULL REFERENCES shelves (id),
    added_on TIMESTAMP NOT NULL DEFAULT NOW()
);

ALTER TABLE shelves_users
    ADD CONSTRAINT users_shelves_uniqueness
        UNIQUE (user_id, shelf_id);

ALTER TABLE shelves_users
    ADD CONSTRAINT shelves_users_uniqueness
        UNIQUE (shelf_id, user_id);

CREATE TABLE editions_shelves
(
    id         SERIAL PRIMARY KEY,
    edition_id INTEGER   NOT NULL REFERENCES editions (id),
    shelf_id   INTEGER   NOT NULL REFERENCES shelves (id),
    added_on   TIMESTAMP NOT NULL DEFAULT NOW()
);

ALTER TABLE editions_shelves
    ADD CONSTRAINT editions_shelves_uniqueness
        UNIQUE (edition_id, shelf_id);


INSERT INTO books (name)
VALUES ('Kane and Abel'),
       ('Harry Potter 1'),
       ('Harry Potter 2'),
       ('Harry Potter 3');

ALTER TABLE editions
    ADD COLUMN current BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE editions_shelves
    ADD CONSTRAINT shelves_editions_uniqueness
        UNIQUE (shelf_id, edition_id);

ALTER TABLE editions
    ADD CONSTRAINT only_one_current_edition
        EXCLUDE USING btree (book_id WITH =) WHERE (current);

ALTER TABLE users
    ADD CONSTRAINT username_length_validation
        CHECK (5 <= length(name) AND length(name) <= 20);

-- TRUNCATE TABLE genres RESTART IDENTITY CASCADE;

CREATE TABLE authors
(
    id         SERIAL PRIMARY KEY,
    name       CHARACTER VARYING NOT NULL,
    created_at TIMESTAMP         NOT NULL DEFAULT now()
);

CREATE TABLE authors_books
(
    id        SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES authors (id),
    book_id   INTEGER NOT NULL REFERENCES books (id),
    position  INTEGER NOT NULL CHECK ( position > 0 ),
    CONSTRAINT authors_books_uniqueness UNIQUE (author_id, book_id),
    CONSTRAINT books_authors_uniqueness UNIQUE (book_id, author_id),
    CONSTRAINT books_position_uniqueness UNIQUE (book_id, position)
);

CREATE TABLE authors_editions
(
    id        SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES authors (id),
    edition_id   INTEGER NOT NULL REFERENCES editions (id),
    position  INTEGER NOT NULL CHECK ( position > 0 ),
    CONSTRAINT authors_editions_uniqueness UNIQUE (author_id, edition_id),
    CONSTRAINT editions_authors_uniqueness UNIQUE (edition_id, author_id),
    CONSTRAINT editions_position_uniqueness UNIQUE (edition_id, position)
);

day2.sql

-- nesting a read and write for same table
INSERT INTO users (name, email)
    (
        SELECT name || '-foo', email || '1'
        FROM users
    );

-- add missing constraint in data
ALTER TABLE books_genres
    ADD CONSTRAINT books_genres_uniqueness
        UNIQUE (book_id, genre_id);

SELECT COUNT(*)
FROM editions_shelves

-- 1. find books without genres
SELECT COUNT(books.id)
FROM books
         LEFT JOIN books_genres
                   ON books.id = books_genres.book_id
WHERE books_genres.id is NULL;

-- a book with genre
SELECT *
FROM books_genres
WHERE books_genres.book_id = 1;

-- a book without genre
SELECT *
FROM books_genres
WHERE books_genres.book_id = 20;


-- 2. authors with edition, but no books
SELECT authors.id, authors.name
FROM authors
         INNER JOIN authors_editions ON authors.id = authors_editions.author_id
         LEFT JOIN authors_books ON authors.id = authors_books.author_id
WHERE authors_books.id IS NULL;

-- optimisation without authors table
SELECT authors_editions.author_id
FROM authors_editions
         LEFT JOIN authors_books ON authors_editions.author_id = authors_books.author_id
WHERE authors_books.id IS NULL;


-- 3. find all authors of a book including all editions
SELECT DISTINCT authors.id, authors.name
FROM authors
         LEFT JOIN authors_books ON authors_books.author_id = authors.id
         LEFT JOIN authors_editions ON authors_editions.author_id = authors.id
         INNER JOIN editions ON editions.id = authors_editions.edition_id
WHERE editions.book_id = 1
   OR authors_books.book_id = 1;

-- the above approach has a very large number of columns in the intermediate state
-- split into two parts

-- first authors of the book
SELECT authors.id, authors.name, 'author of book' as reason
FROM authors
         INNER JOIN authors_books on authors.id = authors_books.author_id
WHERE authors_books.book_id = 1

UNION

-- then authors of all editions of the book
SELECT authors.id, authors.name, 'author of edition' as reason
FROM authors
         INNER JOIN authors_editions on authors_editions.author_id = authors.id
         INNER JOIN editions on editions.id = authors_editions.edition_id
WHERE editions.book_id = 1;


-- 4. given a user id, find shelves that are private to the user
SELECT shelf_id,
       COUNT(user_id)          AS num_users,
       array_agg(user_id)      AS users_list,
       (array_agg(user_id))[1] AS first_user
FROM shelves_users
GROUP BY shelf_id
HAVING COUNT(user_id) > 0
   AND 39 = SOME (array_agg(user_id))
   AND 39 = (array_agg(user_id))[1]
ORDER BY num_users DESC;

-- example of a nested query
SELECT su1.user_id, su1.shelf_id
FROM shelves_users su1
WHERE shelf_id = (
    SELECT su2.shelf_id
    FROM shelves_users su2
    WHERE su2.shelf_id = su1.shelf_id
    GROUP BY shelf_id
    HAVING count(su2.user_id) = 1)
  AND user_id = 39;