| blob | 100edb35396dc63c7e62e20565b910eb68c24f30 |
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 NOTICE: merging multiple inherited definitions of column "aa"
9 NOTICE: merging multiple inherited definitions of column "aa"
10 INSERT INTO a(aa) VALUES('aaa');
11 INSERT INTO a(aa) VALUES('aaaa');
12 INSERT INTO a(aa) VALUES('aaaaa');
13 INSERT INTO a(aa) VALUES('aaaaaa');
14 INSERT INTO a(aa) VALUES('aaaaaaa');
15 INSERT INTO a(aa) VALUES('aaaaaaaa');
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 INSERT INTO c(aa) VALUES('ccc');
23 INSERT INTO c(aa) VALUES('cccc');
24 INSERT INTO c(aa) VALUES('ccccc');
25 INSERT INTO c(aa) VALUES('cccccc');
26 INSERT INTO c(aa) VALUES('ccccccc');
27 INSERT INTO c(aa) VALUES('cccccccc');
28 INSERT INTO d(aa) VALUES('ddd');
29 INSERT INTO d(aa) VALUES('dddd');
30 INSERT INTO d(aa) VALUES('ddddd');
31 INSERT INTO d(aa) VALUES('dddddd');
32 INSERT INTO d(aa) VALUES('ddddddd');
33 INSERT INTO d(aa) VALUES('dddddddd');
34 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
35 relname | aa
36 ---------+----------
37 a | aaa
38 a | aaaa
39 a | aaaaa
40 a | aaaaaa
41 a | aaaaaaa
42 a | aaaaaaaa
43 b | bbb
44 b | bbbb
45 b | bbbbb
46 b | bbbbbb
47 b | bbbbbbb
48 b | bbbbbbbb
49 c | ccc
50 c | cccc
51 c | ccccc
52 c | cccccc
53 c | ccccccc
54 c | cccccccc
55 d | ddd
56 d | dddd
57 d | ddddd
58 d | dddddd
59 d | ddddddd
60 d | dddddddd
61 (24 rows)
63 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
64 relname | aa | bb
65 ---------+----------+----
66 b | bbb |
67 b | bbbb |
68 b | bbbbb |
69 b | bbbbbb |
70 b | bbbbbbb |
71 b | bbbbbbbb |
72 d | ddd |
73 d | dddd |
74 d | ddddd |
75 d | dddddd |
76 d | ddddddd |
77 d | dddddddd |
78 (12 rows)
80 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
81 relname | aa | cc
82 ---------+----------+----
83 c | ccc |
84 c | cccc |
85 c | ccccc |
86 c | cccccc |
87 c | ccccccc |
88 c | cccccccc |
89 d | ddd |
90 d | dddd |
91 d | ddddd |
92 d | dddddd |
93 d | ddddddd |
94 d | dddddddd |
95 (12 rows)
97 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
98 relname | aa | bb | cc | dd
99 ---------+----------+----+----+----
100 d | ddd | | |
101 d | dddd | | |
102 d | ddddd | | |
103 d | dddddd | | |
104 d | ddddddd | | |
105 d | dddddddd | | |
106 (6 rows)
108 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
109 relname | aa
110 ---------+----------
111 a | aaa
112 a | aaaa
113 a | aaaaa
114 a | aaaaaa
115 a | aaaaaaa
116 a | aaaaaaaa
117 (6 rows)
119 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
120 relname | aa | bb
121 ---------+----------+----
122 b | bbb |
123 b | bbbb |
124 b | bbbbb |
125 b | bbbbbb |
126 b | bbbbbbb |
127 b | bbbbbbbb |
128 (6 rows)
130 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
131 relname | aa | cc
132 ---------+----------+----
133 c | ccc |
134 c | cccc |
135 c | ccccc |
136 c | cccccc |
137 c | ccccccc |
138 c | cccccccc |
139 (6 rows)
141 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
142 relname | aa | bb | cc | dd
143 ---------+----------+----+----+----
144 d | ddd | | |
145 d | dddd | | |
146 d | ddddd | | |
147 d | dddddd | | |
148 d | ddddddd | | |
149 d | dddddddd | | |
150 (6 rows)
152 UPDATE a SET aa='zzzz' WHERE aa='aaaa';
153 UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
154 UPDATE b SET aa='zzz' WHERE aa='aaa';
155 UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
156 UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
157 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
158 relname | aa
159 ---------+----------
160 a | zzzz
161 a | zzzzz
162 a | zzzzzz
163 a | zzzzzz
164 a | zzzzzz
165 a | zzzzzz
166 b | bbb
167 b | bbbb
168 b | bbbbb
169 b | bbbbbb
170 b | bbbbbbb
171 b | bbbbbbbb
172 c | ccc
173 c | cccc
174 c | ccccc
175 c | cccccc
176 c | ccccccc
177 c | cccccccc
178 d | ddd
179 d | dddd
180 d | ddddd
181 d | dddddd
182 d | ddddddd
183 d | dddddddd
184 (24 rows)
186 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
187 relname | aa | bb
188 ---------+----------+----
189 b | bbb |
190 b | bbbb |
191 b | bbbbb |
192 b | bbbbbb |
193 b | bbbbbbb |
194 b | bbbbbbbb |
195 d | ddd |
196 d | dddd |
197 d | ddddd |
198 d | dddddd |
199 d | ddddddd |
200 d | dddddddd |
201 (12 rows)
203 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
204 relname | aa | cc
205 ---------+----------+----
206 c | ccc |
207 c | cccc |
208 c | ccccc |
209 c | cccccc |
210 c | ccccccc |
211 c | cccccccc |
212 d | ddd |
213 d | dddd |
214 d | ddddd |
215 d | dddddd |
216 d | ddddddd |
217 d | dddddddd |
218 (12 rows)
220 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
221 relname | aa | bb | cc | dd
222 ---------+----------+----+----+----
223 d | ddd | | |
224 d | dddd | | |
225 d | ddddd | | |
226 d | dddddd | | |
227 d | ddddddd | | |
228 d | dddddddd | | |
229 (6 rows)
231 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
232 relname | aa
233 ---------+--------
234 a | zzzz
235 a | zzzzz
236 a | zzzzzz
237 a | zzzzzz
238 a | zzzzzz
239 a | zzzzzz
240 (6 rows)
242 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
243 relname | aa | bb
244 ---------+----------+----
245 b | bbb |
246 b | bbbb |
247 b | bbbbb |
248 b | bbbbbb |
249 b | bbbbbbb |
250 b | bbbbbbbb |
251 (6 rows)
253 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
254 relname | aa | cc
255 ---------+----------+----
256 c | ccc |
257 c | cccc |
258 c | ccccc |
259 c | cccccc |
260 c | ccccccc |
261 c | cccccccc |
262 (6 rows)
264 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
265 relname | aa | bb | cc | dd
266 ---------+----------+----+----+----
267 d | ddd | | |
268 d | dddd | | |
269 d | ddddd | | |
270 d | dddddd | | |
271 d | ddddddd | | |
272 d | dddddddd | | |
273 (6 rows)
275 UPDATE b SET aa='new';
276 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
277 relname | aa
278 ---------+----------
279 a | zzzz
280 a | zzzzz
281 a | zzzzzz
282 a | zzzzzz
283 a | zzzzzz
284 a | zzzzzz
285 b | new
286 b | new
287 b | new
288 b | new
289 b | new
290 b | new
291 c | ccc
292 c | cccc
293 c | ccccc
294 c | cccccc
295 c | ccccccc
296 c | cccccccc
297 d | new
298 d | new
299 d | new
300 d | new
301 d | new
302 d | new
303 (24 rows)
305 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
306 relname | aa | bb
307 ---------+-----+----
308 b | new |
309 b | new |
310 b | new |
311 b | new |
312 b | new |
313 b | new |
314 d | new |
315 d | new |
316 d | new |
317 d | new |
318 d | new |
319 d | new |
320 (12 rows)
322 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
323 relname | aa | cc
324 ---------+----------+----
325 c | ccc |
326 c | cccc |
327 c | ccccc |
328 c | cccccc |
329 c | ccccccc |
330 c | cccccccc |
331 d | new |
332 d | new |
333 d | new |
334 d | new |
335 d | new |
336 d | new |
337 (12 rows)
339 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
340 relname | aa | bb | cc | dd
341 ---------+-----+----+----+----
342 d | new | | |
343 d | new | | |
344 d | new | | |
345 d | new | | |
346 d | new | | |
347 d | new | | |
348 (6 rows)
350 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
351 relname | aa
352 ---------+--------
353 a | zzzz
354 a | zzzzz
355 a | zzzzzz
356 a | zzzzzz
357 a | zzzzzz
358 a | zzzzzz
359 (6 rows)
361 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
362 relname | aa | bb
363 ---------+-----+----
364 b | new |
365 b | new |
366 b | new |
367 b | new |
368 b | new |
369 b | new |
370 (6 rows)
372 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
373 relname | aa | cc
374 ---------+----------+----
375 c | ccc |
376 c | cccc |
377 c | ccccc |
378 c | cccccc |
379 c | ccccccc |
380 c | cccccccc |
381 (6 rows)
383 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
384 relname | aa | bb | cc | dd
385 ---------+-----+----+----+----
386 d | new | | |
387 d | new | | |
388 d | new | | |
389 d | new | | |
390 d | new | | |
391 d | new | | |
392 (6 rows)
394 UPDATE a SET aa='new';
395 DELETE FROM ONLY c WHERE aa='new';
396 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
397 relname | aa
398 ---------+-----
399 a | new
400 a | new
401 a | new
402 a | new
403 a | new
404 a | new
405 b | new
406 b | new
407 b | new
408 b | new
409 b | new
410 b | new
411 d | new
412 d | new
413 d | new
414 d | new
415 d | new
416 d | new
417 (18 rows)
419 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
420 relname | aa | bb
421 ---------+-----+----
422 b | new |
423 b | new |
424 b | new |
425 b | new |
426 b | new |
427 b | new |
428 d | new |
429 d | new |
430 d | new |
431 d | new |
432 d | new |
433 d | new |
434 (12 rows)
436 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
437 relname | aa | cc
438 ---------+-----+----
439 d | new |
440 d | new |
441 d | new |
442 d | new |
443 d | new |
444 d | new |
445 (6 rows)
447 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
448 relname | aa | bb | cc | dd
449 ---------+-----+----+----+----
450 d | new | | |
451 d | new | | |
452 d | new | | |
453 d | new | | |
454 d | new | | |
455 d | new | | |
456 (6 rows)
458 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
459 relname | aa
460 ---------+-----
461 a | new
462 a | new
463 a | new
464 a | new
465 a | new
466 a | new
467 (6 rows)
469 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
470 relname | aa | bb
471 ---------+-----+----
472 b | new |
473 b | new |
474 b | new |
475 b | new |
476 b | new |
477 b | new |
478 (6 rows)
480 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
481 relname | aa | cc
482 ---------+----+----
483 (0 rows)
485 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
486 relname | aa | bb | cc | dd
487 ---------+-----+----+----+----
488 d | new | | |
489 d | new | | |
490 d | new | | |
491 d | new | | |
492 d | new | | |
493 d | new | | |
494 (6 rows)
496 DELETE FROM a;
497 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
498 relname | aa
499 ---------+----
500 (0 rows)
502 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
503 relname | aa | bb
504 ---------+----+----
505 (0 rows)
507 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
508 relname | aa | cc
509 ---------+----+----
510 (0 rows)
512 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
513 relname | aa | bb | cc | dd
514 ---------+----+----+----+----
515 (0 rows)
517 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
518 relname | aa
519 ---------+----
520 (0 rows)
522 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
523 relname | aa | bb
524 ---------+----+----
525 (0 rows)
527 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
528 relname | aa | cc
529 ---------+----+----
530 (0 rows)
532 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
533 relname | aa | bb | cc | dd
534 ---------+----+----+----+----
535 (0 rows)
537 -- Confirm PRIMARY KEY adds NOT NULL constraint to child table
538 CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
539 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "z_pkey" for table "z"
540 INSERT INTO z VALUES (NULL, 'text'); -- should fail
541 ERROR: null value in column "aa" violates not-null constraint
542 -- Check UPDATE with inherited target and an inherited source table
543 create temp table foo(f1 int, f2 int);
544 create temp table foo2(f3 int) inherits (foo);
545 create temp table bar(f1 int, f2 int);
546 create temp table bar2(f3 int) inherits (bar);
547 insert into foo values(1,1);
548 insert into foo values(3,3);
549 insert into foo2 values(2,2,2);
550 insert into foo2 values(3,3,3);
551 insert into bar values(1,1);
552 insert into bar values(2,2);
553 insert into bar values(3,3);
554 insert into bar values(4,4);
555 insert into bar2 values(1,1,1);
556 insert into bar2 values(2,2,2);
557 insert into bar2 values(3,3,3);
558 insert into bar2 values(4,4,4);
559 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
560 SELECT relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid
561 order by 1,2;
562 relname | f1 | f2
563 ---------+----+-----
564 bar | 1 | 101
565 bar | 2 | 102
566 bar | 3 | 103
567 bar | 4 | 4
568 bar2 | 1 | 101
569 bar2 | 2 | 102
570 bar2 | 3 | 103
571 bar2 | 4 | 4
572 (8 rows)
574 /* Test inheritance of structure (LIKE) */
575 CREATE TABLE inhx (xx text DEFAULT 'text');
576 /*
577 * Test double inheritance
578 *
579 * Ensure that defaults are NOT included unless
580 * INCLUDING DEFAULTS is specified
581 */
582 CREATE TABLE inhe (ee text, LIKE inhx) inherits (b);
583 INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4');
584 SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */
585 aa | bb | ee | xx
586 ---------+---------+----+---------
587 ee-col1 | ee-col2 | | ee-col4
588 (1 row)
590 SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */
591 xx
592 ----
593 (0 rows)
595 SELECT * FROM b; /* Has ee entry */
596 aa | bb
597 ---------+---------
598 ee-col1 | ee-col2
599 (1 row)
601 SELECT * FROM a; /* Has ee entry */
602 aa
603 ---------
604 ee-col1
605 (1 row)
607 CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */
608 ERROR: column "xx" specified more than once
609 CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
610 INSERT INTO inhf DEFAULT VALUES;
611 SELECT * FROM inhf; /* Single entry with value 'text' */
612 xx
613 ------
614 text
615 (1 row)
617 ALTER TABLE inhx add constraint foo CHECK (xx = 'text');
618 ALTER TABLE inhx ADD PRIMARY KEY (xx);
619 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "inhx_pkey" for table "inhx"
620 CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */
621 INSERT INTO inhg VALUES ('foo');
622 DROP TABLE inhg;
623 CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
624 INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
625 INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
626 INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */
627 ERROR: new row for relation "inhg" violates check constraint "foo"
628 SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
629 x | xx | y
630 ---+------+---
631 x | text | y
632 x | text | y
633 (2 rows)
635 DROP TABLE inhg;
636 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
637 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "inhg_pkey" for table "inhg"
638 INSERT INTO inhg VALUES (5, 10);
639 INSERT INTO inhg VALUES (20, 10); -- should fail
640 ERROR: duplicate key value violates unique constraint "inhg_pkey"
641 DROP TABLE inhg;
642 /* Multiple primary keys creation should fail */
643 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */
644 ERROR: multiple primary keys for table "inhg" are not allowed
645 CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE);
646 NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhz_yy_key" for table "inhz"
647 CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test';
648 /* Ok to create multiple unique indexes */
649 CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES);
650 NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_x_key" for table "inhg"
651 NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_yy_key" for table "inhg"
652 INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10);
653 INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15);
654 INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
655 ERROR: duplicate key value violates unique constraint "inhg_x_key"
656 DROP TABLE inhg;
657 DROP TABLE inhz;
658 -- Test changing the type of inherited columns
659 insert into d values('test','one','two','three');
660 alter table a alter column aa type integer using bit_length(aa);
661 select * from d;
662 aa | bb | cc | dd
663 ----+-----+-----+-------
664 32 | one | two | three
665 (1 row)
667 -- Tests for casting between the rowtypes of parent and child
668 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
669 create table base (i integer);
670 create table derived () inherits (base);
671 insert into derived (i) values (0);
672 select derived::base from derived;
673 derived
674 ---------
675 (0)
676 (1 row)
678 drop table derived;
679 drop table base;
680 create table p1(ff1 int);
681 create table p2(f1 text);
682 create function p2text(p2) returns text as 'select $1.f1' language sql;
683 create table c1(f3 int) inherits(p1,p2);
684 insert into c1 values(123456789, 'hi', 42);
685 select p2text(c1.*) from c1;
686 p2text
687 --------
688 hi
689 (1 row)
691 drop function p2text(p2);
692 drop table c1;
693 drop table p2;
694 drop table p1;
695 CREATE TABLE ac (aa TEXT);
696 alter table ac add constraint ac_check check (aa is not null);
697 CREATE TABLE bc (bb TEXT) INHERITS (ac);
698 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;
699 relname | conname | contype | conislocal | coninhcount | consrc
700 ---------+----------+---------+------------+-------------+------------------
701 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
702 bc | ac_check | c | f | 1 | (aa IS NOT NULL)
703 (2 rows)
705 insert into ac (aa) values (NULL);
706 ERROR: new row for relation "ac" violates check constraint "ac_check"
707 insert into bc (aa) values (NULL);
708 ERROR: new row for relation "bc" violates check constraint "ac_check"
709 alter table bc drop constraint ac_check; -- fail, disallowed
710 ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
711 alter table ac drop constraint ac_check;
712 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;
713 relname | conname | contype | conislocal | coninhcount | consrc
714 ---------+---------+---------+------------+-------------+--------
715 (0 rows)
717 -- try the unnamed-constraint case
718 alter table ac add check (aa is not null);
719 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;
720 relname | conname | contype | conislocal | coninhcount | consrc
721 ---------+-------------+---------+------------+-------------+------------------
722 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
723 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
724 (2 rows)
726 insert into ac (aa) values (NULL);
727 ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
728 insert into bc (aa) values (NULL);
729 ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
730 alter table bc drop constraint ac_aa_check; -- fail, disallowed
731 ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
732 alter table ac drop constraint ac_aa_check;
733 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;
734 relname | conname | contype | conislocal | coninhcount | consrc
735 ---------+---------+---------+------------+-------------+--------
736 (0 rows)
738 alter table ac add constraint ac_check check (aa is not null);
739 alter table bc no inherit ac;
740 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;
741 relname | conname | contype | conislocal | coninhcount | consrc
742 ---------+----------+---------+------------+-------------+------------------
743 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
744 bc | ac_check | c | t | 0 | (aa IS NOT NULL)
745 (2 rows)
747 alter table bc drop constraint ac_check;
748 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;
749 relname | conname | contype | conislocal | coninhcount | consrc
750 ---------+----------+---------+------------+-------------+------------------
751 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
752 (1 row)
754 alter table ac drop constraint ac_check;
755 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;
756 relname | conname | contype | conislocal | coninhcount | consrc
757 ---------+---------+---------+------------+-------------+--------
758 (0 rows)
760 drop table bc;
761 drop table ac;
762 create table ac (a int constraint check_a check (a <> 0));
763 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
764 NOTICE: merging column "a" with inherited definition
765 NOTICE: merging constraint "check_a" with inherited definition
766 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;
767 relname | conname | contype | conislocal | coninhcount | consrc
768 ---------+---------+---------+------------+-------------+----------
769 ac | check_a | c | t | 0 | (a <> 0)
770 bc | check_a | c | t | 1 | (a <> 0)
771 bc | check_b | c | t | 0 | (b <> 0)
772 (3 rows)
774 drop table bc;
775 drop table ac;
776 create table ac (a int constraint check_a check (a <> 0));
777 create table bc (b int constraint check_b check (b <> 0));
778 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
779 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;
780 relname | conname | contype | conislocal | coninhcount | consrc
781 ---------+---------+---------+------------+-------------+----------
782 ac | check_a | c | t | 0 | (a <> 0)
783 bc | check_b | c | t | 0 | (b <> 0)
784 cc | check_a | c | f | 1 | (a <> 0)
785 cc | check_b | c | f | 1 | (b <> 0)
786 cc | check_c | c | t | 0 | (c <> 0)
787 (5 rows)
789 alter table cc no inherit bc;
790 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;
791 relname | conname | contype | conislocal | coninhcount | consrc
792 ---------+---------+---------+------------+-------------+----------
793 ac | check_a | c | t | 0 | (a <> 0)
794 bc | check_b | c | t | 0 | (b <> 0)
795 cc | check_a | c | f | 1 | (a <> 0)
796 cc | check_b | c | t | 0 | (b <> 0)
797 cc | check_c | c | t | 0 | (c <> 0)
798 (5 rows)
800 drop table cc;
801 drop table bc;
802 drop table ac;
803 create table p1(f1 int);
804 create table p2(f2 int);
805 create table c1(f3 int) inherits(p1,p2);
806 insert into c1 values(1,-1,2);
807 alter table p2 add constraint cc check (f2>0); -- fail
808 ERROR: check constraint "cc" is violated by some row
809 alter table p2 add check (f2>0); -- check it without a name, too
810 ERROR: check constraint "p2_f2_check" is violated by some row
811 delete from c1;
812 insert into c1 values(1,1,2);
813 alter table p2 add check (f2>0);
814 insert into c1 values(1,-1,2); -- fail
815 ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
816 create table c2(f3 int) inherits(p1,p2);
817 \d c2
818 Table "public.c2"
819 Column | Type | Modifiers
820 --------+---------+-----------
821 f1 | integer |
822 f2 | integer |
823 f3 | integer |
824 Check constraints:
825 "p2_f2_check" CHECK (f2 > 0)
826 Inherits: p1,
827 p2
829 create table c3 (f4 int) inherits(c1,c2);
830 NOTICE: merging multiple inherited definitions of column "f1"
831 NOTICE: merging multiple inherited definitions of column "f2"
832 NOTICE: merging multiple inherited definitions of column "f3"
833 \d c3
834 Table "public.c3"
835 Column | Type | Modifiers
836 --------+---------+-----------
837 f1 | integer |
838 f2 | integer |
839 f3 | integer |
840 f4 | integer |
841 Check constraints:
842 "p2_f2_check" CHECK (f2 > 0)
843 Inherits: c1,
844 c2
846 drop table p1 cascade;
847 NOTICE: drop cascades to 3 other objects
848 DETAIL: drop cascades to table c1
849 drop cascades to table c2
850 drop cascades to table c3
851 drop table p2 cascade;
852 create table pp1 (f1 int);
853 create table cc1 (f2 text, f3 int) inherits (pp1);
854 alter table pp1 add column a1 int check (a1 > 0);
855 \d cc1
856 Table "public.cc1"
857 Column | Type | Modifiers
858 --------+---------+-----------
859 f1 | integer |
860 f2 | text |
861 f3 | integer |
862 a1 | integer |
863 Check constraints:
864 "pp1_a1_check" CHECK (a1 > 0)
865 Inherits: pp1
867 create table cc2(f4 float) inherits(pp1,cc1);
868 NOTICE: merging multiple inherited definitions of column "f1"
869 NOTICE: merging multiple inherited definitions of column "a1"
870 \d cc2
871 Table "public.cc2"
872 Column | Type | Modifiers
873 --------+------------------+-----------
874 f1 | integer |
875 a1 | integer |
876 f2 | text |
877 f3 | integer |
878 f4 | double precision |
879 Check constraints:
880 "pp1_a1_check" CHECK (a1 > 0)
881 Inherits: pp1,
882 cc1
884 alter table pp1 add column a2 int check (a2 > 0);
885 NOTICE: merging definition of column "a2" for child "cc2"
886 NOTICE: merging constraint "pp1_a2_check" with inherited definition
887 \d cc2
888 Table "public.cc2"
889 Column | Type | Modifiers
890 --------+------------------+-----------
891 f1 | integer |
892 a1 | integer |
893 f2 | text |
894 f3 | integer |
895 f4 | double precision |
896 a2 | integer |
897 Check constraints:
898 "pp1_a1_check" CHECK (a1 > 0)
899 "pp1_a2_check" CHECK (a2 > 0)
900 Inherits: pp1,
901 cc1
903 drop table pp1 cascade;
904 NOTICE: drop cascades to 2 other objects
905 DETAIL: drop cascades to table cc1
906 drop cascades to table cc2