From 20219dd4f7192c61492b7667dd3f15b05dbba044 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Sat, 23 Sep 2023 20:19:44 +0200 Subject: feat: Add post to version control --- content/posts/postgres-aggregates/setup.sql | 36 +++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) create mode 100644 content/posts/postgres-aggregates/setup.sql (limited to 'content/posts/postgres-aggregates/setup.sql') diff --git a/content/posts/postgres-aggregates/setup.sql b/content/posts/postgres-aggregates/setup.sql new file mode 100644 index 0000000..622f02b --- /dev/null +++ b/content/posts/postgres-aggregates/setup.sql @@ -0,0 +1,36 @@ +CREATE TABLE products ( + id SERIAL PRIMARY KEY, + name varchar(255) NOT NULL, + description text, + price numeric(10,2) NOT NULL, + created_at timestamp NOT NULL DEFAULT NOW() +); + +INSERT INTO products (name, description, price) +SELECT concat('product_', n), 'This is a product description.', trunc(random() * 1000 + 1) +FROM generate_series(1, 10000) as n; + +CREATE TABLE orders ( + id SERIAL PRIMARY KEY, + order_date DATE NOT NULL, + customer_name TEXT NOT NULL, + product_id INTEGER NOT NULL REFERENCES products(id) +); + +INSERT INTO orders (order_date, customer_name, product_id) +SELECT now(), concat('customer_', n), floor(random() * 10000) + 1 +FROM generate_series(1, 1000000) as n; + +CREATE TABLE reviews ( + id SERIAL PRIMARY KEY, + review_date TIMESTAMP NOT NULL, + comment TEXT NOT NULL, + product_id INTEGER NOT NULL REFERENCES products(id) +); + +INSERT INTO reviews (review_date, comment, product_id) +SELECT now(), concat('Comment for review #', n), floor(random() * 10000) + 1 +FROM generate_series(1, 100000) as n; + +CREATE INDEX reviews_product_id_idx ON reviews (product_id); +CREATE INDEX orders_product_id_idx ON orders (product_id); -- cgit v1.3