src/test/regress/sql/inherit.sql

   1 --
   2 -- Test inheritance features
   3 --
   4 CREATE TABLE a (aa TEXT);
   5 CREATE TABLE b (bb TEXT) INHERITS (a);
   6 CREATE TABLE c (cc TEXT) INHERITS (a);
   7 CREATE TABLE d (dd TEXT) INHERITS (b,c,a);
   8
   9 INSERT INTO a(aa) VALUES('aaa');
  10 INSERT INTO a(aa) VALUES('aaaa');
  11 INSERT INTO a(aa) VALUES('aaaaa');
  12 INSERT INTO a(aa) VALUES('aaaaaa');
  13 INSERT INTO a(aa) VALUES('aaaaaaa');
  14 INSERT INTO a(aa) VALUES('aaaaaaaa');
  15
  16 INSERT INTO b(aa) VALUES('bbb');
  17 INSERT INTO b(aa) VALUES('bbbb');
  18 INSERT INTO b(aa) VALUES('bbbbb');
  19 INSERT INTO b(aa) VALUES('bbbbbb');
  20 INSERT INTO b(aa) VALUES('bbbbbbb');
  21 INSERT INTO b(aa) VALUES('bbbbbbbb');
  22
  23 INSERT INTO c(aa) VALUES('ccc');
  24 INSERT INTO c(aa) VALUES('cccc');
  25 INSERT INTO c(aa) VALUES('ccccc');
  26 INSERT INTO c(aa) VALUES('cccccc');
  27 INSERT INTO c(aa) VALUES('ccccccc');
  28 INSERT INTO c(aa) VALUES('cccccccc');
  29
  30 INSERT INTO d(aa) VALUES('ddd');
  31 INSERT INTO d(aa) VALUES('dddd');
  32 INSERT INTO d(aa) VALUES('ddddd');
  33 INSERT INTO d(aa) VALUES('dddddd');
  34 INSERT INTO d(aa) VALUES('ddddddd');
  35 INSERT INTO d(aa) VALUES('dddddddd');
  36
  37 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  38 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  39 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  40 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  41 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  42 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  43 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  44 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
  45
  46 UPDATE a SET aa='zzzz' WHERE aa='aaaa';
  47 UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
  48 UPDATE b SET aa='zzz' WHERE aa='aaa';
  49 UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
  50 UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
  51
  52 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  53 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  54 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  55 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  56 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  57 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  58 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  59 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
  60
  61 UPDATE b SET aa='new';
  62
  63 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  64 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  65 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  66 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  67 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  68 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  69 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  70 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
  71
  72 UPDATE a SET aa='new';
  73
  74 DELETE FROM ONLY c WHERE aa='new';
  75
  76 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  77 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  78 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  79 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  80 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  81 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  82 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  83 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
  84
  85 DELETE FROM a;
  86
  87 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
  88 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
  89 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
  90 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
  91 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
  92 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
  93 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
  94 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
  95
  96 -- Confirm PRIMARY KEY adds NOT NULL constraint to child table
  97 CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
  98 INSERT INTO z VALUES (NULL, 'text'); -- should fail
  99
 100 -- Check UPDATE with inherited target and an inherited source table
 101 create temp table foo(f1 int, f2 int);
 102 create temp table foo2(f3 int) inherits (foo);
 103 create temp table bar(f1 int, f2 int);
 104 create temp table bar2(f3 int) inherits (bar);
 105
 106 insert into foo values(1,1);
 107 insert into foo values(3,3);
 108 insert into foo2 values(2,2,2);
 109 insert into foo2 values(3,3,3);
 110 insert into bar values(1,1);
 111 insert into bar values(2,2);
 112 insert into bar values(3,3);
 113 insert into bar values(4,4);
 114 insert into bar2 values(1,1,1);
 115 insert into bar2 values(2,2,2);
 116 insert into bar2 values(3,3,3);
 117 insert into bar2 values(4,4,4);
 118
 119 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
 120
 121 SELECT relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid
 122 order by 1,2;
 123
 124
 125 /* Test inheritance of structure (LIKE) */
 126 CREATE TABLE inhx (xx text DEFAULT 'text');
 127
 128 /*
 129  * Test double inheritance
 130  *
 131  * Ensure that defaults are NOT included unless
 132  * INCLUDING DEFAULTS is specified
 133  */
 134 CREATE TABLE inhe (ee text, LIKE inhx) inherits (b);
 135 INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4');
 136 SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */
 137 SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */
 138 SELECT * FROM b; /* Has ee entry */
 139 SELECT * FROM a; /* Has ee entry */
 140
 141 CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */
 142
 143 CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
 144 INSERT INTO inhf DEFAULT VALUES;
 145 SELECT * FROM inhf; /* Single entry with value 'text' */
 146
 147 ALTER TABLE inhx add constraint foo CHECK (xx = 'text');
 148 ALTER TABLE inhx ADD PRIMARY KEY (xx);
 149 CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */
 150 INSERT INTO inhg VALUES ('foo');
 151 DROP TABLE inhg;
 152 CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
 153 INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
 154 INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
 155 INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
 156 SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
 157 DROP TABLE inhg;
 158
 159 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
 160 INSERT INTO inhg VALUES (5, 10);
 161 INSERT INTO inhg VALUES (20, 10); -- should fail
 162 DROP TABLE inhg;
 163 /* Multiple primary keys creation should fail */
 164 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */
 165 CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE);
 166 CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test';
 167 /* Ok to create multiple unique indexes */
 168 CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES);
 169 INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10);
 170 INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15);
 171 INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
 172 DROP TABLE inhg;
 173 DROP TABLE inhz;
 174
 175 -- Test changing the type of inherited columns
 176 insert into d values('test','one','two','three');
 177 alter table a alter column aa type integer using bit_length(aa);
 178 select * from d;
 179
 180 -- Tests for casting between the rowtypes of parent and child
 181 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
 182 create table base (i integer);
 183 create table derived () inherits (base);
 184 insert into derived (i) values (0);
 185 select derived::base from derived;
 186 drop table derived;
 187 drop table base;
 188
 189 create table p1(ff1 int);
 190 create table p2(f1 text);
 191 create function p2text(p2) returns text as 'select $1.f1' language sql;
 192 create table c1(f3 int) inherits(p1,p2);
 193 insert into c1 values(123456789, 'hi', 42);
 194 select p2text(c1.*) from c1;
 195 drop function p2text(p2);
 196 drop table c1;
 197 drop table p2;
 198 drop table p1;
 199
 200 CREATE TABLE ac (aa TEXT);
 201 alter table ac add constraint ac_check check (aa is not null);
 202 CREATE TABLE bc (bb TEXT) INHERITS (ac);
 203 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 204
 205 insert into ac (aa) values (NULL);
 206 insert into bc (aa) values (NULL);
 207
 208 alter table bc drop constraint ac_check;  -- fail, disallowed
 209 alter table ac drop constraint ac_check;
 210 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 211
 212 -- try the unnamed-constraint case
 213 alter table ac add check (aa is not null);
 214 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 215
 216 insert into ac (aa) values (NULL);
 217 insert into bc (aa) values (NULL);
 218
 219 alter table bc drop constraint ac_aa_check;  -- fail, disallowed
 220 alter table ac drop constraint ac_aa_check;
 221 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 222
 223 alter table ac add constraint ac_check check (aa is not null);
 224 alter table bc no inherit ac;
 225 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 226 alter table bc drop constraint ac_check;
 227 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 228 alter table ac drop constraint ac_check;
 229 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 230
 231 drop table bc;
 232 drop table ac;
 233
 234 create table ac (a int constraint check_a check (a <> 0));
 235 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
 236 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 237
 238 drop table bc;
 239 drop table ac;
 240
 241 create table ac (a int constraint check_a check (a <> 0));
 242 create table bc (b int constraint check_b check (b <> 0));
 243 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
 244 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
 245
 246 alter table cc no inherit bc;
 247 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
 248
 249 drop table cc;
 250 drop table bc;
 251 drop table ac;
 252
 253 create table p1(f1 int);
 254 create table p2(f2 int);
 255 create table c1(f3 int) inherits(p1,p2);
 256 insert into c1 values(1,-1,2);
 257 alter table p2 add constraint cc check (f2>0);  -- fail
 258 alter table p2 add check (f2>0);  -- check it without a name, too
 259 delete from c1;
 260 insert into c1 values(1,1,2);
 261 alter table p2 add check (f2>0);
 262 insert into c1 values(1,-1,2);  -- fail
 263 create table c2(f3 int) inherits(p1,p2);
 264 \d c2
 265 create table c3 (f4 int) inherits(c1,c2);
 266 \d c3
 267 drop table p1 cascade;
 268 drop table p2 cascade;
 269
 270 create table pp1 (f1 int);
 271 create table cc1 (f2 text, f3 int) inherits (pp1);
 272 alter table pp1 add column a1 int check (a1 > 0);
 273 \d cc1
 274 create table cc2(f4 float) inherits(pp1,cc1);
 275 \d cc2
 276 alter table pp1 add column a2 int check (a2 > 0);
 277 \d cc2
 278 drop table pp1 cascade;