granicus.if.org Git - postgresql/blob - src/test/regress/expected/functional_deps.out

   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,
   4     keywords text,
   5     title text UNIQUE NOT NULL,
   6     body text UNIQUE,
   7     created date
   8 );
   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 (
  13     article_id int,
  14     category_id int,
  15     changed date,
  16     PRIMARY KEY (article_id, category_id)
  17 );
  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
  20 -- base tables
  21 -- group by primary key (OK)
  22 SELECT id, keywords, title, body, created
  23 FROM articles
  24 GROUP BY id;
  25  id | keywords | title | body | created
  26 ----+----------+-------+------+---------
  27 (0 rows)
  28
  29 -- group by unique not null (fail/todo)
  30 SELECT id, keywords, title, body, created
  31 FROM articles
  32 GROUP BY title;
  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
  35                ^
  36 -- group by unique nullable (fail)
  37 SELECT id, keywords, title, body, created
  38 FROM articles
  39 GROUP BY body;
  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
  42                ^
  43 -- group by something else (fail)
  44 SELECT id, keywords, title, body, created
  45 FROM articles
  46 GROUP BY keywords;
  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
  49                ^
  50 -- multiple tables
  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)
  55 GROUP BY a.id;
  56  id | keywords | title | body | created
  57 ----+----------+-------+------+---------
  58 (0 rows)
  59
  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
  67                ^
  68 -- JOIN syntax
  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)
  73 GROUP BY a.id;
  74  id | keywords | title | body | created
  75 ----+----------+-------+------+---------
  76 (0 rows)
  77
  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
  85                ^
  86 -- group by right table's (composite) primary key (OK)
  87 SELECT aic.changed
  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;
  91  changed
  92 ---------
  93 (0 rows)
  94
  95 -- group by right table's partial primary key (fail)
  96 SELECT aic.changed
  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
 102                ^
 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);
 106 -- OK
 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 ------------+------+-------
 112 (0 rows)
 113
 114 -- fail
 115 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
 116     FROM products p LEFT JOIN sales s USING (product_id)
 117     GROUP BY 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
 120                            ^
 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"
 123 -- OK now
 124 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
 125     FROM products p LEFT JOIN sales s USING (product_id)
 126     GROUP BY product_id;
 127  product_id | name | sales
 128 ------------+------+-------
 129 (0 rows)
 130
 131 -- Drupal example, http://drupal.org/node/555530
 132 CREATE TEMP TABLE node (
 133     nid SERIAL,
 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',
 140     -- snip
 141     PRIMARY KEY (nid, vid)
 142 );
 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 '',
 149     -- snip
 150     PRIMARY KEY (uid),
 151     UNIQUE (name)
 152 );
 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"
 155 -- OK
 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;
 160  uid | name
 161 -----+------
 162 (0 rows)
 163
 164 -- OK
 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
 168 GROUP BY u.uid;
 169  uid | name
 170 -----+------
 171 (0 rows)
 172
 173 -- Check views and dependencies
 174 -- fail
 175 CREATE TEMP VIEW fdv1 AS
 176 SELECT id, keywords, title, body, created
 177 FROM articles
 178 GROUP BY body;
 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
 181                ^
 182 -- OK
 183 CREATE TEMP VIEW fdv1 AS
 184 SELECT id, keywords, title, body, created
 185 FROM articles
 186 GROUP BY id;
 187 -- fail
 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.
 192 DROP VIEW fdv1;
 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.
 207 DROP VIEW fdv2;
 208 -- nested queries
 209 CREATE TEMP VIEW fdv3 AS
 210 SELECT id, keywords, title, body, created
 211 FROM articles
 212 GROUP BY id
 213 UNION
 214 SELECT id, keywords, title, body, created
 215 FROM articles
 216 GROUP BY id;
 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.
 221 DROP VIEW fdv3;
 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.
 228 DROP VIEW fdv4;
 229 -- prepared query plans: this results in failure on reuse
 230 PREPARE foo AS
 231   SELECT id, keywords, title, body, created
 232   FROM articles
 233   GROUP BY id;
 234 EXECUTE foo;
 235  id | keywords | title | body | created
 236 ----+----------+-------+------+---------
 237 (0 rows)
 238
 239 ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
 240 EXECUTE foo;  -- fail
 241 ERROR:  column "articles.keywords" must appear in the GROUP BY clause or be used in an aggregate function