From bb72edc0782d2ce5a990504da58662beec61cd7b Mon Sep 17 00:00:00 2001
From: Martin Kagamino Lehoux <martin@lehoux.net>
Date: Tue, 3 Oct 2023 19:18:38 +0200
Subject: fix(postgres-aggregates): Some rewrite and more assets

---
 content/posts/postgres-aggregates/setup.sql | 62 ++++++++++++++++++-----------
 1 file changed, 38 insertions(+), 24 deletions(-)

(limited to 'content/posts/postgres-aggregates/setup.sql')

diff --git a/content/posts/postgres-aggregates/setup.sql b/content/posts/postgres-aggregates/setup.sql
index 622f02b..551a979 100644
--- a/content/posts/postgres-aggregates/setup.sql
+++ b/content/posts/postgres-aggregates/setup.sql
@@ -1,36 +1,50 @@
-CREATE TABLE products (
-  id SERIAL PRIMARY KEY,
+CREATE TABLE products(
+  id serial PRIMARY KEY,
   name varchar(255) NOT NULL,
   description text,
-  price numeric(10,2) NOT NULL,
+  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; 
+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)
+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;
+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)
+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;
+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);
 
-CREATE INDEX reviews_product_id_idx ON reviews (product_id);
-CREATE INDEX orders_product_id_idx ON orders (product_id);
-- 
cgit v1.3