Skip to content

Instantly share code, notes, and snippets.

@adamanthil
Last active September 19, 2016 04:31
Show Gist options
  • Save adamanthil/ddd75cfbcbbc333913f3261c03740c0c to your computer and use it in GitHub Desktop.
Save adamanthil/ddd75cfbcbbc333913f3261c03740c0c to your computer and use it in GitHub Desktop.
Inserts sample data for a hypothetical book sales database used for demonstrating methods of writing PIVOT queries in PostgreSQL. Assumes schema described here: http://bender.io/2016/09/18/dynamic-pivot-tables-with-json-and-postgresql
---------------------------------------------------------------------
-- Assumes book, customer, and sale tables in the requisite format
---------------------------------------------------------------------
INSERT INTO book VALUES (1, 'The Martian', 'Andy Weir', 2014, 'Science Fiction', 14.88);
INSERT INTO book VALUES (2, 'Augustus: First Emperor of Rome', 'Adrian Goldsworthy', 2014, 'History', 24.29);
INSERT INTO book VALUES (3, 'Ready Player One', 'Ernest Cline', 2011, 'Science Fiction', 9.69);
INSERT INTO book VALUES (4, 'The Hitchhiker''s Guide to the Galaxy', 'Douglas Adams', 1979, 'Science Fiction', 7.19);
INSERT INTO book VALUES (5, 'The Girl with All the Gifts', 'M. R. Carey', 2014, 'Dystopian Fiction', 11.48);
INSERT INTO book VALUES (6, 'Predictably Irrational', 'Dan Ariely', 2008, 'Behavioral Economics', 9.52);
INSERT INTO book VALUES (7, 'Steve Jobs', 'Walter Isaacson', 2011, 'Biography', 11.99);
INSERT INTO book VALUES (8, 'Elon Musk: Tesla, SpaceX, and the Quest for a Fantastic Future', 'Ashlee Vance', 2015, 'Biography', 17.84);
INSERT INTO book VALUES (9, 'Misbehaving: The Making of Behavioral Economics', 'Richard H. Thaler', 2015, 'Behavioral Economics', 10.06);
INSERT INTO book VALUES (10, 'Caesar: Life of a Colossus', 'Adrian Goldsworthy', 2008, 'History', 17.40);
INSERT INTO book VALUES (11, 'Fear the Sky (The Fear Saga Book 1)', 'Stephen Moss', 2014, 'Science Fiction', 13.99);
INSERT INTO book VALUES (12, 'Breaking the Chains of Gravity: The Story of Spaceflight before NASA', 'Amy Shira Teitel', 2015, 'Science', 16.20);
INSERT INTO customer
SELECT
id,
(ARRAY['Jessica', 'Ashley', 'Emily', 'Samantha', 'Sarah', 'Michael', 'Matthew', 'Christopher', 'Jacob', 'Richard'])[TRUNC(RANDOM()*10)+1],
(ARRAY['Smith', 'Johnson', 'Williams', 'Jones', 'Brown', 'Davis', 'Miller', 'Wilson', 'Moore', 'Taylor', 'Anderson', 'Jackson', 'Lopez'])[TRUNC(RANDOM()*13)+1],
(ARRAY['AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DE', 'FL', 'GA', 'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY'])[TRUNC(RANDOM()*50)+1]
FROM generate_series(1, 100) AS id;
INSERT INTO sale
SELECT
sale_id,
TRUNC(RANDOM()*12)+1 AS book_id,
TRUNC(RANDOM()*100)+1 AS customer_id,
date (date '2010-01-01' + random() * (timestamp '2016-12-31' - timestamp '2010-01-01'))
FROM generate_series(1,10000) AS sale_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment