wpug-demo-2015-08-20.sql

-- Demo of PostgreSQL 9.5 features for Wellington PostgreSQL User's Group
-- The following is not necessarily in the right order or exactly how I ran it,
-- I mixed it up a bit when presenting!

DROP TABLE IF EXISTS country CASCADE;

DROP TABLE IF EXISTS sales_per_person CASCADE;

DROP TABLE IF EXISTS film CASCADE;

DROP TABLE IF EXISTS work_queue CASCADE;

DROP TABLE IF EXISTS sample;

--- ON CONFLICT

CREATE TABLE country (
 id VARCHAR(2) PRIMARY KEY,
 name TEXT NOT NULL,
 population INTEGER NOT NULL
);

INSERT INTO country (id, name, population)
VALUES ('NZ', 'New Zealand', 4400000),
       ('AU', 'Australia', 23000000);

SELECT * FROM country;

INSERT INTO country (id, name, population)
VALUES ('NZ', 'New Zealand', 4500000);

UPDATE country
   SET population = 64000000
 WHERE id = 'GB';

INSERT INTO country (id, name, population)
VALUES ('NZ', 'New Zealand', 4500000)
ON CONFLICT DO NOTHING;

INSERT INTO country (id, name, population)
VALUES ('NZ', 'New Zealand', 4700000),
       ('FR', 'France', 66000000)
ON CONFLICT ON CONSTRAINT country_pkey
DO UPDATE SET population = excluded.population;

-- GROUPING SETS

--- ROLLUP

CREATE TABLE sales_per_person (
 country TEXT REFERENCES country(id),
 city TEXT NOT NULL,
 salesperson TEXT NOT NULL,
 widgets_sold INTEGER NOT NULL,
 UNIQUE (country, city, salesperson)
);

INSERT INTO sales_per_person (country, city, salesperson, widgets_sold)
VALUES ('NZ', 'Wellington', 'Alice', 3),
       ('NZ', 'Wellington', 'Ben', 1),
       ('NZ', 'Auckland', 'Chris', 4),
       ('AU', 'Sydney', 'Derek', 1),
       ('AU', 'Sydney', 'Eric', 5),
       ('AU', 'Melbourne', 'Fran', 9);

SELECT *
  FROM sales_per_person
 ORDER BY country, city, salesperson;

SELECT country, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY country
 ORDER BY country;

SELECT country, city, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY country, city
 ORDER BY country, city;

SELECT country, city, salesperson, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY country, city, salesperson
 ORDER BY country, city, salesperson;

WITH results(country, city, salesperson) AS (
SELECT NULL, NULL, NULL, SUM(widgets_sold)
  FROM sales_per_person
UNION ALL
SELECT country, NULL, NULL, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY country
UNION ALL
SELECT country, city, NULL, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY country, city
UNION ALL
SELECT country, city, salesperson, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY country, city, salesperson
)
SELECT *
  FROM results
 ORDER BY country, city, salesperson;

SELECT country, city, salesperson, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY ROLLUP (country, city, salesperson)
 ORDER BY country, city, salesperson;

--- CUBE

CREATE TABLE film (
  name TEXT,
  country TEXT,
  genre TEXT,
  adam_sandler BOOLEAN,
  box_office INTEGER
);

INSERT INTO film (name, country, genre, adam_sandler, box_office)
VALUES ('The Return of Foo', 'US', 'horror', true, 42),
       ('The Return of the Return of Foo', 'US', 'horror', false, 7),
       ('Java Strikes Back', 'GB', 'comedy', true, 8),
       ('NullPointerException', 'NZ', 'romance', false, 2),
       ('Deep Stack Trace 9', 'US', 'adventure', true, 8);

SELECT country, genre, adam_sandler, SUM(box_office)
  FROM film
 GROUP BY CUBE (country, genre, adam_sandler)
 ORDER BY country, genre, adam_sandler;

-- GROUPING SETS

