From 07871783082839a768e4fa999cee58532d0a79fe Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Sun, 7 Sep 2025 21:35:50 +0200 Subject: draft for nested postgres results --- content/posts/nested-postgres-results/setup.sql | 26 +++++++++++++++++++++++++ 1 file changed, 26 insertions(+) create mode 100644 content/posts/nested-postgres-results/setup.sql (limited to 'content/posts/nested-postgres-results/setup.sql') diff --git a/content/posts/nested-postgres-results/setup.sql b/content/posts/nested-postgres-results/setup.sql new file mode 100644 index 0000000..a54563b --- /dev/null +++ b/content/posts/nested-postgres-results/setup.sql @@ -0,0 +1,26 @@ +CREATE TABLE accommodations ( + id serial PRIMARY KEY, + name text NOT NULL, + default_price numeric(10, 2) NOT NULL +); + +INSERT INTO accommodations (name, default_price) +SELECT + CONCAT('accommodation_', generate_series) AS name, + TRUNC(RANDOM() * 100 + 200) AS default_price +FROM GENERATE_SERIES(1, 10000); + +CREATE TABLE overrides ( + id serial PRIMARY KEY, + accommodation_id integer NOT NULL REFERENCES accommodations (id), + date date NOT NULL, + price numeric(10, 2) NOT NULL +); +ALTER TABLE overrides ADD constraint unique_dates UNIQUE ( + accommodation_id, date +); +INSERT INTO overrides (accommodation_id, date, price) SELECT + (generate_series / 100) + 1 AS accommodation_id, + '2025-01-01'::date + (generate_series % 100) * '3 days'::interval AS date, + TRUNC(RANDOM() * 100 + 200) AS price +FROM GENERATE_SERIES(1, 999999); -- cgit v1.3