1 -- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
2 CREATE TEMP TABLE articles (
3 id int CONSTRAINT articles_pkey PRIMARY KEY,
5 title text UNIQUE NOT NULL,
9 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "articles_pkey" for table "articles"
10 NOTICE: CREATE TABLE / UNIQUE will create implicit index "articles_title_key" for table "articles"
11 NOTICE: CREATE TABLE / UNIQUE will create implicit index "articles_body_key" for table "articles"
12 CREATE TEMP TABLE articles_in_category (
16 PRIMARY KEY (article_id, category_id)
18 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "articles_in_category_pkey" for table "articles_in_category"
19 -- test functional dependencies based on primary keys/unique constraints
21 -- group by primary key (OK)
22 SELECT id, keywords, title, body, created
25 id | keywords | title | body | created
26 ----+----------+-------+------+---------
29 -- group by unique not null (fail/todo)
30 SELECT id, keywords, title, body, created
33 ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
34 LINE 1: SELECT id, keywords, title, body, created
36 -- group by unique nullable (fail)
37 SELECT id, keywords, title, body, created
40 ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
41 LINE 1: SELECT id, keywords, title, body, created
43 -- group by something else (fail)
44 SELECT id, keywords, title, body, created
47 ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
48 LINE 1: SELECT id, keywords, title, body, created
51 -- group by primary key (OK)
52 SELECT a.id, a.keywords, a.title, a.body, a.created
53 FROM articles AS a, articles_in_category AS aic
54 WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
56 id | keywords | title | body | created
57 ----+----------+-------+------+---------
60 -- group by something else (fail)
61 SELECT a.id, a.keywords, a.title, a.body, a.created
62 FROM articles AS a, articles_in_category AS aic
63 WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
64 GROUP BY aic.article_id, aic.category_id;
65 ERROR: column "a.id" must appear in the GROUP BY clause or be used in an aggregate function
66 LINE 1: SELECT a.id, a.keywords, a.title, a.body, a.created
69 -- group by left table's primary key (OK)
70 SELECT a.id, a.keywords, a.title, a.body, a.created
71 FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
72 WHERE aic.category_id in (14,62,70,53,138)
74 id | keywords | title | body | created
75 ----+----------+-------+------+---------
78 -- group by something else (fail)
79 SELECT a.id, a.keywords, a.title, a.body, a.created
80 FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
81 WHERE aic.category_id in (14,62,70,53,138)
82 GROUP BY aic.article_id, aic.category_id;
83 ERROR: column "a.id" must appear in the GROUP BY clause or be used in an aggregate function
84 LINE 1: SELECT a.id, a.keywords, a.title, a.body, a.created
86 -- group by right table's (composite) primary key (OK)
88 FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
89 WHERE aic.category_id in (14,62,70,53,138)
90 GROUP BY aic.category_id, aic.article_id;
95 -- group by right table's partial primary key (fail)
97 FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
98 WHERE aic.category_id in (14,62,70,53,138)
99 GROUP BY aic.article_id;
100 ERROR: column "aic.changed" must appear in the GROUP BY clause or be used in an aggregate function
101 LINE 1: SELECT aic.changed
103 -- example from documentation
104 CREATE TEMP TABLE products (product_id int, name text, price numeric);
105 CREATE TEMP TABLE sales (product_id int, units int);
107 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
108 FROM products p LEFT JOIN sales s USING (product_id)
109 GROUP BY product_id, p.name, p.price;
110 product_id | name | sales
111 ------------+------+-------
115 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
116 FROM products p LEFT JOIN sales s USING (product_id)
118 ERROR: column "p.name" must appear in the GROUP BY clause or be used in an aggregate function
119 LINE 1: SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
121 ALTER TABLE products ADD PRIMARY KEY (product_id);
122 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "products_pkey" for table "products"
124 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
125 FROM products p LEFT JOIN sales s USING (product_id)
127 product_id | name | sales
128 ------------+------+-------
131 -- Drupal example, http://drupal.org/node/555530
132 CREATE TEMP TABLE node (
134 vid integer NOT NULL default '0',
135 type varchar(32) NOT NULL default '',
136 title varchar(128) NOT NULL default '',
137 uid integer NOT NULL default '0',
138 status integer NOT NULL default '1',
139 created integer NOT NULL default '0',
141 PRIMARY KEY (nid, vid)
143 NOTICE: CREATE TABLE will create implicit sequence "node_nid_seq" for serial column "node.nid"
144 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
145 CREATE TEMP TABLE users (
146 uid integer NOT NULL default '0',
147 name varchar(60) NOT NULL default '',
148 pass varchar(32) NOT NULL default '',
153 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
154 NOTICE: CREATE TABLE / UNIQUE will create implicit index "users_name_key" for table "users"
156 SELECT u.uid, u.name FROM node n
157 INNER JOIN users u ON u.uid = n.uid
158 WHERE n.type = 'blog' AND n.status = 1
159 GROUP BY u.uid, u.name;
165 SELECT u.uid, u.name FROM node n
166 INNER JOIN users u ON u.uid = n.uid
167 WHERE n.type = 'blog' AND n.status = 1
173 -- Check views and dependencies
175 CREATE TEMP VIEW fdv1 AS
176 SELECT id, keywords, title, body, created
179 ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
180 LINE 2: SELECT id, keywords, title, body, created
183 CREATE TEMP VIEW fdv1 AS
184 SELECT id, keywords, title, body, created
188 ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
189 ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it
190 DETAIL: view fdv1 depends on constraint articles_pkey on table articles
191 HINT: Use DROP ... CASCADE to drop the dependent objects too.
193 -- multiple dependencies
194 CREATE TEMP VIEW fdv2 AS
195 SELECT a.id, a.keywords, a.title, aic.category_id, aic.changed
196 FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
197 WHERE aic.category_id in (14,62,70,53,138)
198 GROUP BY a.id, aic.category_id, aic.article_id;
199 ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail
200 ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it
201 DETAIL: view fdv2 depends on constraint articles_pkey on table articles
202 HINT: Use DROP ... CASCADE to drop the dependent objects too.
203 ALTER TABLE articles_in_category DROP CONSTRAINT articles_in_category_pkey RESTRICT; --fail
204 ERROR: cannot drop constraint articles_in_category_pkey on table articles_in_category because other objects depend on it
205 DETAIL: view fdv2 depends on constraint articles_in_category_pkey on table articles_in_category
206 HINT: Use DROP ... CASCADE to drop the dependent objects too.
209 CREATE TEMP VIEW fdv3 AS
210 SELECT id, keywords, title, body, created
214 SELECT id, keywords, title, body, created
217 ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail
218 ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it
219 DETAIL: view fdv3 depends on constraint articles_pkey on table articles
220 HINT: Use DROP ... CASCADE to drop the dependent objects too.
222 CREATE TEMP VIEW fdv4 AS
223 SELECT * FROM articles WHERE title IN (SELECT title FROM articles GROUP BY id);
224 ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail
225 ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it
226 DETAIL: view fdv4 depends on constraint articles_pkey on table articles
227 HINT: Use DROP ... CASCADE to drop the dependent objects too.
229 -- prepared query plans: this results in failure on reuse
231 SELECT id, keywords, title, body, created
235 id | keywords | title | body | created
236 ----+----------+-------+------+---------
239 ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
241 ERROR: column "articles.keywords" must appear in the GROUP BY clause or be used in an aggregate function