4 -- Clean up in case a prior regression run failed
5 SET client_min_messages TO 'warning';
6 DROP ROLE IF EXISTS regress_alter_table_user1;
7 RESET client_min_messages;
8 CREATE USER regress_alter_table_user1;
12 CREATE TABLE attmp (initial int4);
13 COMMENT ON TABLE attmp_wrong IS 'table comment';
14 ERROR: relation "attmp_wrong" does not exist
15 COMMENT ON TABLE attmp IS 'table comment';
16 COMMENT ON TABLE attmp IS NULL;
17 ALTER TABLE attmp ADD COLUMN xmin integer; -- fails
18 ERROR: column name "xmin" conflicts with a system column name
19 ALTER TABLE attmp ADD COLUMN a int4 default 3;
20 ALTER TABLE attmp ADD COLUMN b name;
21 ALTER TABLE attmp ADD COLUMN c text;
22 ALTER TABLE attmp ADD COLUMN d float8;
23 ALTER TABLE attmp ADD COLUMN e float4;
24 ALTER TABLE attmp ADD COLUMN f int2;
25 ALTER TABLE attmp ADD COLUMN g polygon;
26 ALTER TABLE attmp ADD COLUMN i char;
27 ALTER TABLE attmp ADD COLUMN k int4;
28 ALTER TABLE attmp ADD COLUMN l tid;
29 ALTER TABLE attmp ADD COLUMN m xid;
30 ALTER TABLE attmp ADD COLUMN n oidvector;
31 --ALTER TABLE attmp ADD COLUMN o lock;
32 ALTER TABLE attmp ADD COLUMN p boolean;
33 ALTER TABLE attmp ADD COLUMN q point;
34 ALTER TABLE attmp ADD COLUMN r lseg;
35 ALTER TABLE attmp ADD COLUMN s path;
36 ALTER TABLE attmp ADD COLUMN t box;
37 ALTER TABLE attmp ADD COLUMN v timestamp;
38 ALTER TABLE attmp ADD COLUMN w interval;
39 ALTER TABLE attmp ADD COLUMN x float8[];
40 ALTER TABLE attmp ADD COLUMN y float4[];
41 ALTER TABLE attmp ADD COLUMN z int2[];
42 INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t,
44 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
46 314159, '(1,1)', '512',
47 '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
48 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)',
49 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
51 initial | a | b | c | d | e | f | g | i | k | l | m | n | p | q | r | s | t | v | w | x | y | z
52 ---------+---+------+------+-----+-----+---+-----------------------+---+--------+-------+-----+-----------------+---+-----------+-----------------------+-----------------------------+---------------------+--------------------------+------------------+-----------+-----------+-----------
53 | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | c | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | t | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
57 -- the wolf bug - schema mods caused inconsistent row descriptors
61 ALTER TABLE attmp ADD COLUMN a int4;
62 ALTER TABLE attmp ADD COLUMN b name;
63 ALTER TABLE attmp ADD COLUMN c text;
64 ALTER TABLE attmp ADD COLUMN d float8;
65 ALTER TABLE attmp ADD COLUMN e float4;
66 ALTER TABLE attmp ADD COLUMN f int2;
67 ALTER TABLE attmp ADD COLUMN g polygon;
68 ALTER TABLE attmp ADD COLUMN i char;
69 ALTER TABLE attmp ADD COLUMN k int4;
70 ALTER TABLE attmp ADD COLUMN l tid;
71 ALTER TABLE attmp ADD COLUMN m xid;
72 ALTER TABLE attmp ADD COLUMN n oidvector;
73 --ALTER TABLE attmp ADD COLUMN o lock;
74 ALTER TABLE attmp ADD COLUMN p boolean;
75 ALTER TABLE attmp ADD COLUMN q point;
76 ALTER TABLE attmp ADD COLUMN r lseg;
77 ALTER TABLE attmp ADD COLUMN s path;
78 ALTER TABLE attmp ADD COLUMN t box;
79 ALTER TABLE attmp ADD COLUMN v timestamp;
80 ALTER TABLE attmp ADD COLUMN w interval;
81 ALTER TABLE attmp ADD COLUMN x float8[];
82 ALTER TABLE attmp ADD COLUMN y float4[];
83 ALTER TABLE attmp ADD COLUMN z int2[];
84 INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t,
86 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
88 314159, '(1,1)', '512',
89 '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
90 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)',
91 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
93 initial | a | b | c | d | e | f | g | i | k | l | m | n | p | q | r | s | t | v | w | x | y | z
94 ---------+---+------+------+-----+-----+---+-----------------------+---+--------+-------+-----+-----------------+---+-----------+-----------------------+-----------------------------+---------------------+--------------------------+------------------+-----------+-----------+-----------
95 | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | c | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | t | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
98 CREATE INDEX attmp_idx ON attmp (a, (d + e), b);
99 ALTER INDEX attmp_idx ALTER COLUMN 0 SET STATISTICS 1000;
100 ERROR: column number must be in range from 1 to 32767
101 LINE 1: ALTER INDEX attmp_idx ALTER COLUMN 0 SET STATISTICS 1000;
103 ALTER INDEX attmp_idx ALTER COLUMN 1 SET STATISTICS 1000;
104 ERROR: cannot alter statistics on non-expression column "a" of index "attmp_idx"
105 HINT: Alter statistics on table column instead.
106 ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS 1000;
108 Index "public.attmp_idx"
109 Column | Type | Key? | Definition | Storage | Stats target
110 --------+------------------+------+------------+---------+--------------
111 a | integer | yes | a | plain |
112 expr | double precision | yes | (d + e) | plain | 1000
113 b | cstring | yes | b | plain |
114 btree, for table "public.attmp"
116 ALTER INDEX attmp_idx ALTER COLUMN 3 SET STATISTICS 1000;
117 ERROR: cannot alter statistics on non-expression column "b" of index "attmp_idx"
118 HINT: Alter statistics on table column instead.
119 ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000;
120 ERROR: column number 4 of relation "attmp_idx" does not exist
121 ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1;
124 -- rename - check on both non-temp and temp tables
126 CREATE TABLE attmp (regtable int);
127 CREATE TEMP TABLE attmp (attmptable int);
128 ALTER TABLE attmp RENAME TO attmp_new;
134 SELECT * FROM attmp_new;
139 ALTER TABLE attmp RENAME TO attmp_new2;
140 SELECT * FROM attmp; -- should fail
141 ERROR: relation "attmp" does not exist
142 LINE 1: SELECT * FROM attmp;
144 SELECT * FROM attmp_new;
149 SELECT * FROM attmp_new2;
154 DROP TABLE attmp_new;
155 DROP TABLE attmp_new2;
156 -- check rename of partitioned tables and indexes also
157 CREATE TABLE part_attmp (a int primary key) partition by range (a);
158 CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100);
159 ALTER INDEX part_attmp_pkey RENAME TO part_attmp_index;
160 ALTER INDEX part_attmp1_pkey RENAME TO part_attmp1_index;
161 ALTER TABLE part_attmp RENAME TO part_at2tmp;
162 ALTER TABLE part_attmp1 RENAME TO part_at2tmp1;
163 SET ROLE regress_alter_table_user1;
164 ALTER INDEX part_attmp_index RENAME TO fail;
165 ERROR: must be owner of index part_attmp_index
166 ALTER INDEX part_attmp1_index RENAME TO fail;
167 ERROR: must be owner of index part_attmp1_index
168 ALTER TABLE part_at2tmp RENAME TO fail;
169 ERROR: must be owner of table part_at2tmp
170 ALTER TABLE part_at2tmp1 RENAME TO fail;
171 ERROR: must be owner of table part_at2tmp1
173 DROP TABLE part_at2tmp;
175 -- check renaming to a table's array type's autogenerated name
176 -- (the array type's name should get out of the way)
178 CREATE TABLE attmp_array (id int);
179 CREATE TABLE attmp_array2 (id int);
180 SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype;
186 SELECT typname FROM pg_type WHERE oid = 'attmp_array2[]'::regtype;
192 ALTER TABLE attmp_array2 RENAME TO _attmp_array;
193 SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype;
199 SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype;
205 DROP TABLE _attmp_array;
206 DROP TABLE attmp_array;
207 -- renaming to table's own array type's name is an interesting corner case
208 CREATE TABLE attmp_array (id int);
209 SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype;
215 ALTER TABLE attmp_array RENAME TO _attmp_array;
216 SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype;
222 DROP TABLE _attmp_array;
223 -- ALTER TABLE ... RENAME on non-table relations
224 -- renaming indexes (FIXME: this should probably test the index's functionality)
225 ALTER INDEX IF EXISTS __onek_unique1 RENAME TO attmp_onek_unique1;
226 NOTICE: relation "__onek_unique1" does not exist, skipping
227 ALTER INDEX IF EXISTS __attmp_onek_unique1 RENAME TO onek_unique1;
228 NOTICE: relation "__attmp_onek_unique1" does not exist, skipping
229 ALTER INDEX onek_unique1 RENAME TO attmp_onek_unique1;
230 ALTER INDEX attmp_onek_unique1 RENAME TO onek_unique1;
231 SET ROLE regress_alter_table_user1;
232 ALTER INDEX onek_unique1 RENAME TO fail; -- permission denied
233 ERROR: must be owner of index onek_unique1
236 CREATE VIEW attmp_view (unique1) AS SELECT unique1 FROM tenk1;
237 ALTER TABLE attmp_view RENAME TO attmp_view_new;
238 SET ROLE regress_alter_table_user1;
239 ALTER VIEW attmp_view_new RENAME TO fail; -- permission denied
240 ERROR: must be owner of view attmp_view_new
242 -- hack to ensure we get an indexscan here
243 set enable_seqscan to off;
244 set enable_bitmapscan to off;
246 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
256 reset enable_seqscan;
257 reset enable_bitmapscan;
258 DROP VIEW attmp_view_new;
259 -- toast-like relation name
260 alter table stud_emp rename to pg_toast_stud_emp;
261 alter table pg_toast_stud_emp rename to stud_emp;
262 -- renaming index should rename constraint as well
263 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
264 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
265 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
266 -- renaming constraint
267 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
268 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
269 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
270 -- renaming constraint should rename index as well
271 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
272 DROP INDEX onek_unique1_constraint; -- to see whether it's there
273 ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it
274 HINT: You can drop constraint onek_unique1_constraint on table onek instead.
275 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
276 DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
277 ERROR: cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it
278 HINT: You can drop constraint onek_unique1_constraint_foo on table onek instead.
279 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
280 -- renaming constraints vs. inheritance
281 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
282 \d constraint_rename_test
283 Table "public.constraint_rename_test"
284 Column | Type | Collation | Nullable | Default
285 --------+---------+-----------+----------+---------
292 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
293 NOTICE: merging column "a" with inherited definition
294 NOTICE: merging constraint "con1" with inherited definition
295 \d constraint_rename_test2
296 Table "public.constraint_rename_test2"
297 Column | Type | Collation | Nullable | Default
298 --------+---------+-----------+----------+---------
305 Inherits: constraint_rename_test
307 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
308 ERROR: cannot rename inherited constraint "con1"
309 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
310 ERROR: inherited constraint "con1" must be renamed in child tables too
311 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
312 \d constraint_rename_test
313 Table "public.constraint_rename_test"
314 Column | Type | Collation | Nullable | Default
315 --------+---------+-----------+----------+---------
320 "con1foo" CHECK (a > 0)
321 Number of child tables: 1 (Use \d+ to list them.)
323 \d constraint_rename_test2
324 Table "public.constraint_rename_test2"
325 Column | Type | Collation | Nullable | Default
326 --------+---------+-----------+----------+---------
332 "con1foo" CHECK (a > 0)
333 Inherits: constraint_rename_test
335 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
336 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
337 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
338 \d constraint_rename_test
339 Table "public.constraint_rename_test"
340 Column | Type | Collation | Nullable | Default
341 --------+---------+-----------+----------+---------
346 "con1foo" CHECK (a > 0)
347 "con2bar" CHECK (b > 0) NO INHERIT
348 Number of child tables: 1 (Use \d+ to list them.)
350 \d constraint_rename_test2
351 Table "public.constraint_rename_test2"
352 Column | Type | Collation | Nullable | Default
353 --------+---------+-----------+----------+---------
359 "con1foo" CHECK (a > 0)
360 Inherits: constraint_rename_test
362 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
363 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
364 \d constraint_rename_test
365 Table "public.constraint_rename_test"
366 Column | Type | Collation | Nullable | Default
367 --------+---------+-----------+----------+---------
368 a | integer | | not null |
372 "con3foo" PRIMARY KEY, btree (a)
374 "con1foo" CHECK (a > 0)
375 "con2bar" CHECK (b > 0) NO INHERIT
376 Number of child tables: 1 (Use \d+ to list them.)
378 \d constraint_rename_test2
379 Table "public.constraint_rename_test2"
380 Column | Type | Collation | Nullable | Default
381 --------+---------+-----------+----------+---------
382 a | integer | | not null |
387 "con1foo" CHECK (a > 0)
388 Inherits: constraint_rename_test
390 DROP TABLE constraint_rename_test2;
391 DROP TABLE constraint_rename_test;
392 ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok
393 NOTICE: relation "constraint_not_exist" does not exist, skipping
394 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
395 NOTICE: relation "constraint_rename_test" does not exist, skipping
396 -- renaming constraints with cache reset of target relation
397 CREATE TABLE constraint_rename_cache (a int,
398 CONSTRAINT chk_a CHECK (a > 0),
400 ALTER TABLE constraint_rename_cache
401 RENAME CONSTRAINT chk_a TO chk_a_new;
402 ALTER TABLE constraint_rename_cache
403 RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new;
404 CREATE TABLE like_constraint_rename_cache
405 (LIKE constraint_rename_cache INCLUDING ALL);
406 \d like_constraint_rename_cache
407 Table "public.like_constraint_rename_cache"
408 Column | Type | Collation | Nullable | Default
409 --------+---------+-----------+----------+---------
410 a | integer | | not null |
412 "like_constraint_rename_cache_pkey" PRIMARY KEY, btree (a)
414 "chk_a_new" CHECK (a > 0)
416 DROP TABLE constraint_rename_cache;
417 DROP TABLE like_constraint_rename_cache;
418 -- FOREIGN KEY CONSTRAINT adding TEST
419 CREATE TABLE attmp2 (a int primary key);
420 CREATE TABLE attmp3 (a int, b int);
421 CREATE TABLE attmp4 (a int, b int, unique(a,b));
422 CREATE TABLE attmp5 (a int, b int);
423 -- Insert rows into attmp2 (pktable)
424 INSERT INTO attmp2 values (1);
425 INSERT INTO attmp2 values (2);
426 INSERT INTO attmp2 values (3);
427 INSERT INTO attmp2 values (4);
428 -- Insert rows into attmp3
429 INSERT INTO attmp3 values (1,10);
430 INSERT INTO attmp3 values (1,20);
431 INSERT INTO attmp3 values (5,50);
432 -- Try (and fail) to add constraint due to invalid source columns
433 ALTER TABLE attmp3 add constraint attmpconstr foreign key(c) references attmp2 match full;
434 ERROR: column "c" referenced in foreign key constraint does not exist
435 -- Try (and fail) to add constraint due to invalid destination columns explicitly given
436 ALTER TABLE attmp3 add constraint attmpconstr foreign key(a) references attmp2(b) match full;
437 ERROR: column "b" referenced in foreign key constraint does not exist
438 -- Try (and fail) to add constraint due to invalid data
439 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
440 ERROR: insert or update on table "attmp3" violates foreign key constraint "attmpconstr"
441 DETAIL: Key (a)=(5) is not present in table "attmp2".
442 -- Delete failing row
443 DELETE FROM attmp3 where a=5;
445 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
446 ALTER TABLE attmp3 drop constraint attmpconstr;
447 INSERT INTO attmp3 values (5,50);
448 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
449 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full NOT VALID;
450 ALTER TABLE attmp3 validate constraint attmpconstr;
451 ERROR: insert or update on table "attmp3" violates foreign key constraint "attmpconstr"
452 DETAIL: Key (a)=(5) is not present in table "attmp2".
453 -- Delete failing row
454 DELETE FROM attmp3 where a=5;
455 -- Try (and succeed) and repeat to show it works on already valid constraint
456 ALTER TABLE attmp3 validate constraint attmpconstr;
457 ALTER TABLE attmp3 validate constraint attmpconstr;
458 -- Try a non-verified CHECK constraint
459 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
460 ERROR: check constraint "b_greater_than_ten" is violated by some row
461 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
462 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
463 ERROR: check constraint "b_greater_than_ten" is violated by some row
464 DELETE FROM attmp3 WHERE NOT b > 10;
465 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
466 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
467 -- Test inherited NOT VALID CHECK constraints
468 select * from attmp3;
474 CREATE TABLE attmp6 () INHERITS (attmp3);
475 CREATE TABLE attmp7 () INHERITS (attmp3);
476 INSERT INTO attmp6 VALUES (6, 30), (7, 16);
477 ALTER TABLE attmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
478 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- fails
479 ERROR: check constraint "b_le_20" is violated by some row
480 DELETE FROM attmp6 WHERE b > 20;
481 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
482 -- An already validated constraint must not be revalidated
483 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
484 INSERT INTO attmp7 VALUES (8, 18);
485 ALTER TABLE attmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
487 ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
488 NOTICE: merging constraint "identity" with inherited definition
489 ALTER TABLE attmp3 VALIDATE CONSTRAINT identity;
492 -- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT
493 create table parent_noinh_convalid (a int);
494 create table child_noinh_convalid () inherits (parent_noinh_convalid);
495 insert into parent_noinh_convalid values (1);
496 insert into child_noinh_convalid values (1);
497 alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid;
498 -- fail, because of the row in parent
499 alter table parent_noinh_convalid validate constraint check_a_is_2;
500 ERROR: check constraint "check_a_is_2" is violated by some row
501 delete from only parent_noinh_convalid;
502 -- ok (parent itself contains no violating rows)
503 alter table parent_noinh_convalid validate constraint check_a_is_2;
504 select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2';
511 drop table parent_noinh_convalid, child_noinh_convalid;
512 -- Try (and fail) to create constraint from attmp5(a) to attmp4(a) - unique constraint on
514 ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full;
515 ERROR: there is no unique constraint matching given keys for referenced table "attmp4"
522 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
523 -- exclusion until validated
524 set constraint_exclusion TO 'partition';
525 create table nv_parent (d date, check (false) no inherit not valid);
526 -- not valid constraint added at creation time should automatically become valid
528 Table "public.nv_parent"
529 Column | Type | Collation | Nullable | Default
530 --------+------+-----------+----------+---------
533 "nv_parent_check" CHECK (false) NO INHERIT
535 create table nv_child_2010 () inherits (nv_parent);
536 create table nv_child_2011 () inherits (nv_parent);
537 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
538 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
539 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
541 ---------------------------------------------------------------------------
543 -> Seq Scan on nv_parent
544 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
545 -> Seq Scan on nv_child_2010
546 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
547 -> Seq Scan on nv_child_2011
548 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
551 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
552 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
554 ---------------------------------------------------------------------------
556 -> Seq Scan on nv_parent
557 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
558 -> Seq Scan on nv_child_2010
559 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
560 -> Seq Scan on nv_child_2011
561 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
564 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
566 ---------------------------------------------------------------------------
568 -> Seq Scan on nv_parent
569 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
570 -> Seq Scan on nv_child_2010
571 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
572 -> Seq Scan on nv_child_2011
573 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
574 -> Seq Scan on nv_child_2009
575 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
578 -- after validation, the constraint should be used
579 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
580 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
582 ---------------------------------------------------------------------------
584 -> Seq Scan on nv_parent
585 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
586 -> Seq Scan on nv_child_2010
587 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
588 -> Seq Scan on nv_child_2009
589 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
592 -- add an inherited NOT VALID constraint
593 alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid;
595 Table "public.nv_child_2009"
596 Column | Type | Collation | Nullable | Default
597 --------+------+-----------+----------+---------
600 "nv_child_2009_d_check" CHECK (d >= '01-01-2009'::date AND d <= '12-31-2009'::date)
601 "nv_parent_d_check" CHECK (d >= '01-01-2001'::date AND d <= '12-31-2099'::date) NOT VALID
604 -- we leave nv_parent and children around to help test pg_dump logic
605 -- Foreign key adding test with mixed types
606 -- Note: these tables are TEMP to avoid name conflicts when this test
607 -- is run in parallel with foreign_key.sql.
608 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
609 INSERT INTO PKTABLE VALUES(42);
610 CREATE TEMP TABLE FKTABLE (ftest1 inet);
611 -- This next should fail, because int=inet does not exist
612 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
613 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
614 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
615 -- This should also fail for the same reason, but here we
616 -- give the column name
617 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
618 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
619 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
621 -- This should succeed, even though they are different types,
622 -- because int=int8 exists and is a member of the integer opfamily
623 CREATE TEMP TABLE FKTABLE (ftest1 int8);
624 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
625 -- Check it actually works
626 INSERT INTO FKTABLE VALUES(42); -- should succeed
627 INSERT INTO FKTABLE VALUES(43); -- should fail
628 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
629 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
631 -- This should fail, because we'd have to cast numeric to int which is
632 -- not an implicit coercion (or use numeric=numeric, but that's not part
633 -- of the integer opfamily)
634 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
635 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
636 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
637 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
640 -- On the other hand, this should work because int implicitly promotes to
641 -- numeric, and we allow promotion on the FK side
642 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
643 INSERT INTO PKTABLE VALUES(42);
644 CREATE TEMP TABLE FKTABLE (ftest1 int);
645 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
646 -- Check it actually works
647 INSERT INTO FKTABLE VALUES(42); -- should succeed
648 INSERT INTO FKTABLE VALUES(43); -- should fail
649 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
650 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
653 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
654 PRIMARY KEY(ptest1, ptest2));
655 -- This should fail, because we just chose really odd types
656 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
657 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
658 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
659 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
661 -- Again, so should this...
662 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
663 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
664 references pktable(ptest1, ptest2);
665 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
666 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
668 -- This fails because we mixed up the column ordering
669 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
670 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
671 references pktable(ptest2, ptest1);
672 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
673 DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
675 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
676 references pktable(ptest1, ptest2);
677 ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented
678 DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
681 -- Test that ALTER CONSTRAINT updates trigger deferrability properly
682 CREATE TEMP TABLE PKTABLE (ptest1 int primary key);
683 CREATE TEMP TABLE FKTABLE (ftest1 int);
684 ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable
685 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
686 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable
687 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
688 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable
689 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE;
690 ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable
691 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
692 ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE;
693 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable
694 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
695 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED;
696 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable
697 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
698 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE;
699 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
700 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
701 WHERE tgrelid = 'pktable'::regclass
703 conname | tgfoid | tgtype | tgdeferrable | tginitdeferred
704 ---------+------------------------+--------+--------------+----------------
705 fkdd | "RI_FKey_cascade_del" | 9 | f | f
706 fkdd | "RI_FKey_noaction_upd" | 17 | t | t
707 fkdd2 | "RI_FKey_cascade_del" | 9 | f | f
708 fkdd2 | "RI_FKey_noaction_upd" | 17 | t | t
709 fkdi | "RI_FKey_cascade_del" | 9 | f | f
710 fkdi | "RI_FKey_noaction_upd" | 17 | t | f
711 fkdi2 | "RI_FKey_cascade_del" | 9 | f | f
712 fkdi2 | "RI_FKey_noaction_upd" | 17 | t | f
713 fknd | "RI_FKey_cascade_del" | 9 | f | f
714 fknd | "RI_FKey_noaction_upd" | 17 | f | f
715 fknd2 | "RI_FKey_cascade_del" | 9 | f | f
716 fknd2 | "RI_FKey_noaction_upd" | 17 | f | f
719 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
720 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
721 WHERE tgrelid = 'fktable'::regclass
723 conname | tgfoid | tgtype | tgdeferrable | tginitdeferred
724 ---------+---------------------+--------+--------------+----------------
725 fkdd | "RI_FKey_check_ins" | 5 | t | t
726 fkdd | "RI_FKey_check_upd" | 17 | t | t
727 fkdd2 | "RI_FKey_check_ins" | 5 | t | t
728 fkdd2 | "RI_FKey_check_upd" | 17 | t | t
729 fkdi | "RI_FKey_check_ins" | 5 | t | f
730 fkdi | "RI_FKey_check_upd" | 17 | t | f
731 fkdi2 | "RI_FKey_check_ins" | 5 | t | f
732 fkdi2 | "RI_FKey_check_upd" | 17 | t | f
733 fknd | "RI_FKey_check_ins" | 5 | f | f
734 fknd | "RI_FKey_check_upd" | 17 | f | f
735 fknd2 | "RI_FKey_check_ins" | 5 | f | f
736 fknd2 | "RI_FKey_check_upd" | 17 | f | f
739 -- temp tables should go away by themselves, need not drop them.
740 -- test check constraint adding
741 create table atacc1 ( test int );
742 -- add a check constraint
743 alter table atacc1 add constraint atacc_test1 check (test>3);
745 insert into atacc1 (test) values (2);
746 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
747 DETAIL: Failing row contains (2).
749 insert into atacc1 (test) values (4);
751 -- let's do one where the check fails when added
752 create table atacc1 ( test int );
753 -- insert a soon to be failing row
754 insert into atacc1 (test) values (2);
755 -- add a check constraint (fails)
756 alter table atacc1 add constraint atacc_test1 check (test>3);
757 ERROR: check constraint "atacc_test1" is violated by some row
758 insert into atacc1 (test) values (4);
760 -- let's do one where the check fails because the column doesn't exist
761 create table atacc1 ( test int );
762 -- add a check constraint (fails)
763 alter table atacc1 add constraint atacc_test1 check (test1>3);
764 ERROR: column "test1" does not exist
765 HINT: Perhaps you meant to reference the column "atacc1.test".
767 -- something a little more complicated
768 create table atacc1 ( test int, test2 int, test3 int);
769 -- add a check constraint (fails)
770 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
772 insert into atacc1 (test,test2,test3) values (4,4,2);
773 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
774 DETAIL: Failing row contains (4, 4, 2).
776 insert into atacc1 (test,test2,test3) values (4,4,5);
778 -- lets do some naming tests
779 create table atacc1 (test int check (test>3), test2 int);
780 alter table atacc1 add check (test2>test);
781 -- should fail for $2
782 insert into atacc1 (test2, test) values (3, 4);
783 ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
784 DETAIL: Failing row contains (4, 3).
786 -- inheritance related tests
787 create table atacc1 (test int);
788 create table atacc2 (test2 int);
789 create table atacc3 (test3 int) inherits (atacc1, atacc2);
790 alter table atacc2 add constraint foo check (test2>0);
791 -- fail and then succeed on atacc2
792 insert into atacc2 (test2) values (-3);
793 ERROR: new row for relation "atacc2" violates check constraint "foo"
794 DETAIL: Failing row contains (-3).
795 insert into atacc2 (test2) values (3);
796 -- fail and then succeed on atacc3
797 insert into atacc3 (test2) values (-3);
798 ERROR: new row for relation "atacc3" violates check constraint "foo"
799 DETAIL: Failing row contains (null, -3, null).
800 insert into atacc3 (test2) values (3);
804 -- same things with one created with INHERIT
805 create table atacc1 (test int);
806 create table atacc2 (test2 int);
807 create table atacc3 (test3 int) inherits (atacc1, atacc2);
808 alter table atacc3 no inherit atacc2;
810 alter table atacc3 no inherit atacc2;
811 ERROR: relation "atacc2" is not a parent of relation "atacc3"
812 -- make sure it really isn't a child
813 insert into atacc3 (test2) values (3);
814 select test2 from atacc2;
819 -- fail due to missing constraint
820 alter table atacc2 add constraint foo check (test2>0);
821 alter table atacc3 inherit atacc2;
822 ERROR: child table is missing constraint "foo"
823 -- fail due to missing column
824 alter table atacc3 rename test2 to testx;
825 alter table atacc3 inherit atacc2;
826 ERROR: child table is missing column "test2"
827 -- fail due to mismatched data type
828 alter table atacc3 add test2 bool;
829 alter table atacc3 inherit atacc2;
830 ERROR: child table "atacc3" has different type for column "test2"
831 alter table atacc3 drop test2;
833 alter table atacc3 add test2 int;
834 update atacc3 set test2 = 4 where test2 is null;
835 alter table atacc3 add constraint foo check (test2>0);
836 alter table atacc3 inherit atacc2;
837 -- fail due to duplicates and circular inheritance
838 alter table atacc3 inherit atacc2;
839 ERROR: relation "atacc2" would be inherited from more than once
840 alter table atacc2 inherit atacc3;
841 ERROR: circular inheritance not allowed
842 DETAIL: "atacc3" is already a child of "atacc2".
843 alter table atacc2 inherit atacc2;
844 ERROR: circular inheritance not allowed
845 DETAIL: "atacc2" is already a child of "atacc2".
846 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
847 select test2 from atacc2;
853 drop table atacc2 cascade;
854 NOTICE: drop cascades to table atacc3
856 -- adding only to a parent is allowed as of 9.2
857 create table atacc1 (test int);
858 create table atacc2 (test2 int) inherits (atacc1);
860 alter table atacc1 add constraint foo check (test>0) no inherit;
861 -- check constraint is not there on child
862 insert into atacc2 (test) values (-3);
863 -- check constraint is there on parent
864 insert into atacc1 (test) values (-3);
865 ERROR: new row for relation "atacc1" violates check constraint "foo"
866 DETAIL: Failing row contains (-3).
867 insert into atacc1 (test) values (3);
868 -- fail, violating row:
869 alter table atacc2 add constraint foo check (test>0) no inherit;
870 ERROR: check constraint "foo" is violated by some row
873 -- test unique constraint adding
874 create table atacc1 ( test int ) ;
875 -- add a unique constraint
876 alter table atacc1 add constraint atacc_test1 unique (test);
877 -- insert first value
878 insert into atacc1 (test) values (2);
880 insert into atacc1 (test) values (2);
881 ERROR: duplicate key value violates unique constraint "atacc_test1"
882 DETAIL: Key (test)=(2) already exists.
884 insert into atacc1 (test) values (4);
885 -- try to create duplicates via alter table using - should fail
886 alter table atacc1 alter column test type integer using 0;
887 ERROR: could not create unique index "atacc_test1"
888 DETAIL: Key (test)=(0) is duplicated.
890 -- let's do one where the unique constraint fails when added
891 create table atacc1 ( test int );
892 -- insert soon to be failing rows
893 insert into atacc1 (test) values (2);
894 insert into atacc1 (test) values (2);
895 -- add a unique constraint (fails)
896 alter table atacc1 add constraint atacc_test1 unique (test);
897 ERROR: could not create unique index "atacc_test1"
898 DETAIL: Key (test)=(2) is duplicated.
899 insert into atacc1 (test) values (3);
901 -- let's do one where the unique constraint fails
902 -- because the column doesn't exist
903 create table atacc1 ( test int );
904 -- add a unique constraint (fails)
905 alter table atacc1 add constraint atacc_test1 unique (test1);
906 ERROR: column "test1" named in key does not exist
908 -- something a little more complicated
909 create table atacc1 ( test int, test2 int);
910 -- add a unique constraint
911 alter table atacc1 add constraint atacc_test1 unique (test, test2);
912 -- insert initial value
913 insert into atacc1 (test,test2) values (4,4);
915 insert into atacc1 (test,test2) values (4,4);
916 ERROR: duplicate key value violates unique constraint "atacc_test1"
917 DETAIL: Key (test, test2)=(4, 4) already exists.
918 -- should all succeed
919 insert into atacc1 (test,test2) values (4,5);
920 insert into atacc1 (test,test2) values (5,4);
921 insert into atacc1 (test,test2) values (5,5);
923 -- lets do some naming tests
924 create table atacc1 (test int, test2 int, unique(test));
925 alter table atacc1 add unique (test2);
926 -- should fail for @@ second one @@
927 insert into atacc1 (test2, test) values (3, 3);
928 insert into atacc1 (test2, test) values (2, 3);
929 ERROR: duplicate key value violates unique constraint "atacc1_test_key"
930 DETAIL: Key (test)=(3) already exists.
932 -- test primary key constraint adding
933 create table atacc1 ( id serial, test int) ;
934 -- add a primary key constraint
935 alter table atacc1 add constraint atacc_test1 primary key (test);
936 -- insert first value
937 insert into atacc1 (test) values (2);
939 insert into atacc1 (test) values (2);
940 ERROR: duplicate key value violates unique constraint "atacc_test1"
941 DETAIL: Key (test)=(2) already exists.
943 insert into atacc1 (test) values (4);
944 -- inserting NULL should fail
945 insert into atacc1 (test) values(NULL);
946 ERROR: null value in column "test" violates not-null constraint
947 DETAIL: Failing row contains (4, null).
948 -- try adding a second primary key (should fail)
949 alter table atacc1 add constraint atacc_oid1 primary key(id);
950 ERROR: multiple primary keys for table "atacc1" are not allowed
951 -- drop first primary key constraint
952 alter table atacc1 drop constraint atacc_test1 restrict;
953 -- try adding a primary key on oid (should succeed)
954 alter table atacc1 add constraint atacc_oid1 primary key(id);
956 -- let's do one where the primary key constraint fails when added
957 create table atacc1 ( test int );
958 -- insert soon to be failing rows
959 insert into atacc1 (test) values (2);
960 insert into atacc1 (test) values (2);
961 -- add a primary key (fails)
962 alter table atacc1 add constraint atacc_test1 primary key (test);
963 ERROR: could not create unique index "atacc_test1"
964 DETAIL: Key (test)=(2) is duplicated.
965 insert into atacc1 (test) values (3);
967 -- let's do another one where the primary key constraint fails when added
968 create table atacc1 ( test int );
969 -- insert soon to be failing row
970 insert into atacc1 (test) values (NULL);
971 -- add a primary key (fails)
972 alter table atacc1 add constraint atacc_test1 primary key (test);
973 ERROR: column "test" contains null values
974 insert into atacc1 (test) values (3);
976 -- let's do one where the primary key constraint fails
977 -- because the column doesn't exist
978 create table atacc1 ( test int );
979 -- add a primary key constraint (fails)
980 alter table atacc1 add constraint atacc_test1 primary key (test1);
981 ERROR: column "test1" of relation "atacc1" does not exist
983 -- adding a new column as primary key to a non-empty table.
984 -- should fail unless the column has a non-null default value.
985 create table atacc1 ( test int );
986 insert into atacc1 (test) values (0);
987 -- add a primary key column without a default (fails).
988 alter table atacc1 add column test2 int primary key;
989 ERROR: column "test2" contains null values
990 -- now add a primary key column with a default (succeeds).
991 alter table atacc1 add column test2 int default 0 primary key;
993 -- this combination used to have order-of-execution problems (bug #15580)
994 create table atacc1 (a int);
995 insert into atacc1 values(1);
997 add column b float8 not null default random(),
1000 -- something a little more complicated
1001 create table atacc1 ( test int, test2 int);
1002 -- add a primary key constraint
1003 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
1004 -- try adding a second primary key - should fail
1005 alter table atacc1 add constraint atacc_test2 primary key (test);
1006 ERROR: multiple primary keys for table "atacc1" are not allowed
1007 -- insert initial value
1008 insert into atacc1 (test,test2) values (4,4);
1010 insert into atacc1 (test,test2) values (4,4);
1011 ERROR: duplicate key value violates unique constraint "atacc_test1"
1012 DETAIL: Key (test, test2)=(4, 4) already exists.
1013 insert into atacc1 (test,test2) values (NULL,3);
1014 ERROR: null value in column "test" violates not-null constraint
1015 DETAIL: Failing row contains (null, 3).
1016 insert into atacc1 (test,test2) values (3, NULL);
1017 ERROR: null value in column "test2" violates not-null constraint
1018 DETAIL: Failing row contains (3, null).
1019 insert into atacc1 (test,test2) values (NULL,NULL);
1020 ERROR: null value in column "test" violates not-null constraint
1021 DETAIL: Failing row contains (null, null).
1022 -- should all succeed
1023 insert into atacc1 (test,test2) values (4,5);
1024 insert into atacc1 (test,test2) values (5,4);
1025 insert into atacc1 (test,test2) values (5,5);
1027 -- lets do some naming tests
1028 create table atacc1 (test int, test2 int, primary key(test));
1029 -- only first should succeed
1030 insert into atacc1 (test2, test) values (3, 3);
1031 insert into atacc1 (test2, test) values (2, 3);
1032 ERROR: duplicate key value violates unique constraint "atacc1_pkey"
1033 DETAIL: Key (test)=(3) already exists.
1034 insert into atacc1 (test2, test) values (1, NULL);
1035 ERROR: null value in column "test" violates not-null constraint
1036 DETAIL: Failing row contains (null, 1).
1038 -- alter table / alter column [set/drop] not null tests
1039 -- try altering system catalogs, should fail
1040 alter table pg_class alter column relname drop not null;
1041 ERROR: permission denied: "pg_class" is a system catalog
1042 alter table pg_class alter relname set not null;
1043 ERROR: permission denied: "pg_class" is a system catalog
1044 -- try altering non-existent table, should fail
1045 alter table non_existent alter column bar set not null;
1046 ERROR: relation "non_existent" does not exist
1047 alter table non_existent alter column bar drop not null;
1048 ERROR: relation "non_existent" does not exist
1049 -- test setting columns to null and not null and vice versa
1050 -- test checking for null values and primary key
1051 create table atacc1 (test int not null);
1052 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
1053 alter table atacc1 alter column test drop not null;
1054 ERROR: column "test" is in a primary key
1055 alter table atacc1 drop constraint "atacc1_pkey";
1056 alter table atacc1 alter column test drop not null;
1057 insert into atacc1 values (null);
1058 alter table atacc1 alter test set not null;
1059 ERROR: column "test" contains null values
1061 alter table atacc1 alter test set not null;
1062 -- try altering a non-existent column, should fail
1063 alter table atacc1 alter bar set not null;
1064 ERROR: column "bar" of relation "atacc1" does not exist
1065 alter table atacc1 alter bar drop not null;
1066 ERROR: column "bar" of relation "atacc1" does not exist
1067 -- try creating a view and altering that, should fail
1068 create view myview as select * from atacc1;
1069 alter table myview alter column test drop not null;
1070 ERROR: "myview" is not a table or foreign table
1071 alter table myview alter column test set not null;
1072 ERROR: "myview" is not a table or foreign table
1075 -- set not null verified by constraints
1076 create table atacc1 (test_a int, test_b int);
1077 insert into atacc1 values (null, 1);
1078 -- constraint not cover all values, should fail
1079 alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10);
1080 alter table atacc1 alter test_a set not null;
1081 ERROR: column "test_a" contains null values
1082 alter table atacc1 drop constraint atacc1_constr_or;
1083 -- not valid constraint, should fail
1084 alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid;
1085 alter table atacc1 alter test_a set not null;
1086 ERROR: column "test_a" contains null values
1087 alter table atacc1 drop constraint atacc1_constr_invalid;
1088 -- with valid constraint
1089 update atacc1 set test_a = 1;
1090 alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null);
1091 alter table atacc1 alter test_a set not null;
1093 insert into atacc1 values (2, null);
1094 alter table atacc1 alter test_a drop not null;
1095 -- test multiple set not null at same time
1096 -- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan
1097 alter table atacc1 alter test_a set not null, alter test_b set not null;
1098 ERROR: column "test_b" contains null values
1099 -- commands order has no importance
1100 alter table atacc1 alter test_b set not null, alter test_a set not null;
1101 ERROR: column "test_b" contains null values
1102 -- valid one by table scan, one by check constraints
1103 update atacc1 set test_b = 1;
1104 alter table atacc1 alter test_b set not null, alter test_a set not null;
1105 alter table atacc1 alter test_a drop not null, alter test_b drop not null;
1106 -- both column has check constraints
1107 alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
1108 alter table atacc1 alter test_b set not null, alter test_a set not null;
1111 create table parent (a int);
1112 create table child (b varchar(255)) inherits (parent);
1113 alter table parent alter a set not null;
1114 insert into parent values (NULL);
1115 ERROR: null value in column "a" violates not-null constraint
1116 DETAIL: Failing row contains (null).
1117 insert into child (a, b) values (NULL, 'foo');
1118 ERROR: null value in column "a" violates not-null constraint
1119 DETAIL: Failing row contains (null, foo).
1120 alter table parent alter a drop not null;
1121 insert into parent values (NULL);
1122 insert into child (a, b) values (NULL, 'foo');
1123 alter table only parent alter a set not null;
1124 ERROR: column "a" contains null values
1125 alter table child alter a set not null;
1126 ERROR: column "a" contains null values
1128 alter table only parent alter a set not null;
1129 insert into parent values (NULL);
1130 ERROR: null value in column "a" violates not-null constraint
1131 DETAIL: Failing row contains (null).
1132 alter table child alter a set not null;
1133 insert into child (a, b) values (NULL, 'foo');
1134 ERROR: null value in column "a" violates not-null constraint
1135 DETAIL: Failing row contains (null, foo).
1137 alter table child alter a set not null;
1138 insert into child (a, b) values (NULL, 'foo');
1139 ERROR: null value in column "a" violates not-null constraint
1140 DETAIL: Failing row contains (null, foo).
1143 -- test setting and removing default values
1144 create table def_test (
1146 c2 text default 'initial_default'
1148 insert into def_test default values;
1149 alter table def_test alter column c1 drop default;
1150 insert into def_test default values;
1151 alter table def_test alter column c2 drop default;
1152 insert into def_test default values;
1153 alter table def_test alter column c1 set default 10;
1154 alter table def_test alter column c2 set default 'new_default';
1155 insert into def_test default values;
1156 select * from def_test;
1158 ----+-----------------
1165 -- set defaults to an incorrect type: this should fail
1166 alter table def_test alter column c1 set default 'wrong_datatype';
1167 ERROR: invalid input syntax for type integer: "wrong_datatype"
1168 alter table def_test alter column c2 set default 20;
1169 -- set defaults on a non-existent column: this should fail
1170 alter table def_test alter column c3 set default 30;
1171 ERROR: column "c3" of relation "def_test" does not exist
1172 -- set defaults on views: we need to create a view, add a rule
1173 -- to allow insertions into it, and then alter the view to add
1175 create view def_view_test as select * from def_test;
1176 create rule def_view_test_ins as
1177 on insert to def_view_test
1178 do instead insert into def_test select new.*;
1179 insert into def_view_test default values;
1180 alter table def_view_test alter column c1 set default 45;
1181 insert into def_view_test default values;
1182 alter table def_view_test alter column c2 set default 'view_default';
1183 insert into def_view_test default values;
1184 select * from def_view_test;
1186 ----+-----------------
1196 drop rule def_view_test_ins on def_view_test;
1197 drop view def_view_test;
1198 drop table def_test;
1199 -- alter table / drop column tests
1200 -- try altering system catalogs, should fail
1201 alter table pg_class drop column relname;
1202 ERROR: permission denied: "pg_class" is a system catalog
1203 -- try altering non-existent table, should fail
1204 alter table nosuchtable drop column bar;
1205 ERROR: relation "nosuchtable" does not exist
1206 -- test dropping columns
1207 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
1208 insert into atacc1 values (1, 2, 3, 4);
1209 alter table atacc1 drop a;
1210 alter table atacc1 drop a;
1211 ERROR: column "a" of relation "atacc1" does not exist
1213 select * from atacc1;
1219 select * from atacc1 order by a;
1220 ERROR: column "a" does not exist
1221 LINE 1: select * from atacc1 order by a;
1223 select * from atacc1 order by "........pg.dropped.1........";
1224 ERROR: column "........pg.dropped.1........" does not exist
1225 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
1227 select * from atacc1 group by a;
1228 ERROR: column "a" does not exist
1229 LINE 1: select * from atacc1 group by a;
1231 select * from atacc1 group by "........pg.dropped.1........";
1232 ERROR: column "........pg.dropped.1........" does not exist
1233 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
1235 select atacc1.* from atacc1;
1241 select a from atacc1;
1242 ERROR: column "a" does not exist
1243 LINE 1: select a from atacc1;
1245 select atacc1.a from atacc1;
1246 ERROR: column atacc1.a does not exist
1247 LINE 1: select atacc1.a from atacc1;
1249 select b,c,d from atacc1;
1255 select a,b,c,d from atacc1;
1256 ERROR: column "a" does not exist
1257 LINE 1: select a,b,c,d from atacc1;
1259 select * from atacc1 where a = 1;
1260 ERROR: column "a" does not exist
1261 LINE 1: select * from atacc1 where a = 1;
1263 select "........pg.dropped.1........" from atacc1;
1264 ERROR: column "........pg.dropped.1........" does not exist
1265 LINE 1: select "........pg.dropped.1........" from atacc1;
1267 select atacc1."........pg.dropped.1........" from atacc1;
1268 ERROR: column atacc1.........pg.dropped.1........ does not exist
1269 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
1271 select "........pg.dropped.1........",b,c,d from atacc1;
1272 ERROR: column "........pg.dropped.1........" does not exist
1273 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
1275 select * from atacc1 where "........pg.dropped.1........" = 1;
1276 ERROR: column "........pg.dropped.1........" does not exist
1277 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
1280 update atacc1 set a = 3;
1281 ERROR: column "a" of relation "atacc1" does not exist
1282 LINE 1: update atacc1 set a = 3;
1284 update atacc1 set b = 2 where a = 3;
1285 ERROR: column "a" does not exist
1286 LINE 1: update atacc1 set b = 2 where a = 3;
1288 update atacc1 set "........pg.dropped.1........" = 3;
1289 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1290 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
1292 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
1293 ERROR: column "........pg.dropped.1........" does not exist
1294 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
1297 insert into atacc1 values (10, 11, 12, 13);
1298 ERROR: INSERT has more expressions than target columns
1299 LINE 1: insert into atacc1 values (10, 11, 12, 13);
1301 insert into atacc1 values (default, 11, 12, 13);
1302 ERROR: INSERT has more expressions than target columns
1303 LINE 1: insert into atacc1 values (default, 11, 12, 13);
1305 insert into atacc1 values (11, 12, 13);
1306 insert into atacc1 (a) values (10);
1307 ERROR: column "a" of relation "atacc1" does not exist
1308 LINE 1: insert into atacc1 (a) values (10);
1310 insert into atacc1 (a) values (default);
1311 ERROR: column "a" of relation "atacc1" does not exist
1312 LINE 1: insert into atacc1 (a) values (default);
1314 insert into atacc1 (a,b,c,d) values (10,11,12,13);
1315 ERROR: column "a" of relation "atacc1" does not exist
1316 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
1318 insert into atacc1 (a,b,c,d) values (default,11,12,13);
1319 ERROR: column "a" of relation "atacc1" does not exist
1320 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
1322 insert into atacc1 (b,c,d) values (11,12,13);
1323 insert into atacc1 ("........pg.dropped.1........") values (10);
1324 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1325 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
1327 insert into atacc1 ("........pg.dropped.1........") values (default);
1328 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1329 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
1331 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
1332 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1333 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
1335 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
1336 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1337 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
1340 delete from atacc1 where a = 3;
1341 ERROR: column "a" does not exist
1342 LINE 1: delete from atacc1 where a = 3;
1344 delete from atacc1 where "........pg.dropped.1........" = 3;
1345 ERROR: column "........pg.dropped.1........" does not exist
1346 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
1349 -- try dropping a non-existent column, should fail
1350 alter table atacc1 drop bar;
1351 ERROR: column "bar" of relation "atacc1" does not exist
1352 -- try removing an oid column, should succeed (as it's nonexistent)
1353 alter table atacc1 SET WITHOUT OIDS;
1354 -- try adding an oid column, should fail (not supported)
1355 alter table atacc1 SET WITH OIDS;
1356 ERROR: syntax error at or near "WITH"
1357 LINE 1: alter table atacc1 SET WITH OIDS;
1359 -- try dropping the xmin column, should fail
1360 alter table atacc1 drop xmin;
1361 ERROR: cannot drop system column "xmin"
1362 -- try creating a view and altering that, should fail
1363 create view myview as select * from atacc1;
1364 select * from myview;
1369 alter table myview drop d;
1370 ERROR: "myview" is not a table, composite type, or foreign table
1372 -- test some commands to make sure they fail on the dropped column
1374 ERROR: column "a" of relation "atacc1" does not exist
1375 analyze atacc1("........pg.dropped.1........");
1376 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1377 vacuum analyze atacc1(a);
1378 ERROR: column "a" of relation "atacc1" does not exist
1379 vacuum analyze atacc1("........pg.dropped.1........");
1380 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1381 comment on column atacc1.a is 'testing';
1382 ERROR: column "a" of relation "atacc1" does not exist
1383 comment on column atacc1."........pg.dropped.1........" is 'testing';
1384 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1385 alter table atacc1 alter a set storage plain;
1386 ERROR: column "a" of relation "atacc1" does not exist
1387 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
1388 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1389 alter table atacc1 alter a set statistics 0;
1390 ERROR: column "a" of relation "atacc1" does not exist
1391 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
1392 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1393 alter table atacc1 alter a set default 3;
1394 ERROR: column "a" of relation "atacc1" does not exist
1395 alter table atacc1 alter "........pg.dropped.1........" set default 3;
1396 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1397 alter table atacc1 alter a drop default;
1398 ERROR: column "a" of relation "atacc1" does not exist
1399 alter table atacc1 alter "........pg.dropped.1........" drop default;
1400 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1401 alter table atacc1 alter a set not null;
1402 ERROR: column "a" of relation "atacc1" does not exist
1403 alter table atacc1 alter "........pg.dropped.1........" set not null;
1404 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1405 alter table atacc1 alter a drop not null;
1406 ERROR: column "a" of relation "atacc1" does not exist
1407 alter table atacc1 alter "........pg.dropped.1........" drop not null;
1408 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1409 alter table atacc1 rename a to x;
1410 ERROR: column "a" does not exist
1411 alter table atacc1 rename "........pg.dropped.1........" to x;
1412 ERROR: column "........pg.dropped.1........" does not exist
1413 alter table atacc1 add primary key(a);
1414 ERROR: column "a" of relation "atacc1" does not exist
1415 alter table atacc1 add primary key("........pg.dropped.1........");
1416 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1417 alter table atacc1 add unique(a);
1418 ERROR: column "a" named in key does not exist
1419 alter table atacc1 add unique("........pg.dropped.1........");
1420 ERROR: column "........pg.dropped.1........" named in key does not exist
1421 alter table atacc1 add check (a > 3);
1422 ERROR: column "a" does not exist
1423 alter table atacc1 add check ("........pg.dropped.1........" > 3);
1424 ERROR: column "........pg.dropped.1........" does not exist
1425 create table atacc2 (id int4 unique);
1426 alter table atacc1 add foreign key (a) references atacc2(id);
1427 ERROR: column "a" referenced in foreign key constraint does not exist
1428 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
1429 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1430 alter table atacc2 add foreign key (id) references atacc1(a);
1431 ERROR: column "a" referenced in foreign key constraint does not exist
1432 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
1433 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1435 create index "testing_idx" on atacc1(a);
1436 ERROR: column "a" does not exist
1437 create index "testing_idx" on atacc1("........pg.dropped.1........");
1438 ERROR: column "........pg.dropped.1........" does not exist
1439 -- test create as and select into
1440 insert into atacc1 values (21, 22, 23);
1441 create table attest1 as select * from atacc1;
1442 select * from attest1;
1449 select * into attest2 from atacc1;
1450 select * from attest2;
1457 -- try dropping all columns
1458 alter table atacc1 drop c;
1459 alter table atacc1 drop d;
1460 alter table atacc1 drop b;
1461 select * from atacc1;
1466 -- test constraint error reporting in presence of dropped columns
1467 create table atacc1 (id serial primary key, value int check (value < 10));
1468 insert into atacc1(value) values (100);
1469 ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check"
1470 DETAIL: Failing row contains (1, 100).
1471 alter table atacc1 drop column value;
1472 alter table atacc1 add column value int check (value < 10);
1473 insert into atacc1(value) values (100);
1474 ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check"
1475 DETAIL: Failing row contains (2, 100).
1476 insert into atacc1(id, value) values (null, 0);
1477 ERROR: null value in column "id" violates not-null constraint
1478 DETAIL: Failing row contains (null, 0).
1481 create table parent (a int, b int, c int);
1482 insert into parent values (1, 2, 3);
1483 alter table parent drop a;
1484 create table child (d varchar(255)) inherits (parent);
1485 insert into child values (12, 13, 'testing');
1486 select * from parent;
1493 select * from child;
1499 alter table parent drop c;
1500 select * from parent;
1507 select * from child;
1515 -- check error cases for inheritance column merging
1516 create table parent (a float8, b numeric(10,4), c text collate "C");
1517 create table child (a float4) inherits (parent); -- fail
1518 NOTICE: merging column "a" with inherited definition
1519 ERROR: column "a" has a type conflict
1520 DETAIL: double precision versus real
1521 create table child (b decimal(10,7)) inherits (parent); -- fail
1522 NOTICE: moving and merging column "b" with inherited definition
1523 DETAIL: User-specified column moved to the position of the inherited column.
1524 ERROR: column "b" has a type conflict
1525 DETAIL: numeric(10,4) versus numeric(10,7)
1526 create table child (c text collate "POSIX") inherits (parent); -- fail
1527 NOTICE: moving and merging column "c" with inherited definition
1528 DETAIL: User-specified column moved to the position of the inherited column.
1529 ERROR: column "c" has a collation conflict
1530 DETAIL: "C" versus "POSIX"
1531 create table child (a double precision, b decimal(10,4)) inherits (parent);
1532 NOTICE: merging column "a" with inherited definition
1533 NOTICE: merging column "b" with inherited definition
1537 create table attest (a int4, b int4, c int4);
1538 insert into attest values (1,2,3);
1539 alter table attest drop a;
1540 copy attest to stdout;
1542 copy attest(a) to stdout;
1543 ERROR: column "a" of relation "attest" does not exist
1544 copy attest("........pg.dropped.1........") to stdout;
1545 ERROR: column "........pg.dropped.1........" of relation "attest" does not exist
1546 copy attest from stdin;
1547 ERROR: extra data after last expected column
1548 CONTEXT: COPY attest, line 1: "10 11 12"
1549 select * from attest;
1555 copy attest from stdin;
1556 select * from attest;
1563 copy attest(a) from stdin;
1564 ERROR: column "a" of relation "attest" does not exist
1565 copy attest("........pg.dropped.1........") from stdin;
1566 ERROR: column "........pg.dropped.1........" of relation "attest" does not exist
1567 copy attest(b,c) from stdin;
1568 select * from attest;
1578 create table dropColumn (a int, b int, e int);
1579 create table dropColumnChild (c int) inherits (dropColumn);
1580 create table dropColumnAnother (d int) inherits (dropColumnChild);
1581 -- these two should fail
1582 alter table dropColumnchild drop column a;
1583 ERROR: cannot drop inherited column "a"
1584 alter table only dropColumnChild drop column b;
1585 ERROR: cannot drop inherited column "b"
1586 -- these three should work
1587 alter table only dropColumn drop column e;
1588 alter table dropColumnChild drop column c;
1589 alter table dropColumn drop column a;
1590 create table renameColumn (a int);
1591 create table renameColumnChild (b int) inherits (renameColumn);
1592 create table renameColumnAnother (c int) inherits (renameColumnChild);
1593 -- these three should fail
1594 alter table renameColumnChild rename column a to d;
1595 ERROR: cannot rename inherited column "a"
1596 alter table only renameColumnChild rename column a to d;
1597 ERROR: inherited column "a" must be renamed in child tables too
1598 alter table only renameColumn rename column a to d;
1599 ERROR: inherited column "a" must be renamed in child tables too
1600 -- these should work
1601 alter table renameColumn rename column a to d;
1602 alter table renameColumnChild rename column b to a;
1603 -- these should work
1604 alter table if exists doesnt_exist_tab rename column a to d;
1605 NOTICE: relation "doesnt_exist_tab" does not exist, skipping
1606 alter table if exists doesnt_exist_tab rename column b to a;
1607 NOTICE: relation "doesnt_exist_tab" does not exist, skipping
1609 alter table renameColumn add column w int;
1611 alter table only renameColumn add column x int;
1612 ERROR: column must be added to child tables too
1613 -- Test corner cases in dropping of inherited columns
1614 create table p1 (f1 int, f2 int);
1615 create table c1 (f1 int not null) inherits(p1);
1616 NOTICE: merging column "f1" with inherited definition
1617 -- should be rejected since c1.f1 is inherited
1618 alter table c1 drop column f1;
1619 ERROR: cannot drop inherited column "f1"
1621 alter table p1 drop column f1;
1622 -- c1.f1 is still there, but no longer inherited
1628 alter table c1 drop column f1;
1630 ERROR: column "f1" does not exist
1631 LINE 1: select f1 from c1;
1633 HINT: Perhaps you meant to reference the column "c1.f2".
1634 drop table p1 cascade;
1635 NOTICE: drop cascades to table c1
1636 create table p1 (f1 int, f2 int);
1637 create table c1 () inherits(p1);
1638 -- should be rejected since c1.f1 is inherited
1639 alter table c1 drop column f1;
1640 ERROR: cannot drop inherited column "f1"
1641 alter table p1 drop column f1;
1642 -- c1.f1 is dropped now, since there is no local definition for it
1644 ERROR: column "f1" does not exist
1645 LINE 1: select f1 from c1;
1647 HINT: Perhaps you meant to reference the column "c1.f2".
1648 drop table p1 cascade;
1649 NOTICE: drop cascades to table c1
1650 create table p1 (f1 int, f2 int);
1651 create table c1 () inherits(p1);
1652 -- should be rejected since c1.f1 is inherited
1653 alter table c1 drop column f1;
1654 ERROR: cannot drop inherited column "f1"
1655 alter table only p1 drop column f1;
1656 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1657 alter table c1 drop column f1;
1658 drop table p1 cascade;
1659 NOTICE: drop cascades to table c1
1660 create table p1 (f1 int, f2 int);
1661 create table c1 (f1 int not null) inherits(p1);
1662 NOTICE: merging column "f1" with inherited definition
1663 -- should be rejected since c1.f1 is inherited
1664 alter table c1 drop column f1;
1665 ERROR: cannot drop inherited column "f1"
1666 alter table only p1 drop column f1;
1667 -- c1.f1 is still there, but no longer inherited
1668 alter table c1 drop column f1;
1669 drop table p1 cascade;
1670 NOTICE: drop cascades to table c1
1671 create table p1(id int, name text);
1672 create table p2(id2 int, name text, height int);
1673 create table c1(age int) inherits(p1,p2);
1674 NOTICE: merging multiple inherited definitions of column "name"
1675 create table gc1() inherits (c1);
1676 select relname, attname, attinhcount, attislocal
1677 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1678 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1679 order by relname, attnum;
1680 relname | attname | attinhcount | attislocal
1681 ---------+---------+-------------+------------
1690 gc1 | height | 1 | f
1700 alter table only p1 drop column name;
1701 -- should work. Now c1.name is local and inhcount is 0.
1702 alter table p2 drop column name;
1703 -- should be rejected since its inherited
1704 alter table gc1 drop column name;
1705 ERROR: cannot drop inherited column "name"
1706 -- should work, and drop gc1.name along
1707 alter table c1 drop column name;
1708 -- should fail: column does not exist
1709 alter table gc1 drop column name;
1710 ERROR: column "name" of relation "gc1" does not exist
1711 -- should work and drop the attribute in all tables
1712 alter table p2 drop column height;
1714 create table dropColumnExists ();
1715 alter table dropColumnExists drop column non_existing; --fail
1716 ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
1717 alter table dropColumnExists drop column if exists non_existing; --succeed
1718 NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
1719 select relname, attname, attinhcount, attislocal
1720 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1721 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1722 order by relname, attnum;
1723 relname | attname | attinhcount | attislocal
1724 ---------+---------+-------------+------------
1735 drop table p1, p2 cascade;
1736 NOTICE: drop cascades to 2 other objects
1737 DETAIL: drop cascades to table c1
1738 drop cascades to table gc1
1739 -- test attinhcount tracking with merged columns
1740 create table depth0();
1741 create table depth1(c text) inherits (depth0);
1742 create table depth2() inherits (depth1);
1743 alter table depth0 add c text;
1744 NOTICE: merging definition of column "c" for child "depth1"
1745 select attrelid::regclass, attname, attinhcount, attislocal
1747 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1748 order by attrelid::regclass::text, attnum;
1749 attrelid | attname | attinhcount | attislocal
1750 ----------+---------+-------------+------------
1756 -- test renumbering of child-table columns in inherited operations
1757 create table p1 (f1 int);
1758 create table c1 (f2 text, f3 int) inherits (p1);
1759 alter table p1 add column a1 int check (a1 > 0);
1760 alter table p1 add column f2 text;
1761 NOTICE: merging definition of column "f2" for child "c1"
1762 insert into p1 values (1,2,'abc');
1763 insert into c1 values(11,'xyz',33,0); -- should fail
1764 ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1765 DETAIL: Failing row contains (11, xyz, 33, 0).
1766 insert into c1 values(11,'xyz',33,22);
1774 update p1 set a1 = a1 + 1, f2 = upper(f2);
1782 drop table p1 cascade;
1783 NOTICE: drop cascades to table c1
1784 -- test that operations with a dropped column do not try to reference
1786 create domain mytype as text;
1787 create temp table foo (f1 text, f2 mytype, f3 text);
1788 insert into foo values('bb','cc','dd');
1795 drop domain mytype cascade;
1796 NOTICE: drop cascades to column f2 of table foo
1803 insert into foo values('qq','rr');
1811 update foo set f3 = 'zz';
1819 select f3,max(f1) from foo group by f3;
1825 -- Simple tests for alter table column type
1826 alter table foo alter f1 TYPE integer; -- fails
1827 ERROR: column "f1" cannot be cast automatically to type integer
1828 HINT: You might need to specify "USING f1::integer".
1829 alter table foo alter f1 TYPE varchar(10);
1830 create table anothertab (atcol1 serial8, atcol2 boolean,
1831 constraint anothertab_chk check (atcol1 <= 3));
1832 insert into anothertab (atcol1, atcol2) values (default, true);
1833 insert into anothertab (atcol1, atcol2) values (default, false);
1834 select * from anothertab;
1841 alter table anothertab alter column atcol1 type boolean; -- fails
1842 ERROR: column "atcol1" cannot be cast automatically to type boolean
1843 HINT: You might need to specify "USING atcol1::boolean".
1844 alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails
1845 ERROR: result of USING clause for column "atcol1" cannot be cast automatically to type boolean
1846 HINT: You might need to add an explicit cast.
1847 alter table anothertab alter column atcol1 type integer;
1848 select * from anothertab;
1855 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1856 ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1857 DETAIL: Failing row contains (45, null).
1858 insert into anothertab (atcol1, atcol2) values (default, null);
1859 select * from anothertab;
1867 alter table anothertab alter column atcol2 type text
1868 using case when atcol2 is true then 'IT WAS TRUE'
1869 when atcol2 is false then 'IT WAS FALSE'
1870 else 'IT WAS NULL!' end;
1871 select * from anothertab;
1873 --------+--------------
1879 alter table anothertab alter column atcol1 type boolean
1880 using case when atcol1 % 2 = 0 then true else false end; -- fails
1881 ERROR: default for column "atcol1" cannot be cast automatically to type boolean
1882 alter table anothertab alter column atcol1 drop default;
1883 alter table anothertab alter column atcol1 type boolean
1884 using case when atcol1 % 2 = 0 then true else false end; -- fails
1885 ERROR: operator does not exist: boolean <= integer
1886 HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
1887 alter table anothertab drop constraint anothertab_chk;
1888 alter table anothertab drop constraint anothertab_chk; -- fails
1889 ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
1890 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1891 NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
1892 alter table anothertab alter column atcol1 type boolean
1893 using case when atcol1 % 2 = 0 then true else false end;
1894 select * from anothertab;
1896 --------+--------------
1902 drop table anothertab;
1903 -- Test index handling in alter table column type (cf. bugs #15835, #15865)
1904 create table anothertab(f1 int primary key, f2 int unique,
1905 f3 int, f4 int, f5 int);
1906 alter table anothertab
1907 add exclude using btree (f3 with =);
1908 alter table anothertab
1909 add exclude using btree (f4 with =) where (f4 is not null);
1910 alter table anothertab
1911 add exclude using btree (f4 with =) where (f5 > 0);
1912 alter table anothertab
1914 create index on anothertab(f2,f3);
1915 create unique index on anothertab(f4);
1917 Table "public.anothertab"
1918 Column | Type | Collation | Nullable | Default
1919 --------+---------+-----------+----------+---------
1920 f1 | integer | | not null |
1926 "anothertab_pkey" PRIMARY KEY, btree (f1)
1927 "anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4)
1928 "anothertab_f2_f3_idx" btree (f2, f3)
1929 "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
1930 "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
1931 "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
1932 "anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0)
1933 "anothertab_f4_idx" UNIQUE, btree (f4)
1935 alter table anothertab alter column f1 type bigint;
1936 alter table anothertab
1937 alter column f2 type bigint,
1938 alter column f3 type bigint,
1939 alter column f4 type bigint;
1940 alter table anothertab alter column f5 type bigint;
1942 Table "public.anothertab"
1943 Column | Type | Collation | Nullable | Default
1944 --------+--------+-----------+----------+---------
1945 f1 | bigint | | not null |
1951 "anothertab_pkey" PRIMARY KEY, btree (f1)
1952 "anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4)
1953 "anothertab_f2_f3_idx" btree (f2, f3)
1954 "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
1955 "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
1956 "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
1957 "anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0)
1958 "anothertab_f4_idx" UNIQUE, btree (f4)
1960 drop table anothertab;
1961 create table another (f1 int, f2 text);
1962 insert into another values(1, 'one');
1963 insert into another values(2, 'two');
1964 insert into another values(3, 'three');
1965 select * from another;
1974 alter f1 type text using f2 || ' more',
1975 alter f2 type bigint using f1 * 10;
1976 select * from another;
1986 create table tab1 (a int, b text);
1987 create table tab2 (x int, y tab1);
1988 alter table tab1 alter column b type varchar; -- fails
1989 ERROR: cannot alter table "tab1" because column "tab2.y" uses its row type
1990 -- Alter column type that's part of a partitioned index
1991 create table at_partitioned (a int, b text) partition by range (a);
1992 create table at_part_1 partition of at_partitioned for values from (0) to (1000);
1993 insert into at_partitioned values (512, '0.123');
1994 create table at_part_2 (b text, a int);
1995 insert into at_part_2 values ('1.234', 1024);
1996 create index on at_partitioned (b);
1997 create index on at_partitioned (a);
1999 Table "public.at_part_1"
2000 Column | Type | Collation | Nullable | Default
2001 --------+---------+-----------+----------+---------
2004 Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
2006 "at_part_1_a_idx" btree (a)
2007 "at_part_1_b_idx" btree (b)
2010 Table "public.at_part_2"
2011 Column | Type | Collation | Nullable | Default
2012 --------+---------+-----------+----------+---------
2016 alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000);
2018 Table "public.at_part_2"
2019 Column | Type | Collation | Nullable | Default
2020 --------+---------+-----------+----------+---------
2023 Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
2025 "at_part_2_a_idx" btree (a)
2026 "at_part_2_b_idx" btree (b)
2028 alter table at_partitioned alter column b type numeric using b::numeric;
2030 Table "public.at_part_1"
2031 Column | Type | Collation | Nullable | Default
2032 --------+---------+-----------+----------+---------
2035 Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
2037 "at_part_1_a_idx" btree (a)
2038 "at_part_1_b_idx" btree (b)
2041 Table "public.at_part_2"
2042 Column | Type | Collation | Nullable | Default
2043 --------+---------+-----------+----------+---------
2046 Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
2048 "at_part_2_a_idx" btree (a)
2049 "at_part_2_b_idx" btree (b)
2051 drop table at_partitioned;
2052 -- Alter column type when no table rewrite is required
2053 -- Also check that comments are preserved
2054 create table at_partitioned(id int, name varchar(64), unique (id, name))
2055 partition by hash(id);
2056 comment on constraint at_partitioned_id_name_key on at_partitioned is 'parent constraint';
2057 comment on index at_partitioned_id_name_key is 'parent index';
2058 create table at_partitioned_0 partition of at_partitioned
2059 for values with (modulus 2, remainder 0);
2060 comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint';
2061 comment on index at_partitioned_0_id_name_key is 'child 0 index';
2062 create table at_partitioned_1 partition of at_partitioned
2063 for values with (modulus 2, remainder 1);
2064 comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint';
2065 comment on index at_partitioned_1_id_name_key is 'child 1 index';
2066 insert into at_partitioned values(1, 'foo');
2067 insert into at_partitioned values(3, 'bar');
2068 create temp table old_oids as
2069 select relname, oid as oldoid, relfilenode as oldfilenode
2070 from pg_class where relname like 'at_partitioned%';
2072 c.oid = oldoid as orig_oid,
2075 when c.oid then 'own'
2076 when oldfilenode then 'orig'
2079 obj_description(c.oid, 'pg_class') as desc
2080 from pg_class c left join old_oids using (relname)
2081 where relname like 'at_partitioned%'
2083 relname | orig_oid | storage | desc
2084 ------------------------------+----------+---------+---------------
2085 at_partitioned | t | none |
2086 at_partitioned_0 | t | own |
2087 at_partitioned_0_id_name_key | t | own | child 0 index
2088 at_partitioned_1 | t | own |
2089 at_partitioned_1_id_name_key | t | own | child 1 index
2090 at_partitioned_id_name_key | t | none | parent index
2093 select conname, obj_description(oid, 'pg_constraint') as desc
2094 from pg_constraint where conname like 'at_partitioned%'
2097 ------------------------------+--------------------
2098 at_partitioned_0_id_name_key | child 0 constraint
2099 at_partitioned_1_id_name_key | child 1 constraint
2100 at_partitioned_id_name_key | parent constraint
2103 alter table at_partitioned alter column name type varchar(127);
2104 -- Note: these tests currently show the wrong behavior for comments :-(
2106 c.oid = oldoid as orig_oid,
2109 when c.oid then 'own'
2110 when oldfilenode then 'orig'
2113 obj_description(c.oid, 'pg_class') as desc
2114 from pg_class c left join old_oids using (relname)
2115 where relname like 'at_partitioned%'
2117 relname | orig_oid | storage | desc
2118 ------------------------------+----------+---------+--------------
2119 at_partitioned | t | none |
2120 at_partitioned_0 | t | own |
2121 at_partitioned_0_id_name_key | f | own | parent index
2122 at_partitioned_1 | t | own |
2123 at_partitioned_1_id_name_key | f | own | parent index
2124 at_partitioned_id_name_key | f | none | parent index
2127 select conname, obj_description(oid, 'pg_constraint') as desc
2128 from pg_constraint where conname like 'at_partitioned%'
2131 ------------------------------+-------------------
2132 at_partitioned_0_id_name_key |
2133 at_partitioned_1_id_name_key |
2134 at_partitioned_id_name_key | parent constraint
2137 -- Don't remove this DROP, it exposes bug #15672
2138 drop table at_partitioned;
2139 -- disallow recursive containment of row types
2140 create temp table recur1 (f1 int);
2141 alter table recur1 add column f2 recur1; -- fails
2142 ERROR: composite type recur1 cannot be made a member of itself
2143 alter table recur1 add column f2 recur1[]; -- fails
2144 ERROR: composite type recur1 cannot be made a member of itself
2145 create domain array_of_recur1 as recur1[];
2146 alter table recur1 add column f2 array_of_recur1; -- fails
2147 ERROR: composite type recur1 cannot be made a member of itself
2148 create temp table recur2 (f1 int, f2 recur1);
2149 alter table recur1 add column f2 recur2; -- fails
2150 ERROR: composite type recur1 cannot be made a member of itself
2151 alter table recur1 add column f2 int;
2152 alter table recur1 alter column f2 type recur2; -- fails
2153 ERROR: composite type recur1 cannot be made a member of itself
2154 -- SET STORAGE may need to add a TOAST table
2155 create table test_storage (a text);
2156 alter table test_storage alter a set storage plain;
2157 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
2158 alter table test_storage alter a set storage extended; -- re-add TOAST table
2159 select reltoastrelid <> 0 as has_toast_table
2161 where oid = 'test_storage'::regclass;
2167 -- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
2168 CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
2169 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
2171 Table "public.test_inh_check"
2172 Column | Type | Collation | Nullable | Default
2173 --------+------------------+-----------+----------+---------
2174 a | double precision | | |
2175 b | double precision | | |
2177 "test_inh_check_a_check" CHECK (a > 10.2::double precision)
2178 Number of child tables: 1 (Use \d+ to list them.)
2180 \d test_inh_check_child
2181 Table "public.test_inh_check_child"
2182 Column | Type | Collation | Nullable | Default
2183 --------+------------------+-----------+----------+---------
2184 a | double precision | | |
2185 b | double precision | | |
2187 "test_inh_check_a_check" CHECK (a > 10.2::double precision)
2188 Inherits: test_inh_check
2190 select relname, conname, coninhcount, conislocal, connoinherit
2191 from pg_constraint c, pg_class r
2192 where relname like 'test_inh_check%' and c.conrelid = r.oid
2194 relname | conname | coninhcount | conislocal | connoinherit
2195 ----------------------+------------------------+-------------+------------+--------------
2196 test_inh_check | test_inh_check_a_check | 0 | t | f
2197 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2200 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
2202 Table "public.test_inh_check"
2203 Column | Type | Collation | Nullable | Default
2204 --------+------------------+-----------+----------+---------
2206 b | double precision | | |
2208 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2209 Number of child tables: 1 (Use \d+ to list them.)
2211 \d test_inh_check_child
2212 Table "public.test_inh_check_child"
2213 Column | Type | Collation | Nullable | Default
2214 --------+------------------+-----------+----------+---------
2216 b | double precision | | |
2218 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2219 Inherits: test_inh_check
2221 select relname, conname, coninhcount, conislocal, connoinherit
2222 from pg_constraint c, pg_class r
2223 where relname like 'test_inh_check%' and c.conrelid = r.oid
2225 relname | conname | coninhcount | conislocal | connoinherit
2226 ----------------------+------------------------+-------------+------------+--------------
2227 test_inh_check | test_inh_check_a_check | 0 | t | f
2228 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2231 -- also try noinherit, local, and local+inherited cases
2232 ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT;
2233 ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000);
2234 ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1);
2235 ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1);
2236 NOTICE: merging constraint "bmerged" with inherited definition
2238 Table "public.test_inh_check"
2239 Column | Type | Collation | Nullable | Default
2240 --------+------------------+-----------+----------+---------
2242 b | double precision | | |
2244 "bmerged" CHECK (b > 1::double precision)
2245 "bnoinherit" CHECK (b > 100::double precision) NO INHERIT
2246 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2247 Number of child tables: 1 (Use \d+ to list them.)
2249 \d test_inh_check_child
2250 Table "public.test_inh_check_child"
2251 Column | Type | Collation | Nullable | Default
2252 --------+------------------+-----------+----------+---------
2254 b | double precision | | |
2256 "blocal" CHECK (b < 1000::double precision)
2257 "bmerged" CHECK (b > 1::double precision)
2258 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2259 Inherits: test_inh_check
2261 select relname, conname, coninhcount, conislocal, connoinherit
2262 from pg_constraint c, pg_class r
2263 where relname like 'test_inh_check%' and c.conrelid = r.oid
2265 relname | conname | coninhcount | conislocal | connoinherit
2266 ----------------------+------------------------+-------------+------------+--------------
2267 test_inh_check | bmerged | 0 | t | f
2268 test_inh_check | bnoinherit | 0 | t | t
2269 test_inh_check | test_inh_check_a_check | 0 | t | f
2270 test_inh_check_child | blocal | 0 | t | f
2271 test_inh_check_child | bmerged | 1 | t | f
2272 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2275 ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric;
2276 NOTICE: merging constraint "bmerged" with inherited definition
2278 Table "public.test_inh_check"
2279 Column | Type | Collation | Nullable | Default
2280 --------+---------+-----------+----------+---------
2284 "bmerged" CHECK (b::double precision > 1::double precision)
2285 "bnoinherit" CHECK (b::double precision > 100::double precision) NO INHERIT
2286 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2287 Number of child tables: 1 (Use \d+ to list them.)
2289 \d test_inh_check_child
2290 Table "public.test_inh_check_child"
2291 Column | Type | Collation | Nullable | Default
2292 --------+---------+-----------+----------+---------
2296 "blocal" CHECK (b::double precision < 1000::double precision)
2297 "bmerged" CHECK (b::double precision > 1::double precision)
2298 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2299 Inherits: test_inh_check
2301 select relname, conname, coninhcount, conislocal, connoinherit
2302 from pg_constraint c, pg_class r
2303 where relname like 'test_inh_check%' and c.conrelid = r.oid
2305 relname | conname | coninhcount | conislocal | connoinherit
2306 ----------------------+------------------------+-------------+------------+--------------
2307 test_inh_check | bmerged | 0 | t | f
2308 test_inh_check | bnoinherit | 0 | t | t
2309 test_inh_check | test_inh_check_a_check | 0 | t | f
2310 test_inh_check_child | blocal | 0 | t | f
2311 test_inh_check_child | bmerged | 1 | t | f
2312 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2315 -- ALTER COLUMN TYPE with different schema in children
2316 -- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com
2317 CREATE TABLE test_type_diff (f1 int);
2318 CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff);
2319 ALTER TABLE test_type_diff ADD COLUMN f2 int;
2320 INSERT INTO test_type_diff_c VALUES (1, 2, 3);
2321 ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint;
2322 CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8);
2323 CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2);
2324 CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4);
2325 CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8);
2326 ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2;
2327 ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2;
2328 ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2;
2329 INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3);
2330 INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6);
2331 INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9);
2332 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8;
2333 -- whole-row references are disallowed
2334 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2));
2335 ERROR: cannot convert whole-row table reference
2336 DETAIL: USING expression contains a whole-row table reference.
2337 -- check for rollback of ANALYZE corrupting table property flags (bug #11638)
2338 CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text);
2339 CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text);
2341 ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey;
2342 ANALYZE check_fk_presence_2;
2344 \d check_fk_presence_2
2345 Table "public.check_fk_presence_2"
2346 Column | Type | Collation | Nullable | Default
2347 --------+---------+-----------+----------+---------
2350 Foreign-key constraints:
2351 "check_fk_presence_2_id_fkey" FOREIGN KEY (id) REFERENCES check_fk_presence_1(id)
2353 DROP TABLE check_fk_presence_1, check_fk_presence_2;
2354 -- check column addition within a view (bug #14876)
2355 create table at_base_table(id int, stuff text);
2356 insert into at_base_table values (23, 'skidoo');
2357 create view at_view_1 as select * from at_base_table bt;
2358 create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1;
2360 View "public.at_view_1"
2361 Column | Type | Collation | Nullable | Default | Storage | Description
2362 --------+---------+-----------+----------+---------+----------+-------------
2363 id | integer | | | | plain |
2364 stuff | text | | | | extended |
2368 FROM at_base_table bt;
2371 View "public.at_view_2"
2372 Column | Type | Collation | Nullable | Default | Storage | Description
2373 --------+---------+-----------+----------+---------+----------+-------------
2374 id | integer | | | | plain |
2375 stuff | text | | | | extended |
2376 j | json | | | | extended |
2383 explain (verbose, costs off) select * from at_view_2;
2385 ----------------------------------------------------------
2386 Seq Scan on public.at_base_table bt
2387 Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff))
2390 select * from at_view_2;
2392 ----+--------+----------------------------
2393 23 | skidoo | {"id":23,"stuff":"skidoo"}
2396 create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt;
2398 View "public.at_view_1"
2399 Column | Type | Collation | Nullable | Default | Storage | Description
2400 --------+---------+-----------+----------+---------+----------+-------------
2401 id | integer | | | | plain |
2402 stuff | text | | | | extended |
2403 more | integer | | | | plain |
2408 FROM at_base_table bt;
2411 View "public.at_view_2"
2412 Column | Type | Collation | Nullable | Default | Storage | Description
2413 --------+---------+-----------+----------+---------+----------+-------------
2414 id | integer | | | | plain |
2415 stuff | text | | | | extended |
2416 j | json | | | | extended |
2423 explain (verbose, costs off) select * from at_view_2;
2425 ----------------------------------------------------------------
2426 Seq Scan on public.at_base_table bt
2427 Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff, NULL))
2430 select * from at_view_2;
2432 ----+--------+----------------------------------------
2433 23 | skidoo | {"id":23,"stuff":"skidoo","more":null}
2436 drop view at_view_2;
2437 drop view at_view_1;
2438 drop table at_base_table;
2439 -- check adding a column not iself requiring a rewrite, together with
2440 -- a column requiring a default (bug #16038)
2441 -- ensure that rewrites aren't silently optimized away, removing the
2442 -- value of the test
2443 CREATE OR REPLACE FUNCTION evtrig_rewrite_log() RETURNS event_trigger
2444 LANGUAGE plpgsql AS $$
2446 RAISE WARNING 'rewriting table %',
2447 pg_event_trigger_table_rewrite_oid()::regclass;
2450 CREATE EVENT TRIGGER evtrig_rewrite_log ON table_rewrite
2451 EXECUTE PROCEDURE evtrig_rewrite_log();
2452 CREATE TABLE rewrite_test(col text);
2453 INSERT INTO rewrite_test VALUES ('something');
2454 INSERT INTO rewrite_test VALUES (NULL);
2455 -- empty[12] doesn't need rewrite, but notempty[12]_rewrite will force one
2456 ALTER TABLE rewrite_test
2457 ADD COLUMN empty1 text,
2458 ADD COLUMN notempty1_rewrite serial;
2459 WARNING: rewriting table rewrite_test
2460 ALTER TABLE rewrite_test
2461 ADD COLUMN notempty2_rewrite serial,
2462 ADD COLUMN empty2 text;
2463 WARNING: rewriting table rewrite_test
2464 -- also check that fast defaults cause no problem, first without rewrite
2465 ALTER TABLE rewrite_test
2466 ADD COLUMN empty3 text,
2467 ADD COLUMN notempty3_norewrite int default 42;
2468 ALTER TABLE rewrite_test
2469 ADD COLUMN notempty4_norewrite int default 42,
2470 ADD COLUMN empty4 text;
2471 -- then with rewrite
2472 ALTER TABLE rewrite_test
2473 ADD COLUMN empty5 text,
2474 ADD COLUMN notempty5_norewrite int default 42,
2475 ADD COLUMN notempty5_rewrite serial;
2476 WARNING: rewriting table rewrite_test
2477 ALTER TABLE rewrite_test
2478 ADD COLUMN notempty6_rewrite serial,
2479 ADD COLUMN empty6 text,
2480 ADD COLUMN notempty6_norewrite int default 42;
2481 WARNING: rewriting table rewrite_test
2483 drop event trigger evtrig_rewrite_log;
2484 drop function evtrig_rewrite_log();
2485 DROP TABLE rewrite_test;
2489 drop type lockmodes;
2490 ERROR: type "lockmodes" does not exist
2491 create type lockmodes as enum (
2496 ,'ShareUpdateExclusiveLock'
2498 ,'ShareRowExclusiveLock'
2500 ,'AccessExclusiveLock'
2503 ERROR: view "my_locks" does not exist
2504 create or replace view my_locks as
2505 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
2506 from pg_locks l join pg_class c on l.relation = c.oid
2507 where virtualtransaction = (
2508 select virtualtransaction
2510 where transactionid = txid_current()::integer)
2511 and locktype = 'relation'
2512 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
2513 and c.relname != 'my_locks'
2515 create table alterlock (f1 int primary key, f2 text);
2516 insert into alterlock values (1, 'foo');
2517 create table alterlock2 (f3 int primary key, f1 int);
2518 insert into alterlock2 values (1, 1);
2519 begin; alter table alterlock alter column f2 set statistics 150;
2520 select * from my_locks order by 1;
2521 relname | max_lockmode
2522 -----------+--------------------------
2523 alterlock | ShareUpdateExclusiveLock
2527 begin; alter table alterlock cluster on alterlock_pkey;
2528 select * from my_locks order by 1;
2529 relname | max_lockmode
2530 ----------------+--------------------------
2531 alterlock | ShareUpdateExclusiveLock
2532 alterlock_pkey | ShareUpdateExclusiveLock
2536 begin; alter table alterlock set without cluster;
2537 select * from my_locks order by 1;
2538 relname | max_lockmode
2539 -----------+--------------------------
2540 alterlock | ShareUpdateExclusiveLock
2544 begin; alter table alterlock set (fillfactor = 100);
2545 select * from my_locks order by 1;
2546 relname | max_lockmode
2547 -----------+--------------------------
2548 alterlock | ShareUpdateExclusiveLock
2549 pg_toast | ShareUpdateExclusiveLock
2553 begin; alter table alterlock reset (fillfactor);
2554 select * from my_locks order by 1;
2555 relname | max_lockmode
2556 -----------+--------------------------
2557 alterlock | ShareUpdateExclusiveLock
2558 pg_toast | ShareUpdateExclusiveLock
2562 begin; alter table alterlock set (toast.autovacuum_enabled = off);
2563 select * from my_locks order by 1;
2564 relname | max_lockmode
2565 -----------+--------------------------
2566 alterlock | ShareUpdateExclusiveLock
2567 pg_toast | ShareUpdateExclusiveLock
2571 begin; alter table alterlock set (autovacuum_enabled = off);
2572 select * from my_locks order by 1;
2573 relname | max_lockmode
2574 -----------+--------------------------
2575 alterlock | ShareUpdateExclusiveLock
2576 pg_toast | ShareUpdateExclusiveLock
2580 begin; alter table alterlock alter column f2 set (n_distinct = 1);
2581 select * from my_locks order by 1;
2582 relname | max_lockmode
2583 -----------+--------------------------
2584 alterlock | ShareUpdateExclusiveLock
2588 -- test that mixing options with different lock levels works as expected
2589 begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80);
2590 select * from my_locks order by 1;
2591 relname | max_lockmode
2592 -----------+--------------------------
2593 alterlock | ShareUpdateExclusiveLock
2594 pg_toast | ShareUpdateExclusiveLock
2598 begin; alter table alterlock alter column f2 set storage extended;
2599 select * from my_locks order by 1;
2600 relname | max_lockmode
2601 -----------+---------------------
2602 alterlock | AccessExclusiveLock
2606 begin; alter table alterlock alter column f2 set default 'x';
2607 select * from my_locks order by 1;
2608 relname | max_lockmode
2609 -----------+---------------------
2610 alterlock | AccessExclusiveLock
2615 create trigger ttdummy
2616 before delete or update on alterlock
2620 select * from my_locks order by 1;
2621 relname | max_lockmode
2622 -----------+-----------------------
2623 alterlock | ShareRowExclusiveLock
2628 select * from my_locks order by 1;
2629 relname | max_lockmode
2630 ---------+--------------
2633 alter table alterlock2 add foreign key (f1) references alterlock (f1);
2634 select * from my_locks order by 1;
2635 relname | max_lockmode
2636 -----------------+-----------------------
2637 alterlock | ShareRowExclusiveLock
2638 alterlock2 | ShareRowExclusiveLock
2639 alterlock2_pkey | AccessShareLock
2640 alterlock_pkey | AccessShareLock
2645 alter table alterlock2
2646 add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID;
2647 select * from my_locks order by 1;
2648 relname | max_lockmode
2649 ------------+-----------------------
2650 alterlock | ShareRowExclusiveLock
2651 alterlock2 | ShareRowExclusiveLock
2656 alter table alterlock2 validate constraint alterlock2nv;
2657 select * from my_locks order by 1;
2658 relname | max_lockmode
2659 -----------------+--------------------------
2660 alterlock | RowShareLock
2661 alterlock2 | ShareUpdateExclusiveLock
2662 alterlock2_pkey | AccessShareLock
2663 alterlock_pkey | AccessShareLock
2667 create or replace view my_locks as
2668 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
2669 from pg_locks l join pg_class c on l.relation = c.oid
2670 where virtualtransaction = (
2671 select virtualtransaction
2673 where transactionid = txid_current()::integer)
2674 and locktype = 'relation'
2675 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
2676 and c.relname = 'my_locks'
2679 alter table my_locks set (autovacuum_enabled = false);
2680 ERROR: unrecognized parameter "autovacuum_enabled"
2681 alter view my_locks set (autovacuum_enabled = false);
2682 ERROR: unrecognized parameter "autovacuum_enabled"
2683 alter table my_locks reset (autovacuum_enabled);
2684 alter view my_locks reset (autovacuum_enabled);
2686 alter view my_locks set (security_barrier=off);
2687 select * from my_locks order by 1;
2688 relname | max_lockmode
2689 ----------+---------------------
2690 my_locks | AccessExclusiveLock
2693 alter view my_locks reset (security_barrier);
2695 -- this test intentionally applies the ALTER TABLE command against a view, but
2696 -- uses a view option so we expect this to succeed. This form of SQL is
2697 -- accepted for historical reasons, as shown in the docs for ALTER VIEW
2699 alter table my_locks set (security_barrier=off);
2700 select * from my_locks order by 1;
2701 relname | max_lockmode
2702 ----------+---------------------
2703 my_locks | AccessExclusiveLock
2706 alter table my_locks reset (security_barrier);
2709 drop table alterlock2;
2710 drop table alterlock;
2712 drop type lockmodes;
2716 create function test_strict(text) returns text as
2717 'select coalesce($1, ''got passed a null'');'
2718 language sql returns null on null input;
2719 select test_strict(NULL);
2725 alter function test_strict(text) called on null input;
2726 select test_strict(NULL);
2732 create function non_strict(text) returns text as
2733 'select coalesce($1, ''got passed a null'');'
2734 language sql called on null input;
2735 select non_strict(NULL);
2741 alter function non_strict(text) returns null on null input;
2742 select non_strict(NULL);
2749 -- alter object set schema
2751 create schema alter1;
2752 create schema alter2;
2753 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
2754 create view alter1.v1 as select * from alter1.t1;
2755 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
2756 create domain alter1.posint integer check (value > 0);
2757 create type alter1.ctype as (f1 int, f2 text);
2758 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
2759 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
2760 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
2761 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
2762 operator 1 alter1.=(alter1.ctype, alter1.ctype);
2763 create conversion alter1.latin1_to_utf8 for 'latin1' to 'utf8' from iso8859_1_to_utf8;
2764 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
2765 create text search configuration alter1.cfg(parser = alter1.prs);
2766 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
2767 create text search dictionary alter1.dict(template = alter1.tmpl);
2768 insert into alter1.t1(f2) values(11);
2769 insert into alter1.t1(f2) values(12);
2770 alter table alter1.t1 set schema alter1; -- no-op, same schema
2771 alter table alter1.t1 set schema alter2;
2772 alter table alter1.v1 set schema alter2;
2773 alter function alter1.plus1(int) set schema alter2;
2774 alter domain alter1.posint set schema alter2;
2775 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
2776 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
2777 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
2778 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
2779 alter type alter1.ctype set schema alter1; -- no-op, same schema
2780 alter type alter1.ctype set schema alter2;
2781 alter conversion alter1.latin1_to_utf8 set schema alter2;
2782 alter text search parser alter1.prs set schema alter2;
2783 alter text search configuration alter1.cfg set schema alter2;
2784 alter text search template alter1.tmpl set schema alter2;
2785 alter text search dictionary alter1.dict set schema alter2;
2786 -- this should succeed because nothing is left in alter1
2788 insert into alter2.t1(f2) values(13);
2789 insert into alter2.t1(f2) values(14);
2790 select * from alter2.t1;
2799 select * from alter2.v1;
2808 select alter2.plus1(41);
2815 drop schema alter2 cascade;
2816 NOTICE: drop cascades to 13 other objects
2817 DETAIL: drop cascades to table alter2.t1
2818 drop cascades to view alter2.v1
2819 drop cascades to function alter2.plus1(integer)
2820 drop cascades to type alter2.posint
2821 drop cascades to type alter2.ctype
2822 drop cascades to function alter2.same(alter2.ctype,alter2.ctype)
2823 drop cascades to operator alter2.=(alter2.ctype,alter2.ctype)
2824 drop cascades to operator family alter2.ctype_hash_ops for access method hash
2825 drop cascades to conversion alter2.latin1_to_utf8
2826 drop cascades to text search parser alter2.prs
2827 drop cascades to text search configuration alter2.cfg
2828 drop cascades to text search template alter2.tmpl
2829 drop cascades to text search dictionary alter2.dict
2833 CREATE TYPE test_type AS (a int);
2835 Composite type "public.test_type"
2836 Column | Type | Collation | Nullable | Default
2837 --------+---------+-----------+----------+---------
2840 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
2841 ERROR: relation "nosuchtype" does not exist
2842 ALTER TYPE test_type ADD ATTRIBUTE b text;
2844 Composite type "public.test_type"
2845 Column | Type | Collation | Nullable | Default
2846 --------+---------+-----------+----------+---------
2850 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
2851 ERROR: column "b" of relation "test_type" already exists
2852 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
2854 Composite type "public.test_type"
2855 Column | Type | Collation | Nullable | Default
2856 --------+-------------------+-----------+----------+---------
2858 b | character varying | | |
2860 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
2862 Composite type "public.test_type"
2863 Column | Type | Collation | Nullable | Default
2864 --------+---------+-----------+----------+---------
2868 ALTER TYPE test_type DROP ATTRIBUTE b;
2870 Composite type "public.test_type"
2871 Column | Type | Collation | Nullable | Default
2872 --------+---------+-----------+----------+---------
2875 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
2876 ERROR: column "c" of relation "test_type" does not exist
2877 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
2878 NOTICE: column "c" of relation "test_type" does not exist, skipping
2879 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
2881 Composite type "public.test_type"
2882 Column | Type | Collation | Nullable | Default
2883 --------+---------+-----------+----------+---------
2886 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
2887 ERROR: column "a" does not exist
2888 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
2890 Composite type "public.test_type"
2891 Column | Type | Collation | Nullable | Default
2892 --------+---------+-----------+----------+---------
2895 DROP TYPE test_type;
2896 CREATE TYPE test_type1 AS (a int, b text);
2897 CREATE TABLE test_tbl1 (x int, y test_type1);
2898 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
2899 ERROR: cannot alter type "test_type1" because column "test_tbl1.y" uses it
2900 CREATE TYPE test_type2 AS (a int, b text);
2901 CREATE TABLE test_tbl2 OF test_type2;
2902 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
2904 Composite type "public.test_type2"
2905 Column | Type | Collation | Nullable | Default
2906 --------+---------+-----------+----------+---------
2911 Table "public.test_tbl2"
2912 Column | Type | Collation | Nullable | Default
2913 --------+---------+-----------+----------+---------
2916 Number of child tables: 1 (Use \d+ to list them.)
2917 Typed table of type: test_type2
2919 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
2920 ERROR: cannot alter type "test_type2" because it is the type of a typed table
2921 HINT: Use ALTER ... CASCADE to alter the typed tables too.
2922 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
2924 Composite type "public.test_type2"
2925 Column | Type | Collation | Nullable | Default
2926 --------+---------+-----------+----------+---------
2932 Table "public.test_tbl2"
2933 Column | Type | Collation | Nullable | Default
2934 --------+---------+-----------+----------+---------
2938 Number of child tables: 1 (Use \d+ to list them.)
2939 Typed table of type: test_type2
2941 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
2942 ERROR: cannot alter type "test_type2" because it is the type of a typed table
2943 HINT: Use ALTER ... CASCADE to alter the typed tables too.
2944 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
2946 Composite type "public.test_type2"
2947 Column | Type | Collation | Nullable | Default
2948 --------+-------------------+-----------+----------+---------
2950 b | character varying | | |
2954 Table "public.test_tbl2"
2955 Column | Type | Collation | Nullable | Default
2956 --------+-------------------+-----------+----------+---------
2958 b | character varying | | |
2960 Number of child tables: 1 (Use \d+ to list them.)
2961 Typed table of type: test_type2
2963 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
2964 ERROR: cannot alter type "test_type2" because it is the type of a typed table
2965 HINT: Use ALTER ... CASCADE to alter the typed tables too.
2966 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
2968 Composite type "public.test_type2"
2969 Column | Type | Collation | Nullable | Default
2970 --------+---------+-----------+----------+---------
2975 Table "public.test_tbl2"
2976 Column | Type | Collation | Nullable | Default
2977 --------+---------+-----------+----------+---------
2980 Number of child tables: 1 (Use \d+ to list them.)
2981 Typed table of type: test_type2
2983 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
2984 ERROR: cannot alter type "test_type2" because it is the type of a typed table
2985 HINT: Use ALTER ... CASCADE to alter the typed tables too.
2986 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
2988 Composite type "public.test_type2"
2989 Column | Type | Collation | Nullable | Default
2990 --------+---------+-----------+----------+---------
2995 Table "public.test_tbl2"
2996 Column | Type | Collation | Nullable | Default
2997 --------+---------+-----------+----------+---------
3000 Number of child tables: 1 (Use \d+ to list them.)
3001 Typed table of type: test_type2
3003 \d test_tbl2_subclass
3004 Table "public.test_tbl2_subclass"
3005 Column | Type | Collation | Nullable | Default
3006 --------+---------+-----------+----------+---------
3011 DROP TABLE test_tbl2_subclass;
3012 CREATE TYPE test_typex AS (a int, b text);
3013 CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0));
3014 ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails
3015 ERROR: cannot drop column a of composite type test_typex because other objects depend on it
3016 DETAIL: constraint test_tblx_y_check on table test_tblx depends on column a of composite type test_typex
3017 HINT: Use DROP ... CASCADE to drop the dependent objects too.
3018 ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE;
3019 NOTICE: drop cascades to constraint test_tblx_y_check on table test_tblx
3021 Table "public.test_tblx"
3022 Column | Type | Collation | Nullable | Default
3023 --------+------------+-----------+----------+---------
3025 y | test_typex | | |
3027 DROP TABLE test_tblx;
3028 DROP TYPE test_typex;
3029 -- This test isn't that interesting on its own, but the purpose is to leave
3030 -- behind a table to test pg_upgrade with. The table has a composite type
3031 -- column in it, and the composite type has a dropped attribute.
3032 CREATE TYPE test_type3 AS (a int);
3033 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
3034 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
3035 CREATE TYPE test_type_empty AS ();
3036 DROP TYPE test_type_empty;
3038 -- typed tables: OF / NOT OF
3040 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
3041 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
3042 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
3043 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
3044 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
3045 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
3046 CREATE TABLE tt4 (x int); -- too few columns
3047 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
3048 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
3049 CREATE TABLE tt7 (x int, q text, y numeric(8,2));
3050 ALTER TABLE tt7 DROP q; -- OK
3051 ALTER TABLE tt0 OF tt_t0;
3052 ALTER TABLE tt1 OF tt_t0;
3053 ERROR: table "tt1" has different type for column "y"
3054 ALTER TABLE tt2 OF tt_t0;
3055 ERROR: table "tt2" has different type for column "y"
3056 ALTER TABLE tt3 OF tt_t0;
3057 ERROR: table has column "y" where type requires "x"
3058 ALTER TABLE tt4 OF tt_t0;
3059 ERROR: table is missing column "y"
3060 ALTER TABLE tt5 OF tt_t0;
3061 ERROR: table has extra column "z"
3062 ALTER TABLE tt6 OF tt_t0;
3063 ERROR: typed tables cannot inherit
3064 ALTER TABLE tt7 OF tt_t0;
3065 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
3066 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
3067 ALTER TABLE tt7 NOT OF;
3070 Column | Type | Collation | Nullable | Default
3071 --------+--------------+-----------+----------+---------
3073 y | numeric(8,2) | | |
3075 -- make sure we can drop a constraint on the parent but it remains on the child
3076 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
3077 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
3078 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
3080 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
3081 ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
3082 DETAIL: Failing row contains (null).
3083 DROP TABLE test_drop_constr_parent CASCADE;
3084 NOTICE: drop cascades to table test_drop_constr_child
3088 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
3089 NOTICE: relation "tt8" does not exist, skipping
3090 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
3091 NOTICE: relation "tt8" does not exist, skipping
3092 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
3093 NOTICE: relation "tt8" does not exist, skipping
3094 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
3095 NOTICE: relation "tt8" does not exist, skipping
3096 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
3097 NOTICE: relation "tt8" does not exist, skipping
3098 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
3099 NOTICE: relation "tt8" does not exist, skipping
3100 CREATE TABLE tt8(a int);
3101 CREATE SCHEMA alter2;
3102 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
3103 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
3104 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
3105 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
3106 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
3107 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
3110 Column | Type | Collation | Nullable | Default
3111 --------+---------+-----------+----------+---------
3113 f1 | integer | | not null | 0
3115 "xxx" PRIMARY KEY, btree (f1)
3117 "tt8_f_check" CHECK (f1 >= 0 AND f1 <= 10)
3119 DROP TABLE alter2.tt8;
3122 -- Check conflicts between index and CHECK constraint names
3124 CREATE TABLE tt9(c integer);
3125 ALTER TABLE tt9 ADD CHECK(c > 1);
3126 ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name
3127 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
3128 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name
3129 ERROR: constraint "foo" for relation "tt9" already exists
3130 ALTER TABLE tt9 ADD UNIQUE(c);
3131 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
3132 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name
3133 ERROR: relation "tt9_c_key" already exists
3134 ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name
3135 ERROR: constraint "foo" for relation "tt9" already exists
3136 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name
3137 ERROR: constraint "tt9_c_key" for relation "tt9" already exists
3138 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
3139 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
3142 Column | Type | Collation | Nullable | Default
3143 --------+---------+-----------+----------+---------
3146 "tt9_c_key" UNIQUE CONSTRAINT, btree (c)
3147 "tt9_c_key1" UNIQUE CONSTRAINT, btree (c)
3148 "tt9_c_key3" UNIQUE CONSTRAINT, btree (c)
3151 "tt9_c_check" CHECK (c > 1)
3152 "tt9_c_check1" CHECK (c > 2)
3153 "tt9_c_key2" CHECK (c > 6)
3156 -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
3157 CREATE TABLE comment_test (
3159 positive_col int CHECK (positive_col > 0),
3161 CONSTRAINT comment_test_pk PRIMARY KEY (id));
3162 CREATE INDEX comment_test_index ON comment_test(indexed_col);
3163 COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
3164 COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
3165 COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
3166 COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
3167 COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
3168 SELECT col_description('comment_test'::regclass, 1) as comment;
3170 -----------------------------
3171 Column 'id' on comment_test
3174 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
3176 --------------------+-----------------------------------------------
3177 comment_test_index | Simple index on comment_test
3178 comment_test_pk | Index backing the PRIMARY KEY of comment_test
3181 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
3182 constraint | comment
3183 ---------------------------------+-----------------------------------------------
3184 comment_test_pk | PRIMARY KEY constraint of comment_test
3185 comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
3188 -- Change the datatype of all the columns. ALTER TABLE is optimized to not
3189 -- rebuild an index if the new data type is binary compatible with the old
3190 -- one. Check do a dummy ALTER TABLE that doesn't change the datatype
3191 -- first, to test that no-op codepath, and another one that does.
3192 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int;
3193 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text;
3194 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int;
3195 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
3196 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int;
3197 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint;
3198 -- Check that the comments are intact.
3199 SELECT col_description('comment_test'::regclass, 1) as comment;
3201 -----------------------------
3202 Column 'id' on comment_test
3205 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
3207 --------------------+-----------------------------------------------
3208 comment_test_index | Simple index on comment_test
3209 comment_test_pk | Index backing the PRIMARY KEY of comment_test
3212 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
3213 constraint | comment
3214 ---------------------------------+-----------------------------------------------
3215 comment_test_pk | PRIMARY KEY constraint of comment_test
3216 comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
3219 -- Check compatibility for foreign keys and comments. This is done
3220 -- separately as rebuilding the column type of the parent leads
3221 -- to an error and would reduce the test scope.
3222 CREATE TABLE comment_test_child (
3223 id text CONSTRAINT comment_test_child_fk REFERENCES comment_test);
3224 CREATE INDEX comment_test_child_fk ON comment_test_child(id);
3225 COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child';
3226 COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child';
3227 COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child';
3228 -- Change column type of parent
3229 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
3230 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer;
3231 ERROR: foreign key constraint "comment_test_child_fk" cannot be implemented
3232 DETAIL: Key columns "id" and "id" are of incompatible types: text and integer.
3233 -- Comments should be intact
3234 SELECT col_description('comment_test_child'::regclass, 1) as comment;
3236 -----------------------------------
3237 Column 'id' on comment_test_child
3240 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test_child'::regclass ORDER BY 1, 2;
3242 -----------------------+-----------------------------------------------------
3243 comment_test_child_fk | Index backing the FOREIGN KEY of comment_test_child
3246 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2;
3247 constraint | comment
3248 -----------------------+----------------------------------------------
3249 comment_test_child_fk | FOREIGN KEY constraint of comment_test_child
3252 -- Check that we map relation oids to filenodes and back correctly. Only
3253 -- display bad mappings so the test output doesn't change all the time. A
3254 -- filenode function call can return NULL for a relation dropped concurrently
3255 -- with the call's surrounding query, so ignore a NULL mapped_oid for
3256 -- relations that no longer exist after all calls finish.
3257 CREATE TEMP TABLE filenode_mapping AS
3259 oid, mapped_oid, reltablespace, relfilenode, relname
3261 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid
3262 WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid;
3263 SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid
3264 WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
3265 oid | mapped_oid | reltablespace | relfilenode | relname
3266 -----+------------+---------------+-------------+---------
3269 -- Checks on creating and manipulation of user defined relations in
3272 -- XXX: It would be useful to add checks around trying to manipulate
3273 -- catalog tables, but that might have ugly consequences when run
3274 -- against an existing server with allow_system_table_mods = on.
3275 SHOW allow_system_table_mods;
3276 allow_system_table_mods
3277 -------------------------
3281 -- disallowed because of search_path issues with pg_dump
3282 CREATE TABLE pg_catalog.new_system_table();
3283 ERROR: permission denied to create "pg_catalog.new_system_table"
3284 DETAIL: System catalog modifications are currently disallowed.
3285 -- instead create in public first, move to catalog
3286 CREATE TABLE new_system_table(id serial primary key, othercol text);
3287 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
3288 ALTER TABLE new_system_table SET SCHEMA public;
3289 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
3290 -- will be ignored -- already there:
3291 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
3292 ALTER TABLE new_system_table RENAME TO old_system_table;
3293 CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
3294 INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
3295 UPDATE old_system_table SET id = -id;
3296 DELETE FROM old_system_table WHERE othercol = 'somedata';
3297 TRUNCATE old_system_table;
3298 ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
3299 ALTER TABLE old_system_table DROP COLUMN othercol;
3300 DROP TABLE old_system_table;
3302 CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
3303 -- check relpersistence of an unlogged table
3304 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
3306 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
3308 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
3310 relname | relkind | relpersistence
3311 ------------------+---------+----------------
3315 unlogged1_f1_seq | S | p
3316 unlogged1_pkey | i | u
3319 CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
3320 CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
3321 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
3322 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
3323 ERROR: could not change table "unlogged2" to logged because it references unlogged table "unlogged1"
3324 ALTER TABLE unlogged1 SET LOGGED;
3325 -- check relpersistence of an unlogged table after changing to permanent
3326 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
3328 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
3330 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
3332 relname | relkind | relpersistence
3333 ------------------+---------+----------------
3337 unlogged1_f1_seq | S | p
3338 unlogged1_pkey | i | p
3341 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
3342 DROP TABLE unlogged3;
3343 DROP TABLE unlogged2;
3344 DROP TABLE unlogged1;
3346 CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
3347 -- check relpersistence of a permanent table
3348 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
3350 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
3352 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
3354 relname | relkind | relpersistence
3355 ----------------+---------+----------------
3357 logged1_f1_seq | S | p
3358 logged1_pkey | i | p
3363 CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
3364 CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
3365 ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
3366 ERROR: could not change table "logged1" to unlogged because it references logged table "logged2"
3367 ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
3368 ALTER TABLE logged2 SET UNLOGGED;
3369 ALTER TABLE logged1 SET UNLOGGED;
3370 -- check relpersistence of a permanent table after changing to unlogged
3371 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
3373 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
3375 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
3377 relname | relkind | relpersistence
3378 ----------------+---------+----------------
3380 logged1_f1_seq | S | p
3381 logged1_pkey | i | u
3386 ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
3390 -- test ADD COLUMN IF NOT EXISTS
3391 CREATE TABLE test_add_column(c1 integer);
3393 Table "public.test_add_column"
3394 Column | Type | Collation | Nullable | Default
3395 --------+---------+-----------+----------+---------
3398 ALTER TABLE test_add_column
3399 ADD COLUMN c2 integer;
3401 Table "public.test_add_column"
3402 Column | Type | Collation | Nullable | Default
3403 --------+---------+-----------+----------+---------
3407 ALTER TABLE test_add_column
3408 ADD COLUMN c2 integer; -- fail because c2 already exists
3409 ERROR: column "c2" of relation "test_add_column" already exists
3410 ALTER TABLE ONLY test_add_column
3411 ADD COLUMN c2 integer; -- fail because c2 already exists
3412 ERROR: column "c2" of relation "test_add_column" already exists
3414 Table "public.test_add_column"
3415 Column | Type | Collation | Nullable | Default
3416 --------+---------+-----------+----------+---------
3420 ALTER TABLE test_add_column
3421 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
3422 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3423 ALTER TABLE ONLY test_add_column
3424 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
3425 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3427 Table "public.test_add_column"
3428 Column | Type | Collation | Nullable | Default
3429 --------+---------+-----------+----------+---------
3433 ALTER TABLE test_add_column
3434 ADD COLUMN c2 integer, -- fail because c2 already exists
3435 ADD COLUMN c3 integer;
3436 ERROR: column "c2" of relation "test_add_column" already exists
3438 Table "public.test_add_column"
3439 Column | Type | Collation | Nullable | Default
3440 --------+---------+-----------+----------+---------
3444 ALTER TABLE test_add_column
3445 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
3446 ADD COLUMN c3 integer; -- fail because c3 already exists
3447 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3449 Table "public.test_add_column"
3450 Column | Type | Collation | Nullable | Default
3451 --------+---------+-----------+----------+---------
3456 ALTER TABLE test_add_column
3457 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
3458 ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists
3459 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3460 NOTICE: column "c3" of relation "test_add_column" already exists, skipping
3462 Table "public.test_add_column"
3463 Column | Type | Collation | Nullable | Default
3464 --------+---------+-----------+----------+---------
3469 ALTER TABLE test_add_column
3470 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
3471 ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists
3472 ADD COLUMN c4 integer;
3473 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3474 NOTICE: column "c3" of relation "test_add_column" already exists, skipping
3476 Table "public.test_add_column"
3477 Column | Type | Collation | Nullable | Default
3478 --------+---------+-----------+----------+---------
3484 DROP TABLE test_add_column;
3485 -- unsupported constraint types for partitioned tables
3486 CREATE TABLE partitioned (
3489 ) PARTITION BY RANGE (a, (a+b+1));
3490 ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
3491 ERROR: exclusion constraints are not supported on partitioned tables
3492 LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
3494 -- cannot drop column that is part of the partition key
3495 ALTER TABLE partitioned DROP COLUMN a;
3496 ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
3497 ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
3498 ERROR: cannot alter column "a" because it is part of the partition key of relation "partitioned"
3499 ALTER TABLE partitioned DROP COLUMN b;
3500 ERROR: cannot drop column "b" because it is part of the partition key of relation "partitioned"
3501 ALTER TABLE partitioned ALTER COLUMN b TYPE char(5);
3502 ERROR: cannot alter column "b" because it is part of the partition key of relation "partitioned"
3503 -- partitioned table cannot participate in regular inheritance
3504 CREATE TABLE nonpartitioned (
3508 ALTER TABLE partitioned INHERIT nonpartitioned;
3509 ERROR: cannot change inheritance of partitioned table
3510 ALTER TABLE nonpartitioned INHERIT partitioned;
3511 ERROR: cannot inherit from partitioned table "partitioned"
3512 -- cannot add NO INHERIT constraint to partitioned tables
3513 ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT;
3514 ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned"
3515 DROP TABLE partitioned, nonpartitioned;
3519 -- check that target table is partitioned
3520 CREATE TABLE unparted (
3523 CREATE TABLE fail_part (like unparted);
3524 ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a');
3525 ERROR: table "unparted" is not partitioned
3526 DROP TABLE unparted, fail_part;
3527 -- check that partition bound is compatible
3528 CREATE TABLE list_parted (
3530 b char(2) COLLATE "C",
3531 CONSTRAINT check_a CHECK (a > 0)
3532 ) PARTITION BY LIST (a);
3533 CREATE TABLE fail_part (LIKE list_parted);
3534 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10);
3535 ERROR: invalid bound specification for a list partition
3536 LINE 1: ...list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) T...
3538 DROP TABLE fail_part;
3539 -- check that the table being attached exists
3540 ALTER TABLE list_parted ATTACH PARTITION nonexistent FOR VALUES IN (1);
3541 ERROR: relation "nonexistent" does not exist
3542 -- check ownership of the source table
3543 CREATE ROLE regress_test_me;
3544 CREATE ROLE regress_test_not_me;
3545 CREATE TABLE not_owned_by_me (LIKE list_parted);
3546 ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me;
3547 SET SESSION AUTHORIZATION regress_test_me;
3548 CREATE TABLE owned_by_me (
3550 ) PARTITION BY LIST (a);
3551 ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1);
3552 ERROR: must be owner of table not_owned_by_me
3553 RESET SESSION AUTHORIZATION;
3554 DROP TABLE owned_by_me, not_owned_by_me;
3555 DROP ROLE regress_test_not_me;
3556 DROP ROLE regress_test_me;
3557 -- check that the table being attached is not part of regular inheritance
3558 CREATE TABLE parent (LIKE list_parted);
3559 CREATE TABLE child () INHERITS (parent);
3560 ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1);
3561 ERROR: cannot attach inheritance child as partition
3562 ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1);
3563 ERROR: cannot attach inheritance parent as partition
3564 DROP TABLE parent CASCADE;
3565 NOTICE: drop cascades to table child
3566 -- check any TEMP-ness
3567 CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a);
3568 CREATE TABLE perm_part (a int);
3569 ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1);
3570 ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted"
3571 DROP TABLE temp_parted, perm_part;
3572 -- check that the table being attached is not a typed table
3573 CREATE TYPE mytype AS (a int);
3574 CREATE TABLE fail_part OF mytype;
3575 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3576 ERROR: cannot attach a typed table as partition
3577 DROP TYPE mytype CASCADE;
3578 NOTICE: drop cascades to table fail_part
3579 -- check that the table being attached has only columns present in the parent
3580 CREATE TABLE fail_part (like list_parted, c int);
3581 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3582 ERROR: table "fail_part" contains column "c" not found in parent "list_parted"
3583 DETAIL: The new partition may contain only the columns present in parent.
3584 DROP TABLE fail_part;
3585 -- check that the table being attached has every column of the parent
3586 CREATE TABLE fail_part (a int NOT NULL);
3587 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3588 ERROR: child table is missing column "b"
3589 DROP TABLE fail_part;
3590 -- check that columns match in type, collation and NOT NULL status
3591 CREATE TABLE fail_part (
3595 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3596 ERROR: child table "fail_part" has different type for column "b"
3597 ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX";
3598 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3599 ERROR: child table "fail_part" has different collation for column "b"
3600 DROP TABLE fail_part;
3601 -- check that the table being attached has all constraints of the parent
3602 CREATE TABLE fail_part (
3603 b char(2) COLLATE "C",
3606 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3607 ERROR: child table is missing constraint "check_a"
3608 -- check that the constraint matches in definition with parent's constraint
3609 ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0);
3610 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3611 ERROR: child table "fail_part" has different definition for check constraint "check_a"
3612 DROP TABLE fail_part;
3613 -- check the attributes and constraints after partition is attached
3614 CREATE TABLE part_1 (
3616 b char(2) COLLATE "C",
3617 CONSTRAINT check_a CHECK (a > 0)
3619 ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1);
3620 -- attislocal and conislocal are always false for merged attributes and constraints respectively.
3621 SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0;
3622 attislocal | attinhcount
3623 ------------+-------------
3628 SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a';
3629 conislocal | coninhcount
3630 ------------+-------------
3634 -- check that the new partition won't overlap with an existing partition
3635 CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS);
3636 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3637 ERROR: partition "fail_part" would overlap partition "part_1"
3638 DROP TABLE fail_part;
3639 -- check that an existing table can be attached as a default partition
3640 CREATE TABLE def_part (LIKE list_parted INCLUDING CONSTRAINTS);
3641 ALTER TABLE list_parted ATTACH PARTITION def_part DEFAULT;
3642 -- check attaching default partition fails if a default partition already
3644 CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS);
3645 ALTER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT;
3646 ERROR: partition "fail_def_part" conflicts with existing default partition "def_part"
3647 -- check validation when attaching list partitions
3648 CREATE TABLE list_parted2 (
3651 ) PARTITION BY LIST (a);
3652 -- check that violating rows are correctly reported
3653 CREATE TABLE part_2 (LIKE list_parted2);
3654 INSERT INTO part_2 VALUES (3, 'a');
3655 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
3656 ERROR: partition constraint is violated by some row
3657 -- should be ok after deleting the bad row
3659 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
3660 -- check partition cannot be attached if default has some row for its values
3661 CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
3662 INSERT INTO list_parted2_def VALUES (11, 'z');
3663 CREATE TABLE part_3 (LIKE list_parted2);
3664 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
3665 ERROR: updated partition constraint for default partition would be violated by some row
3666 -- should be ok after deleting the bad row
3667 DELETE FROM list_parted2_def WHERE a = 11;
3668 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
3669 -- adding constraints that describe the desired partition constraint
3670 -- (or more restrictive) will help skip the validation scan
3671 CREATE TABLE part_3_4 (
3673 CONSTRAINT check_a CHECK (a IN (3))
3675 -- however, if a list partition does not accept nulls, there should be
3676 -- an explicit NOT NULL constraint on the partition key column for the
3677 -- validation scan to be skipped;
3678 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
3679 -- adding a NOT NULL constraint will cause the scan to be skipped
3680 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
3681 ALTER TABLE part_3_4 ALTER a SET NOT NULL;
3682 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
3683 -- check if default partition scan skipped
3684 ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6));
3685 CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);
3686 -- check validation when attaching range partitions
3687 CREATE TABLE range_parted (
3690 ) PARTITION BY RANGE (a, b);
3691 -- check that violating rows are correctly reported
3692 CREATE TABLE part1 (
3693 a int NOT NULL CHECK (a = 1),
3694 b int NOT NULL CHECK (b >= 1 AND b <= 10)
3696 INSERT INTO part1 VALUES (1, 10);
3697 -- Remember the TO bound is exclusive
3698 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
3699 ERROR: partition constraint is violated by some row
3700 -- should be ok after deleting the bad row
3702 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
3703 -- adding constraints that describe the desired partition constraint
3704 -- (or more restrictive) will help skip the validation scan
3705 CREATE TABLE part2 (
3706 a int NOT NULL CHECK (a = 1),
3707 b int NOT NULL CHECK (b >= 10 AND b < 18)
3709 ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20);
3710 -- Create default partition
3711 CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT;
3712 -- Only one default partition is allowed, hence, following should give error
3713 CREATE TABLE partr_def2 (LIKE part1 INCLUDING CONSTRAINTS);
3714 ALTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT;
3715 ERROR: partition "partr_def2" conflicts with existing default partition "partr_def1"
3716 -- Overlapping partitions cannot be attached, hence, following should give error
3717 INSERT INTO partr_def1 VALUES (2, 10);
3718 CREATE TABLE part3 (LIKE range_parted);
3719 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (2, 10) TO (2, 20);
3720 ERROR: updated partition constraint for default partition would be violated by some row
3721 -- Attaching partitions should be successful when there are no overlapping rows
3722 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (3, 10) TO (3, 20);
3723 -- check that leaf partitions are scanned when attaching a partitioned
3725 CREATE TABLE part_5 (
3727 ) PARTITION BY LIST (b);
3728 -- check that violating rows are correctly reported
3729 CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a');
3730 INSERT INTO part_5_a (a, b) VALUES (6, 'a');
3731 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
3732 ERROR: partition constraint is violated by some row
3733 -- delete the faulting row and also add a constraint to skip the scan
3734 DELETE FROM part_5_a WHERE a NOT IN (3);
3735 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);
3736 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
3737 ALTER TABLE list_parted2 DETACH PARTITION part_5;
3738 ALTER TABLE part_5 DROP CONSTRAINT check_a;
3739 -- scan should again be skipped, even though NOT NULL is now a column property
3740 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL;
3741 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
3742 -- Check the case where attnos of the partitioning columns in the table being
3743 -- attached differs from the parent. It should not affect the constraint-
3744 -- checking logic that allows to skip the scan.
3745 CREATE TABLE part_6 (
3748 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6)
3750 ALTER TABLE part_6 DROP c;
3751 ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);
3752 -- Similar to above, but the table being attached is a partitioned table
3753 -- whose partition has still different attnos for the root partitioning
3755 CREATE TABLE part_7 (
3757 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
3758 ) PARTITION BY LIST (b);
3759 CREATE TABLE part_7_a_null (
3763 LIKE list_parted2, -- 'a' will have attnum = 4
3764 CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'),
3765 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
3767 ALTER TABLE part_7_a_null DROP c, DROP d, DROP e;
3768 ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null);
3769 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
3770 -- Same example, but check this time that the constraint correctly detects
3772 ALTER TABLE list_parted2 DETACH PARTITION part_7;
3773 ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped
3774 INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a');
3775 SELECT tableoid::regclass, a, b FROM part_7 order by a;
3777 ---------------+---+---
3779 part_7_a_null | 9 | a
3782 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
3783 ERROR: partition constraint is violated by some row
3784 -- check that leaf partitions of default partition are scanned when
3785 -- attaching a partitioned table.
3786 ALTER TABLE part_5 DROP CONSTRAINT check_a;
3787 CREATE TABLE part5_def PARTITION OF part_5 DEFAULT PARTITION BY LIST(a);
3788 CREATE TABLE part5_def_p1 PARTITION OF part5_def FOR VALUES IN (5);
3789 INSERT INTO part5_def_p1 VALUES (5, 'y');
3790 CREATE TABLE part5_p1 (LIKE part_5);
3791 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
3792 ERROR: updated partition constraint for default partition would be violated by some row
3793 -- should be ok after deleting the bad row
3794 DELETE FROM part5_def_p1 WHERE b = 'y';
3795 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
3796 -- check that the table being attached is not already a partition
3797 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
3798 ERROR: "part_2" is already a partition
3799 -- check that circular inheritance is not allowed
3800 ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b');
3801 ERROR: circular inheritance not allowed
3802 DETAIL: "part_5" is already a child of "list_parted2".
3803 ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0);
3804 ERROR: circular inheritance not allowed
3805 DETAIL: "list_parted2" is already a child of "list_parted2".
3806 -- If a partitioned table being created or an existing table being attached
3807 -- as a partition does not have a constraint that would allow validation scan
3808 -- to be skipped, but an individual partition does, then the partition's
3809 -- validation scan is skipped.
3810 CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a);
3811 CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b);
3812 CREATE TABLE quuux_default1 PARTITION OF quuux_default (
3813 CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1)
3814 ) FOR VALUES IN ('b');
3815 CREATE TABLE quuux1 (a int, b text);
3816 ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate!
3817 CREATE TABLE quuux2 (a int, b text);
3818 ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation
3819 DROP TABLE quuux1, quuux2;
3820 -- should validate for quuux1, but not for quuux2
3821 CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);
3822 CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);
3824 -- check validation when attaching hash partitions
3825 -- Use hand-rolled hash functions and operator class to get predictable result
3826 -- on different matchines. part_test_int4_ops is defined in insert.sql.
3827 -- check that the new partition won't overlap with an existing partition
3828 CREATE TABLE hash_parted (
3831 ) PARTITION BY HASH (a part_test_int4_ops);
3832 CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0);
3833 CREATE TABLE fail_part (LIKE hpart_1);
3834 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4);
3835 ERROR: partition "fail_part" would overlap partition "hpart_1"
3836 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 0);
3837 ERROR: partition "fail_part" would overlap partition "hpart_1"
3838 DROP TABLE fail_part;
3839 -- check validation when attaching hash partitions
3840 -- check that violating rows are correctly reported
3841 CREATE TABLE hpart_2 (LIKE hash_parted);
3842 INSERT INTO hpart_2 VALUES (3, 0);
3843 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
3844 ERROR: partition constraint is violated by some row
3845 -- should be ok after deleting the bad row
3846 DELETE FROM hpart_2;
3847 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
3848 -- check that leaf partitions are scanned when attaching a partitioned
3850 CREATE TABLE hpart_5 (
3852 ) PARTITION BY LIST (b);
3853 -- check that violating rows are correctly reported
3854 CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3');
3855 INSERT INTO hpart_5_a (a, b) VALUES (7, 1);
3856 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
3857 ERROR: partition constraint is violated by some row
3858 -- should be ok after deleting the bad row
3859 DELETE FROM hpart_5_a;
3860 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
3861 -- check that the table being attach is with valid modulus and remainder value
3862 CREATE TABLE fail_part(LIKE hash_parted);
3863 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 0, REMAINDER 1);
3864 ERROR: modulus for hash partition must be a positive integer
3865 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 8);
3866 ERROR: remainder for hash partition must be less than modulus
3867 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2);
3868 ERROR: every hash partition modulus must be a factor of the next larger modulus
3869 DROP TABLE fail_part;
3873 -- check that the table is partitioned at all
3874 CREATE TABLE regular_table (a int);
3875 ALTER TABLE regular_table DETACH PARTITION any_name;
3876 ERROR: table "regular_table" is not partitioned
3877 DROP TABLE regular_table;
3878 -- check that the partition being detached exists at all
3879 ALTER TABLE list_parted2 DETACH PARTITION part_4;
3880 ERROR: relation "part_4" does not exist
3881 ALTER TABLE hash_parted DETACH PARTITION hpart_4;
3882 ERROR: relation "hpart_4" does not exist
3883 -- check that the partition being detached is actually a partition of the parent
3884 CREATE TABLE not_a_part (a int);
3885 ALTER TABLE list_parted2 DETACH PARTITION not_a_part;
3886 ERROR: relation "not_a_part" is not a partition of relation "list_parted2"
3887 ALTER TABLE list_parted2 DETACH PARTITION part_1;
3888 ERROR: relation "part_1" is not a partition of relation "list_parted2"
3889 ALTER TABLE hash_parted DETACH PARTITION not_a_part;
3890 ERROR: relation "not_a_part" is not a partition of relation "hash_parted"
3891 DROP TABLE not_a_part;
3892 -- check that, after being detached, attinhcount/coninhcount is dropped to 0 and
3893 -- attislocal/conislocal is set to true
3894 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
3895 SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0;
3896 attinhcount | attislocal
3897 -------------+------------
3902 SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a';
3903 coninhcount | conislocal
3904 -------------+------------
3908 DROP TABLE part_3_4;
3909 -- check that a detached partition is not dropped on dropping a partitioned table
3910 CREATE TABLE range_parted2 (
3912 ) PARTITION BY RANGE(a);
3913 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
3914 ALTER TABLE range_parted2 DETACH PARTITION part_rp;
3915 DROP TABLE range_parted2;
3916 SELECT * from part_rp;
3922 -- Check ALTER TABLE commands for partitioned tables and partitions
3923 -- cannot add/drop column to/from *only* the parent
3924 ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
3925 ERROR: column must be added to child tables too
3926 ALTER TABLE ONLY list_parted2 DROP COLUMN b;
3927 ERROR: cannot drop column from only the partitioned table when partitions exist
3928 HINT: Do not specify the ONLY keyword.
3929 -- cannot add a column to partition or drop an inherited one
3930 ALTER TABLE part_2 ADD COLUMN c text;
3931 ERROR: cannot add column to a partition
3932 ALTER TABLE part_2 DROP COLUMN b;
3933 ERROR: cannot drop inherited column "b"
3934 -- Nor rename, alter type
3935 ALTER TABLE part_2 RENAME COLUMN b to c;
3936 ERROR: cannot rename inherited column "b"
3937 ALTER TABLE part_2 ALTER COLUMN b TYPE text;
3938 ERROR: cannot alter inherited column "b"
3939 -- cannot add/drop NOT NULL or check constraints to *only* the parent, when
3941 ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL;
3942 ERROR: constraint must be added to child tables too
3943 DETAIL: Column "b" of relation "part_2" is not already NOT NULL.
3944 HINT: Do not specify the ONLY keyword.
3945 ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
3946 ERROR: constraint must be added to child tables too
3947 ALTER TABLE list_parted2 ALTER b SET NOT NULL;
3948 ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL;
3949 ERROR: cannot remove constraint from only the partitioned table when partitions exist
3950 HINT: Do not specify the ONLY keyword.
3951 ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
3952 ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b;
3953 ERROR: cannot remove constraint from only the partitioned table when partitions exist
3954 HINT: Do not specify the ONLY keyword.
3955 -- It's alright though, if no partitions are yet created
3956 CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a);
3957 ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL;
3958 ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0);
3959 ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL;
3960 ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a;
3961 DROP TABLE parted_no_parts;
3962 -- cannot drop inherited NOT NULL or check constraints from partition
3963 ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0);
3964 ALTER TABLE part_2 ALTER b DROP NOT NULL;
3965 ERROR: column "b" is marked NOT NULL in parent table
3966 ALTER TABLE part_2 DROP CONSTRAINT check_a2;
3967 ERROR: cannot drop inherited constraint "check_a2" of relation "part_2"
3968 -- Doesn't make sense to add NO INHERIT constraints on partitioned tables
3969 ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT;
3970 ERROR: cannot add NO INHERIT constraint to partitioned table "list_parted2"
3971 -- check that a partition cannot participate in regular inheritance
3972 CREATE TABLE inh_test () INHERITS (part_2);
3973 ERROR: cannot inherit from partition "part_2"
3974 CREATE TABLE inh_test (LIKE part_2);
3975 ALTER TABLE inh_test INHERIT part_2;
3976 ERROR: cannot inherit from a partition
3977 ALTER TABLE part_2 INHERIT inh_test;
3978 ERROR: cannot change inheritance of a partition
3979 -- cannot drop or alter type of partition key columns of lower level
3980 -- partitioned tables; for example, part_5, which is list_parted2's
3981 -- partition, is partitioned on b;
3982 ALTER TABLE list_parted2 DROP COLUMN b;
3983 ERROR: cannot drop column "b" because it is part of the partition key of relation "part_5"
3984 ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
3985 ERROR: cannot alter column "b" because it is part of the partition key of relation "part_5"
3986 -- dropping non-partition key columns should be allowed on the parent table.
3987 ALTER TABLE list_parted DROP COLUMN b;
3988 SELECT * FROM list_parted;
3994 DROP TABLE list_parted, list_parted2, range_parted;
3995 DROP TABLE fail_def_part;
3996 DROP TABLE hash_parted;
3997 -- more tests for certain multi-level partitioning scenarios
3998 create table p (a int, b int) partition by range (a, b);
3999 create table p1 (b int, a int not null) partition by range (b);
4000 create table p11 (like p1);
4001 alter table p11 drop a;
4002 alter table p11 add a int;
4003 alter table p11 drop a;
4004 alter table p11 add a int not null;
4005 -- attnum for key attribute 'a' is different in p, p1, and p11
4006 select attrelid::regclass, attname, attnum
4009 and (attrelid = 'p'::regclass
4010 or attrelid = 'p1'::regclass
4011 or attrelid = 'p11'::regclass)
4012 order by attrelid::regclass::text;
4013 attrelid | attname | attnum
4014 ----------+---------+--------
4020 alter table p1 attach partition p11 for values from (2) to (5);
4021 insert into p1 (a, b) values (2, 3);
4022 -- check that partition validation scan correctly detects violating rows
4023 alter table p attach partition p1 for values from (1, 2) to (1, 10);
4024 ERROR: partition constraint is violated by some row
4028 -- validate constraint on partitioned tables should only scan leaf partitions
4029 create table parted_validate_test (a int) partition by list (a);
4030 create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1);
4031 alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
4032 alter table parted_validate_test validate constraint parted_validate_test_chka;
4033 drop table parted_validate_test;
4034 -- test alter column options
4035 CREATE TABLE attmp(i integer);
4036 INSERT INTO attmp VALUES (1);
4037 ALTER TABLE attmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
4038 ALTER TABLE attmp ALTER COLUMN i RESET (n_distinct_inherited);
4041 DROP USER regress_alter_table_user1;
4042 -- check that violating rows are correctly reported when attaching as the
4043 -- default partition
4044 create table defpart_attach_test (a int) partition by list (a);
4045 create table defpart_attach_test1 partition of defpart_attach_test for values in (1);
4046 create table defpart_attach_test_d (b int, a int);
4047 alter table defpart_attach_test_d drop b;
4048 insert into defpart_attach_test_d values (1), (2);
4049 -- error because its constraint as the default partition would be violated
4050 -- by the row containing 1
4051 alter table defpart_attach_test attach partition defpart_attach_test_d default;
4052 ERROR: partition constraint is violated by some row
4053 delete from defpart_attach_test_d where a = 1;
4054 alter table defpart_attach_test_d add check (a > 1);
4055 -- should be attached successfully and without needing to be scanned
4056 alter table defpart_attach_test attach partition defpart_attach_test_d default;
4057 -- check that attaching a partition correctly reports any rows in the default
4058 -- partition that should not be there for the new partition to be attached
4060 create table defpart_attach_test_2 (like defpart_attach_test_d);
4061 alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2);
4062 ERROR: updated partition constraint for default partition would be violated by some row
4063 drop table defpart_attach_test;
4064 -- check combinations of temporary and permanent relations when attaching
4066 create table perm_part_parent (a int) partition by list (a);
4067 create temp table temp_part_parent (a int) partition by list (a);
4068 create table perm_part_child (a int);
4069 create temp table temp_part_child (a int);
4070 alter table temp_part_parent attach partition perm_part_child default; -- error
4071 ERROR: cannot attach a permanent relation as partition of temporary relation "temp_part_parent"
4072 alter table perm_part_parent attach partition temp_part_child default; -- error
4073 ERROR: cannot attach a temporary relation as partition of permanent relation "perm_part_parent"
4074 alter table temp_part_parent attach partition temp_part_child default; -- ok
4075 drop table perm_part_parent cascade;
4076 drop table temp_part_parent cascade;
4077 -- check that attaching partitions to a table while it is being used is
4079 create table tab_part_attach (a int) partition by list (a);
4080 create or replace function func_part_attach() returns trigger
4081 language plpgsql as $$
4083 execute 'create table tab_part_attach_1 (a int)';
4084 execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)';
4087 create trigger trig_part_attach before insert on tab_part_attach
4088 for each statement execute procedure func_part_attach();
4089 insert into tab_part_attach values (1);
4090 ERROR: cannot ALTER TABLE "tab_part_attach" because it is being used by active queries in this session
4091 CONTEXT: SQL statement "alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)"
4092 PL/pgSQL function func_part_attach() line 4 at EXECUTE
4093 drop table tab_part_attach;
4094 drop function func_part_attach();
4095 -- test case where the partitioning operator is a SQL function whose
4096 -- evaluation results in the table's relcache being rebuilt partway through
4097 -- the execution of an ATTACH PARTITION command
4098 create function at_test_sql_partop (int4, int4) returns int language sql
4099 as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$;
4100 create operator class at_test_sql_partop for type int4 using btree as
4101 operator 1 < (int4, int4), operator 2 <= (int4, int4),
4102 operator 3 = (int4, int4), operator 4 >= (int4, int4),
4103 operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4);
4104 create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop);
4105 create table at_test_sql_partop_1 (a int);
4106 alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10);
4107 drop table at_test_sql_partop;
4108 drop operator class at_test_sql_partop using btree;
4109 drop function at_test_sql_partop;