SELECT country, city, salesperson, SUM(widgets_sold)
  FROM sales_per_person
 GROUP BY GROUPING SETS ((country, city, salesperson),
       	  	   	 (country, city),
			 (country),
			 ())
 ORDER BY country, city, salesperson;

SELECT country, genre, adam_sandler, SUM(box_office)
  FROM film
 GROUP BY GROUPING SETS ((country, genre, adam_sandler),
       	  	   	 (country, genre),
			 (country, adam_sandler),
			 (genre, adam_sandler),
			 (country),
			 (genre),
			 (adam_sandler),
			 ())
 ORDER BY country, genre, adam_sandler;
  
-- JSON & JSONB

CREATE TABLE document (data JSON);

INSERT INTO document
VALUES ('{ "name": "Alice", "age": 114 }'),
       ('{ "name": "Bob", "age": 4 }'),
       ('{ "engine": "v8", "transmission": "manual" }');

INSERT INTO document
VALUES ('{ Ceci n''est pas du JSON');

SELECT * FROM document;

SELECT data, data->>'name' AS name FROM document;

SELECT data FROM document WHERE data->>'transmission' = 'manual';

CREATE TABLE document2 (data JSONB);

INSERT INTO document2
VALUES ('{ "name": "Alice", "age": 114 }'),
       ('{ "name": "Bob", "age": 4 }'),
       ('{ "engine": "v8", "transmission": "manual" }');

SELECT * FROM document2;

UPDATE document2
   SET data = jsonb_set(data, '{"recipe"}', '{"ingredients": ["icecream", "sauce"]}')
 WHERE data->>'transmission' = 'manual';

SELECT * FROM document2;

UPDATE document2
   SET data = jsonb_set(data, '{"recipe", "ingredients"}', '["icecream", "sauce", "cherry"]')
 WHERE data->>'transmission' = 'manual';

SELECT * FROM document2;

DELETE FROM document2;

INSERT INTO document2
SELECT json_build_object('id', generate_series(1, 1000000), 'name', 'the_borg');

INSERT INTO document2
VALUES ('{"id": -1, "name": "foo", "legs": [ "left", "right" ]}');

SELECT * FROM document2 LIMIT 20;

SELECT * FROM document2 WHERE data @> '{"id": 42}';

SELECT * FROM document2 WHERE data ? 'legs';

SELECT * FROM document2 WHERE data @> '{"legs": ["left"]}';

SELECT * FROM document2 WHERE data @> '{"legs": ["middle"]}';

EXPLAIN ANALYZE SELECT * FROM document2 WHERE data @> '{"id": 42}';

CREATE INDEX my_index ON document2 USING gin(data);

-- BRIN

CREATE TABLE sample (
  id SERIAL,
  time TIMESTAMPTZ,
  data JSON
);

INSERT INTO sample (time, data)
SELECT generate_series('2000-01-01 00:00:00'::TIMESTAMPTZ, now(), '1 minute'::INTERVAL),
       '{ "windy": "yes" }';

CREATE INDEX sample_time_btree ON sample(time);

CREATE INDEX sample_time_brin ON sample USING brin(time);

DROP INDEX sample_time_btree;

DROP INDEX sample_time_brin;

ANALYZE sample;

EXPLAIN ANALYZE
 SELECT *
   FROM sample
  WHERE time BETWEEN now() - INTERVAL '2 years' AND now() - INTERVAL '1 week';

-- SKIP LOCKED

CREATE TABLE work_queue (
  id INTEGER PRIMARY KEY,
  status TEXT,
  job TEXT
);


INSERT INTO work_queue
VALUES (1, 'NEW', 'take out the rubbish'),
       (2, 'NEW', 'mow the lawn'),
       (3, 'NEW', 'stack the firewood');

BEGIN;

SELECT id, job
  FROM work_queue
 WHERE status = 'NEW'
   FOR UPDATE
 LIMIT 1;

COMMIT;