-- Schemas
---- Show all schemas
SELECT schema_name FROM information_schema.schemata;

---- Create tables and new schema
CREATE TABLE public.courses (
    number serial primary key,
    name text
);
CREATE SCHEMA fct;
CREATE TABLE fct.courses (
    number serial primary key,
    short text
);

---- Insert data
INSERT INTO public.courses (name) VALUES
    ('Computer Science'),
    ('Big Data');
INSERT INTO fct.courses (short) VALUES
    ('MIEI'),
    ('MAEBD');



-- Partitioning
---- Create main table
CREATE TABLE public.students (
    name text,
    city text
) PARTITION BY LIST (city);

---- Create partitions
CREATE TABLE public.students_lisbon PARTITION OF public.students
    FOR VALUES IN ('Lisbon');
CREATE TABLE public.students_porto PARTITION OF public.students
    FOR VALUES IN ('Porto');
CREATE TABLE public.students_coimbra PARTITION OF public.students
    FOR VALUES IN ('Coimbra');

---- Insert data
INSERT INTO public.students VALUES
    ('Santiago', 'Lisbon'),
    ('Maria', 'Lisbon'),
    ('Francisco', 'Lisbon'),
    ('Leonor', 'Lisbon'),
    ('João', 'Lisbon'),
    ('Matilde', 'Lisbon'),
    ('Afonso', 'Lisbon'),
    ('Beatriz', 'Lisbon'),
    ('Dinis', 'Lisbon'),
    ('Lara', 'Lisbon'),
    ('Dinis', 'Lisbon'),
    ('Alice', 'Lisbon'),
    ('Rodrigo', 'Lisbon'),
    ('Carolina', 'Lisbon'),
    ('Martim', 'Lisbon'),
    ('Mariana', 'Lisbon'),
    ('Tomás', 'Lisbon'),
    ('Ana', 'Lisbon'),
    ('Duarte', 'Lisbon'),
    ('Francisca', 'Lisbon'),
    ('Pedro', 'Lisbon'),
    ('Laura', 'Lisbon'),
    ('Salvador', 'Lisbon'),
    ('Benedita', 'Lisbon'),
    ('Miguel', 'Lisbon'),
    ('Sofia', 'Lisbon'),
    ('Gabriel', 'Lisbon'),
    ('Inês', 'Lisbon'),
    ('Lourenço', 'Lisbon'),
    ('Margarida', 'Lisbon'),
    ('Gonçalo', 'Lisbon'),
    ('Clara', 'Lisbon'),
    ('Rafael', 'Lisbon'),
    ('Diana', 'Lisbon'),
    ('Lucas', 'Lisbon'),
    ('Madalena', 'Lisbon'),
    ('Santiago', 'Porto'),
    ('Maria', 'Porto'),
    ('Francisco', 'Porto'),
    ('Leonor', 'Porto'),
    ('João', 'Porto'),
    ('Matilde', 'Porto'),
    ('Afonso', 'Porto'),
    ('Beatriz', 'Porto'),
    ('Dinis', 'Porto'),
    ('Lara', 'Porto'),
    ('Dinis', 'Porto'),
    ('Alice', 'Porto'),
    ('Rodrigo', 'Porto'),
    ('Carolina', 'Porto'),
    ('Martim', 'Porto'),
    ('Mariana', 'Porto'),
    ('Tomás', 'Porto'),
    ('Ana', 'Porto'),
    ('Duarte', 'Porto'),
    ('Francisca', 'Porto'),
    ('Pedro', 'Porto'),
    ('Laura', 'Porto'),
    ('Salvador', 'Porto'),
    ('Benedita', 'Porto'),
    ('Miguel', 'Porto'),
    ('Sofia', 'Porto'),
    ('Gabriel', 'Porto'),
    ('Inês', 'Porto'),
    ('Lourenço', 'Porto'),
    ('Margarida', 'Porto'),
    ('Gonçalo', 'Porto'),
    ('Clara', 'Porto'),
    ('Rafael', 'Porto'),
    ('Diana', 'Porto'),
    ('Lucas', 'Porto'),
    ('Madalena', 'Porto'),
    ('Santiago', 'Coimbra'),
    ('Maria', 'Coimbra'),
    ('Francisco', 'Coimbra'),
    ('Leonor', 'Coimbra'),
    ('João', 'Coimbra'),
    ('Matilde', 'Coimbra'),
    ('Afonso', 'Coimbra'),
    ('Beatriz', 'Coimbra'),
    ('Dinis', 'Coimbra'),
    ('Lara', 'Coimbra'),
    ('Dinis', 'Coimbra'),
    ('Alice', 'Coimbra'),
    ('Rodrigo', 'Coimbra'),
    ('Carolina', 'Coimbra'),
    ('Martim', 'Coimbra'),
    ('Mariana', 'Coimbra'),
    ('Tomás', 'Coimbra'),
    ('Ana', 'Coimbra'),
    ('Duarte', 'Coimbra'),
    ('Francisca', 'Coimbra'),
    ('Pedro', 'Coimbra'),
    ('Laura', 'Coimbra'),
    ('Salvador', 'Coimbra'),
    ('Benedita', 'Coimbra'),
    ('Miguel', 'Coimbra'),
    ('Sofia', 'Coimbra'),
    ('Gabriel', 'Coimbra'),
    ('Inês', 'Coimbra'),
    ('Lourenço', 'Coimbra'),
    ('Margarida', 'Coimbra'),
    ('Gonçalo', 'Coimbra'),
    ('Clara', 'Coimbra'),
    ('Rafael', 'Coimbra'),
    ('Diana', 'Coimbra'),
    ('Lucas', 'Coimbra'),
    ('Madalena', 'Coimbra');

---- Delete students from Porto
DROP TABLE public.students_porto;



-- NOP --
-- TOAST
---- Create table
CREATE TABLE public.articles (
    number serial primary key,
    name text,
    content bytea
);

---- Insert data
INSERT INTO public.articles (name, content) VALUES
    ('PostgreSQL', pg_read_binary_file('/docs/sbd.pdf'));

---- See data
WITH
     articles_toast_oid as (SELECT reltoastrelid FROM pg_class WHERE relname = 'articles')
SELECT relname FROM pg_class WHERE oid=(table articles_toast_oid);
SELECT * FROM pg_toast.pg_toast_16409;