1-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/ 2 3CREATE TEMP TABLE articles ( 4 id int CONSTRAINT articles_pkey PRIMARY KEY, 5 keywords text, 6 title text UNIQUE NOT NULL, 7 body text UNIQUE, 8 created date 9); 10 11CREATE TEMP TABLE articles_in_category ( 12 article_id int, 13 category_id int, 14 changed date, 15 PRIMARY KEY (article_id, category_id) 16); 17 18-- test functional dependencies based on primary keys/unique constraints 19 20-- base tables 21 22-- group by primary key (OK) 23SELECT id, keywords, title, body, created 24FROM articles 25GROUP BY id; 26 27-- group by unique not null (fail/todo) 28SELECT id, keywords, title, body, created 29FROM articles 30GROUP BY title; 31 32-- group by unique nullable (fail) 33SELECT id, keywords, title, body, created 34FROM articles 35GROUP BY body; 36 37-- group by something else (fail) 38SELECT id, keywords, title, body, created 39FROM articles 40GROUP BY keywords; 41 42-- multiple tables 43 44-- group by primary key (OK) 45SELECT a.id, a.keywords, a.title, a.body, a.created 46FROM articles AS a, articles_in_category AS aic 47WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138) 48GROUP BY a.id; 49 50-- group by something else (fail) 51SELECT a.id, a.keywords, a.title, a.body, a.created 52FROM articles AS a, articles_in_category AS aic 53WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138) 54GROUP BY aic.article_id, aic.category_id; 55 56-- JOIN syntax 57 58-- group by left table's primary key (OK) 59SELECT a.id, a.keywords, a.title, a.body, a.created 60FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id 61WHERE aic.category_id in (14,62,70,53,138) 62GROUP BY a.id; 63 64-- group by something else (fail) 65SELECT a.id, a.keywords, a.title, a.body, a.created 66FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id 67WHERE aic.category_id in (14,62,70,53,138) 68GROUP BY aic.article_id, aic.category_id; 69 70-- group by right table's (composite) primary key (OK) 71SELECT aic.changed 72FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id 73WHERE aic.category_id in (14,62,70,53,138) 74GROUP BY aic.category_id, aic.article_id; 75 76-- group by right table's partial primary key (fail) 77SELECT aic.changed 78FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id 79WHERE aic.category_id in (14,62,70,53,138) 80GROUP BY aic.article_id; 81 82 83-- example from documentation 84 85CREATE TEMP TABLE products (product_id int, name text, price numeric); 86CREATE TEMP TABLE sales (product_id int, units int); 87 88-- OK 89SELECT product_id, p.name, (sum(s.units) * p.price) AS sales 90 FROM products p LEFT JOIN sales s USING (product_id) 91 GROUP BY product_id, p.name, p.price; 92 93-- fail 94SELECT product_id, p.name, (sum(s.units) * p.price) AS sales 95 FROM products p LEFT JOIN sales s USING (product_id) 96 GROUP BY product_id; 97 98ALTER TABLE products ADD PRIMARY KEY (product_id); 99 100-- OK now 101SELECT product_id, p.name, (sum(s.units) * p.price) AS sales 102 FROM products p LEFT JOIN sales s USING (product_id) 103 GROUP BY product_id; 104 105 106-- Drupal example, http://drupal.org/node/555530 107 108CREATE TEMP TABLE node ( 109 nid SERIAL, 110 vid integer NOT NULL default '0', 111 type varchar(32) NOT NULL default '', 112 title varchar(128) NOT NULL default '', 113 uid integer NOT NULL default '0', 114 status integer NOT NULL default '1', 115 created integer NOT NULL default '0', 116 -- snip 117 PRIMARY KEY (nid, vid) 118); 119 120CREATE TEMP TABLE users ( 121 uid integer NOT NULL default '0', 122 name varchar(60) NOT NULL default '', 123 pass varchar(32) NOT NULL default '', 124 -- snip 125 PRIMARY KEY (uid), 126 UNIQUE (name) 127); 128 129-- OK 130SELECT u.uid, u.name FROM node n 131INNER JOIN users u ON u.uid = n.uid 132WHERE n.type = 'blog' AND n.status = 1 133GROUP BY u.uid, u.name; 134 135-- OK 136SELECT u.uid, u.name FROM node n 137INNER JOIN users u ON u.uid = n.uid 138WHERE n.type = 'blog' AND n.status = 1 139GROUP BY u.uid; 140 141 142-- Check views and dependencies 143 144-- fail 145CREATE TEMP VIEW fdv1 AS 146SELECT id, keywords, title, body, created 147FROM articles 148GROUP BY body; 149 150-- OK 151CREATE TEMP VIEW fdv1 AS 152SELECT id, keywords, title, body, created 153FROM articles 154GROUP BY id; 155 156-- fail 157ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; 158 159DROP VIEW fdv1; 160 161 162-- multiple dependencies 163CREATE TEMP VIEW fdv2 AS 164SELECT a.id, a.keywords, a.title, aic.category_id, aic.changed 165FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id 166WHERE aic.category_id in (14,62,70,53,138) 167GROUP BY a.id, aic.category_id, aic.article_id; 168 169ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail 170ALTER TABLE articles_in_category DROP CONSTRAINT articles_in_category_pkey RESTRICT; --fail 171 172DROP VIEW fdv2; 173 174 175-- nested queries 176 177CREATE TEMP VIEW fdv3 AS 178SELECT id, keywords, title, body, created 179FROM articles 180GROUP BY id 181UNION 182SELECT id, keywords, title, body, created 183FROM articles 184GROUP BY id; 185 186ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail 187 188DROP VIEW fdv3; 189 190 191CREATE TEMP VIEW fdv4 AS 192SELECT * FROM articles WHERE title IN (SELECT title FROM articles GROUP BY id); 193 194ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail 195 196DROP VIEW fdv4; 197 198 199-- prepared query plans: this results in failure on reuse 200 201PREPARE foo AS 202 SELECT id, keywords, title, body, created 203 FROM articles 204 GROUP BY id; 205 206EXECUTE foo; 207 208ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; 209 210EXECUTE foo; -- fail 211