Skip to content

Instantly share code, notes, and snippets.

@pmorelli92

pmorelli92/seed.sql

Last active Mar 21, 2021
Embed
What would you like to do?
Reducing loading times by a tenfold with Postgres aggregations
-- Start an instance of postgres
-- docker run --name db -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:latest
CREATE DATABASE veterinary;
CREATE TABLE person (
id UUID PRIMARY KEY,
name TEXT
);
CREATE TABLE animal (
id UUID PRIMARY KEY,
owner_id UUID NOT NULL,
type TEXT NOT NULL,
name TEXT NOT NULL,
FOREIGN KEY(owner_id) REFERENCES person(id)
);
CREATE INDEX animal_owner_id ON animal(owner_id uuid_ops);
CREATE TABLE vaccine(
id UUID PRIMARY KEY,
name TEXT
);
CREATE TABLE animal_vaccine(
vaccine_id UUID,
animal_id UUID,
applied_on TIMESTAMP,
PRIMARY KEY(vaccine_id, animal_id),
FOREIGN KEY(vaccine_id) REFERENCES vaccine(id),
FOREIGN KEY(animal_id) REFERENCES animal(id)
);
CREATE INDEX animal_vaccine_animal_id ON animal_vaccine(animal_id uuid_ops);
CREATE INDEX animal_vaccine_vaccine_id ON animal_vaccine(vaccine_id uuid_ops);
INSERT INTO person(id, name)
VALUES ('fbab22a3-f7bc-422c-942f-d184ad189e29', 'Pablo');
INSERT INTO animal(id, owner_id, type, name) VALUES
('5f2461a9-6c62-453e-9182-38cd0fa76e01', 'fbab22a3-f7bc-422c-942f-d184ad189e29', 'cat', 'Bills');
INSERT INTO vaccine(id, name) VALUES
('0ee28253-5b94-4096-926c-d33ceb22d24d', 'Feline herpesvirus'),
('6cf5487a-ba06-42e7-9e80-226590752a77', 'Rabies');
INSERT INTO animal_vaccine(vaccine_id, animal_id, applied_on) VALUES
('0ee28253-5b94-4096-926c-d33ceb22d24d', '5f2461a9-6c62-453e-9182-38cd0fa76e01', '2021-02-10'),
('6cf5487a-ba06-42e7-9e80-226590752a77', '5f2461a9-6c62-453e-9182-38cd0fa76e01', '2022-02-10');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment