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
235 -- rename statements with mismatching statement and object types
236 CREATE TABLE alter_idx_rename_test (a INT);
237 CREATE INDEX alter_idx_rename_test_idx ON alter_idx_rename_test (a);
238 CREATE TABLE alter_idx_rename_test_parted (a INT) PARTITION BY LIST (a);
239 CREATE INDEX alter_idx_rename_test_parted_idx ON alter_idx_rename_test_parted (a);
241 ALTER INDEX alter_idx_rename_test RENAME TO alter_idx_rename_test_2;
242 ALTER INDEX alter_idx_rename_test_parted RENAME TO alter_idx_rename_test_parted_2;
243 SELECT relation::regclass, mode FROM pg_locks
244 WHERE pid = pg_backend_pid() AND locktype = 'relation'
245 AND relation::regclass::text LIKE 'alter\_idx%'
246 ORDER BY relation::regclass::text COLLATE "C";
248 --------------------------------+---------------------
249 alter_idx_rename_test_2 | AccessExclusiveLock
250 alter_idx_rename_test_parted_2 | AccessExclusiveLock
255 ALTER INDEX alter_idx_rename_test_idx RENAME TO alter_idx_rename_test_idx_2;
256 ALTER INDEX alter_idx_rename_test_parted_idx RENAME TO alter_idx_rename_test_parted_idx_2;
257 SELECT relation::regclass, mode FROM pg_locks
258 WHERE pid = pg_backend_pid() AND locktype = 'relation'
259 AND relation::regclass::text LIKE 'alter\_idx%'
260 ORDER BY relation::regclass::text COLLATE "C";
262 ------------------------------------+--------------------------
263 alter_idx_rename_test_idx_2 | ShareUpdateExclusiveLock
264 alter_idx_rename_test_parted_idx_2 | ShareUpdateExclusiveLock
269 ALTER TABLE alter_idx_rename_test_idx_2 RENAME TO alter_idx_rename_test_idx_3;
270 ALTER TABLE alter_idx_rename_test_parted_idx_2 RENAME TO alter_idx_rename_test_parted_idx_3;
271 SELECT relation::regclass, mode FROM pg_locks
272 WHERE pid = pg_backend_pid() AND locktype = 'relation'
273 AND relation::regclass::text LIKE 'alter\_idx%'
274 ORDER BY relation::regclass::text COLLATE "C";
276 ------------------------------------+---------------------
277 alter_idx_rename_test_idx_3 | AccessExclusiveLock
278 alter_idx_rename_test_parted_idx_3 | AccessExclusiveLock
282 DROP TABLE alter_idx_rename_test_2;
284 CREATE VIEW attmp_view (unique1) AS SELECT unique1 FROM tenk1;
285 ALTER TABLE attmp_view RENAME TO attmp_view_new;
286 SET ROLE regress_alter_table_user1;
287 ALTER VIEW attmp_view_new RENAME TO fail; -- permission denied
288 ERROR: must be owner of view attmp_view_new
290 -- hack to ensure we get an indexscan here
291 set enable_seqscan to off;
292 set enable_bitmapscan to off;
294 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
304 reset enable_seqscan;
305 reset enable_bitmapscan;
306 DROP VIEW attmp_view_new;
307 -- toast-like relation name
308 alter table stud_emp rename to pg_toast_stud_emp;
309 alter table pg_toast_stud_emp rename to stud_emp;
310 -- renaming index should rename constraint as well
311 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
312 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
313 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
314 -- renaming constraint
315 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
316 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
317 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
318 -- renaming constraint should rename index as well
319 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
320 DROP INDEX onek_unique1_constraint; -- to see whether it's there
321 ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it
322 HINT: You can drop constraint onek_unique1_constraint on table onek instead.
323 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
324 DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
325 ERROR: cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it
326 HINT: You can drop constraint onek_unique1_constraint_foo on table onek instead.
327 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
328 -- renaming constraints vs. inheritance
329 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
330 \d constraint_rename_test
331 Table "public.constraint_rename_test"
332 Column | Type | Collation | Nullable | Default
333 --------+---------+-----------+----------+---------
340 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
341 NOTICE: merging column "a" with inherited definition
342 NOTICE: merging constraint "con1" with inherited definition
343 \d constraint_rename_test2
344 Table "public.constraint_rename_test2"
345 Column | Type | Collation | Nullable | Default
346 --------+---------+-----------+----------+---------
353 Inherits: constraint_rename_test
355 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
356 ERROR: cannot rename inherited constraint "con1"
357 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
358 ERROR: inherited constraint "con1" must be renamed in child tables too
359 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
360 \d constraint_rename_test
361 Table "public.constraint_rename_test"
362 Column | Type | Collation | Nullable | Default
363 --------+---------+-----------+----------+---------
368 "con1foo" CHECK (a > 0)
369 Number of child tables: 1 (Use \d+ to list them.)
371 \d constraint_rename_test2
372 Table "public.constraint_rename_test2"
373 Column | Type | Collation | Nullable | Default
374 --------+---------+-----------+----------+---------
380 "con1foo" CHECK (a > 0)
381 Inherits: constraint_rename_test
383 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
384 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
385 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
386 \d constraint_rename_test
387 Table "public.constraint_rename_test"
388 Column | Type | Collation | Nullable | Default
389 --------+---------+-----------+----------+---------
394 "con1foo" CHECK (a > 0)
395 "con2bar" CHECK (b > 0) NO INHERIT
396 Number of child tables: 1 (Use \d+ to list them.)
398 \d constraint_rename_test2
399 Table "public.constraint_rename_test2"
400 Column | Type | Collation | Nullable | Default
401 --------+---------+-----------+----------+---------
407 "con1foo" CHECK (a > 0)
408 Inherits: constraint_rename_test
410 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
411 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
412 \d constraint_rename_test
413 Table "public.constraint_rename_test"
414 Column | Type | Collation | Nullable | Default
415 --------+---------+-----------+----------+---------
416 a | integer | | not null |
420 "con3foo" PRIMARY KEY, btree (a)
422 "con1foo" CHECK (a > 0)
423 "con2bar" CHECK (b > 0) NO INHERIT
424 Number of child tables: 1 (Use \d+ to list them.)
426 \d constraint_rename_test2
427 Table "public.constraint_rename_test2"
428 Column | Type | Collation | Nullable | Default
429 --------+---------+-----------+----------+---------
430 a | integer | | not null |
435 "con1foo" CHECK (a > 0)
436 Inherits: constraint_rename_test
438 DROP TABLE constraint_rename_test2;
439 DROP TABLE constraint_rename_test;
440 ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok
441 NOTICE: relation "constraint_not_exist" does not exist, skipping
442 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
443 NOTICE: relation "constraint_rename_test" does not exist, skipping
444 -- renaming constraints with cache reset of target relation
445 CREATE TABLE constraint_rename_cache (a int,
446 CONSTRAINT chk_a CHECK (a > 0),
448 ALTER TABLE constraint_rename_cache
449 RENAME CONSTRAINT chk_a TO chk_a_new;
450 ALTER TABLE constraint_rename_cache
451 RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new;
452 CREATE TABLE like_constraint_rename_cache
453 (LIKE constraint_rename_cache INCLUDING ALL);
454 \d like_constraint_rename_cache
455 Table "public.like_constraint_rename_cache"
456 Column | Type | Collation | Nullable | Default
457 --------+---------+-----------+----------+---------
458 a | integer | | not null |
460 "like_constraint_rename_cache_pkey" PRIMARY KEY, btree (a)
462 "chk_a_new" CHECK (a > 0)
464 DROP TABLE constraint_rename_cache;
465 DROP TABLE like_constraint_rename_cache;
466 -- FOREIGN KEY CONSTRAINT adding TEST
467 CREATE TABLE attmp2 (a int primary key);
468 CREATE TABLE attmp3 (a int, b int);
469 CREATE TABLE attmp4 (a int, b int, unique(a,b));
470 CREATE TABLE attmp5 (a int, b int);
471 -- Insert rows into attmp2 (pktable)
472 INSERT INTO attmp2 values (1);
473 INSERT INTO attmp2 values (2);
474 INSERT INTO attmp2 values (3);
475 INSERT INTO attmp2 values (4);
476 -- Insert rows into attmp3
477 INSERT INTO attmp3 values (1,10);
478 INSERT INTO attmp3 values (1,20);
479 INSERT INTO attmp3 values (5,50);
480 -- Try (and fail) to add constraint due to invalid source columns
481 ALTER TABLE attmp3 add constraint attmpconstr foreign key(c) references attmp2 match full;
482 ERROR: column "c" referenced in foreign key constraint does not exist
483 -- Try (and fail) to add constraint due to invalid destination columns explicitly given
484 ALTER TABLE attmp3 add constraint attmpconstr foreign key(a) references attmp2(b) match full;
485 ERROR: column "b" referenced in foreign key constraint does not exist
486 -- Try (and fail) to add constraint due to invalid data
487 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
488 ERROR: insert or update on table "attmp3" violates foreign key constraint "attmpconstr"
489 DETAIL: Key (a)=(5) is not present in table "attmp2".
490 -- Delete failing row
491 DELETE FROM attmp3 where a=5;
493 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
494 ALTER TABLE attmp3 drop constraint attmpconstr;
495 INSERT INTO attmp3 values (5,50);
496 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
497 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full NOT VALID;
498 ALTER TABLE attmp3 validate constraint attmpconstr;
499 ERROR: insert or update on table "attmp3" violates foreign key constraint "attmpconstr"
500 DETAIL: Key (a)=(5) is not present in table "attmp2".
501 -- Delete failing row
502 DELETE FROM attmp3 where a=5;
503 -- Try (and succeed) and repeat to show it works on already valid constraint
504 ALTER TABLE attmp3 validate constraint attmpconstr;
505 ALTER TABLE attmp3 validate constraint attmpconstr;
506 -- Try a non-verified CHECK constraint
507 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
508 ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row
509 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
510 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
511 ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row
512 DELETE FROM attmp3 WHERE NOT b > 10;
513 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
514 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
515 -- Test inherited NOT VALID CHECK constraints
516 select * from attmp3;
522 CREATE TABLE attmp6 () INHERITS (attmp3);
523 CREATE TABLE attmp7 () INHERITS (attmp3);
524 INSERT INTO attmp6 VALUES (6, 30), (7, 16);
525 ALTER TABLE attmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
526 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- fails
527 ERROR: check constraint "b_le_20" of relation "attmp6" is violated by some row
528 DELETE FROM attmp6 WHERE b > 20;
529 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
530 -- An already validated constraint must not be revalidated
531 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
532 INSERT INTO attmp7 VALUES (8, 18);
533 ALTER TABLE attmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
535 ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
536 NOTICE: merging constraint "identity" with inherited definition
537 ALTER TABLE attmp3 VALIDATE CONSTRAINT identity;
540 -- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT
541 create table parent_noinh_convalid (a int);
542 create table child_noinh_convalid () inherits (parent_noinh_convalid);
543 insert into parent_noinh_convalid values (1);
544 insert into child_noinh_convalid values (1);
545 alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid;
546 -- fail, because of the row in parent
547 alter table parent_noinh_convalid validate constraint check_a_is_2;
548 ERROR: check constraint "check_a_is_2" of relation "parent_noinh_convalid" is violated by some row
549 delete from only parent_noinh_convalid;
550 -- ok (parent itself contains no violating rows)
551 alter table parent_noinh_convalid validate constraint check_a_is_2;
552 select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2';
559 drop table parent_noinh_convalid, child_noinh_convalid;
560 -- Try (and fail) to create constraint from attmp5(a) to attmp4(a) - unique constraint on
562 ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full;
563 ERROR: there is no unique constraint matching given keys for referenced table "attmp4"
570 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
571 -- exclusion until validated
572 set constraint_exclusion TO 'partition';
573 create table nv_parent (d date, check (false) no inherit not valid);
574 -- not valid constraint added at creation time should automatically become valid
576 Table "public.nv_parent"
577 Column | Type | Collation | Nullable | Default
578 --------+------+-----------+----------+---------
581 "nv_parent_check" CHECK (false) NO INHERIT
583 create table nv_child_2010 () inherits (nv_parent);
584 create table nv_child_2011 () inherits (nv_parent);
585 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
586 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
587 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
589 ---------------------------------------------------------------------------
591 -> Seq Scan on nv_parent nv_parent_1
592 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
593 -> Seq Scan on nv_child_2010 nv_parent_2
594 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
595 -> Seq Scan on nv_child_2011 nv_parent_3
596 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
599 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
600 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
602 ---------------------------------------------------------------------------
604 -> Seq Scan on nv_parent nv_parent_1
605 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
606 -> Seq Scan on nv_child_2010 nv_parent_2
607 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
608 -> Seq Scan on nv_child_2011 nv_parent_3
609 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
612 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
614 ---------------------------------------------------------------------------
616 -> Seq Scan on nv_parent nv_parent_1
617 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
618 -> Seq Scan on nv_child_2010 nv_parent_2
619 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
620 -> Seq Scan on nv_child_2011 nv_parent_3
621 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
622 -> Seq Scan on nv_child_2009 nv_parent_4
623 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
626 -- after validation, the constraint should be used
627 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
628 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
630 ---------------------------------------------------------------------------
632 -> Seq Scan on nv_parent nv_parent_1
633 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
634 -> Seq Scan on nv_child_2010 nv_parent_2
635 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
636 -> Seq Scan on nv_child_2009 nv_parent_3
637 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
640 -- add an inherited NOT VALID constraint
641 alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid;
643 Table "public.nv_child_2009"
644 Column | Type | Collation | Nullable | Default
645 --------+------+-----------+----------+---------
648 "nv_child_2009_d_check" CHECK (d >= '01-01-2009'::date AND d <= '12-31-2009'::date)
649 "nv_parent_d_check" CHECK (d >= '01-01-2001'::date AND d <= '12-31-2099'::date) NOT VALID
652 -- we leave nv_parent and children around to help test pg_dump logic
653 -- Foreign key adding test with mixed types
654 -- Note: these tables are TEMP to avoid name conflicts when this test
655 -- is run in parallel with foreign_key.sql.
656 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
657 INSERT INTO PKTABLE VALUES(42);
658 CREATE TEMP TABLE FKTABLE (ftest1 inet);
659 -- This next should fail, because int=inet does not exist
660 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
661 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
662 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
663 -- This should also fail for the same reason, but here we
664 -- give the column name
665 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
666 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
667 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
669 -- This should succeed, even though they are different types,
670 -- because int=int8 exists and is a member of the integer opfamily
671 CREATE TEMP TABLE FKTABLE (ftest1 int8);
672 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
673 -- Check it actually works
674 INSERT INTO FKTABLE VALUES(42); -- should succeed
675 INSERT INTO FKTABLE VALUES(43); -- should fail
676 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
677 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
679 -- This should fail, because we'd have to cast numeric to int which is
680 -- not an implicit coercion (or use numeric=numeric, but that's not part
681 -- of the integer opfamily)
682 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
683 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
684 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
685 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
688 -- On the other hand, this should work because int implicitly promotes to
689 -- numeric, and we allow promotion on the FK side
690 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
691 INSERT INTO PKTABLE VALUES(42);
692 CREATE TEMP TABLE FKTABLE (ftest1 int);
693 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
694 -- Check it actually works
695 INSERT INTO FKTABLE VALUES(42); -- should succeed
696 INSERT INTO FKTABLE VALUES(43); -- should fail
697 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
698 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
701 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
702 PRIMARY KEY(ptest1, ptest2));
703 -- This should fail, because we just chose really odd types
704 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
705 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
706 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
707 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
709 -- Again, so should this...
710 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
711 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
712 references pktable(ptest1, ptest2);
713 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
714 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
716 -- This fails because we mixed up the column ordering
717 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
718 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
719 references pktable(ptest2, ptest1);
720 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
721 DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
723 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
724 references pktable(ptest1, ptest2);
725 ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented
726 DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
729 -- Test that ALTER CONSTRAINT updates trigger deferrability properly
730 CREATE TEMP TABLE PKTABLE (ptest1 int primary key);
731 CREATE TEMP TABLE FKTABLE (ftest1 int);
732 ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable
733 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
734 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable
735 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
736 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable
737 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE;
738 ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable
739 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
740 ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE;
741 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable
742 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
743 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED;
744 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable
745 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
746 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE;
747 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
748 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
749 WHERE tgrelid = 'pktable'::regclass
751 conname | tgfoid | tgtype | tgdeferrable | tginitdeferred
752 ---------+------------------------+--------+--------------+----------------
753 fkdd | "RI_FKey_cascade_del" | 9 | f | f
754 fkdd | "RI_FKey_noaction_upd" | 17 | t | t
755 fkdd2 | "RI_FKey_cascade_del" | 9 | f | f
756 fkdd2 | "RI_FKey_noaction_upd" | 17 | t | t
757 fkdi | "RI_FKey_cascade_del" | 9 | f | f
758 fkdi | "RI_FKey_noaction_upd" | 17 | t | f
759 fkdi2 | "RI_FKey_cascade_del" | 9 | f | f
760 fkdi2 | "RI_FKey_noaction_upd" | 17 | t | f
761 fknd | "RI_FKey_cascade_del" | 9 | f | f
762 fknd | "RI_FKey_noaction_upd" | 17 | f | f
763 fknd2 | "RI_FKey_cascade_del" | 9 | f | f
764 fknd2 | "RI_FKey_noaction_upd" | 17 | f | f
767 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
768 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
769 WHERE tgrelid = 'fktable'::regclass
771 conname | tgfoid | tgtype | tgdeferrable | tginitdeferred
772 ---------+---------------------+--------+--------------+----------------
773 fkdd | "RI_FKey_check_ins" | 5 | t | t
774 fkdd | "RI_FKey_check_upd" | 17 | t | t
775 fkdd2 | "RI_FKey_check_ins" | 5 | t | t
776 fkdd2 | "RI_FKey_check_upd" | 17 | t | t
777 fkdi | "RI_FKey_check_ins" | 5 | t | f
778 fkdi | "RI_FKey_check_upd" | 17 | t | f
779 fkdi2 | "RI_FKey_check_ins" | 5 | t | f
780 fkdi2 | "RI_FKey_check_upd" | 17 | t | f
781 fknd | "RI_FKey_check_ins" | 5 | f | f
782 fknd | "RI_FKey_check_upd" | 17 | f | f
783 fknd2 | "RI_FKey_check_ins" | 5 | f | f
784 fknd2 | "RI_FKey_check_upd" | 17 | f | f
787 -- temp tables should go away by themselves, need not drop them.
788 -- test check constraint adding
789 create table atacc1 ( test int );
790 -- add a check constraint
791 alter table atacc1 add constraint atacc_test1 check (test>3);
793 insert into atacc1 (test) values (2);
794 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
795 DETAIL: Failing row contains (2).
797 insert into atacc1 (test) values (4);
799 -- let's do one where the check fails when added
800 create table atacc1 ( test int );
801 -- insert a soon to be failing row
802 insert into atacc1 (test) values (2);
803 -- add a check constraint (fails)
804 alter table atacc1 add constraint atacc_test1 check (test>3);
805 ERROR: check constraint "atacc_test1" of relation "atacc1" is violated by some row
806 insert into atacc1 (test) values (4);
808 -- let's do one where the check fails because the column doesn't exist
809 create table atacc1 ( test int );
810 -- add a check constraint (fails)
811 alter table atacc1 add constraint atacc_test1 check (test1>3);
812 ERROR: column "test1" does not exist
813 HINT: Perhaps you meant to reference the column "atacc1.test".
815 -- something a little more complicated
816 create table atacc1 ( test int, test2 int, test3 int);
817 -- add a check constraint (fails)
818 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
820 insert into atacc1 (test,test2,test3) values (4,4,2);
821 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
822 DETAIL: Failing row contains (4, 4, 2).
824 insert into atacc1 (test,test2,test3) values (4,4,5);
826 -- lets do some naming tests
827 create table atacc1 (test int check (test>3), test2 int);
828 alter table atacc1 add check (test2>test);
829 -- should fail for $2
830 insert into atacc1 (test2, test) values (3, 4);
831 ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
832 DETAIL: Failing row contains (4, 3).
834 -- inheritance related tests
835 create table atacc1 (test int);
836 create table atacc2 (test2 int);
837 create table atacc3 (test3 int) inherits (atacc1, atacc2);
838 alter table atacc2 add constraint foo check (test2>0);
839 -- fail and then succeed on atacc2
840 insert into atacc2 (test2) values (-3);
841 ERROR: new row for relation "atacc2" violates check constraint "foo"
842 DETAIL: Failing row contains (-3).
843 insert into atacc2 (test2) values (3);
844 -- fail and then succeed on atacc3
845 insert into atacc3 (test2) values (-3);
846 ERROR: new row for relation "atacc3" violates check constraint "foo"
847 DETAIL: Failing row contains (null, -3, null).
848 insert into atacc3 (test2) values (3);
852 -- same things with one created with INHERIT
853 create table atacc1 (test int);
854 create table atacc2 (test2 int);
855 create table atacc3 (test3 int) inherits (atacc1, atacc2);
856 alter table atacc3 no inherit atacc2;
858 alter table atacc3 no inherit atacc2;
859 ERROR: relation "atacc2" is not a parent of relation "atacc3"
860 -- make sure it really isn't a child
861 insert into atacc3 (test2) values (3);
862 select test2 from atacc2;
867 -- fail due to missing constraint
868 alter table atacc2 add constraint foo check (test2>0);
869 alter table atacc3 inherit atacc2;
870 ERROR: child table is missing constraint "foo"
871 -- fail due to missing column
872 alter table atacc3 rename test2 to testx;
873 alter table atacc3 inherit atacc2;
874 ERROR: child table is missing column "test2"
875 -- fail due to mismatched data type
876 alter table atacc3 add test2 bool;
877 alter table atacc3 inherit atacc2;
878 ERROR: child table "atacc3" has different type for column "test2"
879 alter table atacc3 drop test2;
881 alter table atacc3 add test2 int;
882 update atacc3 set test2 = 4 where test2 is null;
883 alter table atacc3 add constraint foo check (test2>0);
884 alter table atacc3 inherit atacc2;
885 -- fail due to duplicates and circular inheritance
886 alter table atacc3 inherit atacc2;
887 ERROR: relation "atacc2" would be inherited from more than once
888 alter table atacc2 inherit atacc3;
889 ERROR: circular inheritance not allowed
890 DETAIL: "atacc3" is already a child of "atacc2".
891 alter table atacc2 inherit atacc2;
892 ERROR: circular inheritance not allowed
893 DETAIL: "atacc2" is already a child of "atacc2".
894 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
895 select test2 from atacc2;
901 drop table atacc2 cascade;
902 NOTICE: drop cascades to table atacc3
904 -- adding only to a parent is allowed as of 9.2
905 create table atacc1 (test int);
906 create table atacc2 (test2 int) inherits (atacc1);
908 alter table atacc1 add constraint foo check (test>0) no inherit;
909 -- check constraint is not there on child
910 insert into atacc2 (test) values (-3);
911 -- check constraint is there on parent
912 insert into atacc1 (test) values (-3);
913 ERROR: new row for relation "atacc1" violates check constraint "foo"
914 DETAIL: Failing row contains (-3).
915 insert into atacc1 (test) values (3);
916 -- fail, violating row:
917 alter table atacc2 add constraint foo check (test>0) no inherit;
918 ERROR: check constraint "foo" of relation "atacc2" is violated by some row
921 -- test unique constraint adding
922 create table atacc1 ( test int ) ;
923 -- add a unique constraint
924 alter table atacc1 add constraint atacc_test1 unique (test);
925 -- insert first value
926 insert into atacc1 (test) values (2);
928 insert into atacc1 (test) values (2);
929 ERROR: duplicate key value violates unique constraint "atacc_test1"
930 DETAIL: Key (test)=(2) already exists.
932 insert into atacc1 (test) values (4);
933 -- try to create duplicates via alter table using - should fail
934 alter table atacc1 alter column test type integer using 0;
935 ERROR: could not create unique index "atacc_test1"
936 DETAIL: Key (test)=(0) is duplicated.
938 -- let's do one where the unique constraint fails when added
939 create table atacc1 ( test int );
940 -- insert soon to be failing rows
941 insert into atacc1 (test) values (2);
942 insert into atacc1 (test) values (2);
943 -- add a unique constraint (fails)
944 alter table atacc1 add constraint atacc_test1 unique (test);
945 ERROR: could not create unique index "atacc_test1"
946 DETAIL: Key (test)=(2) is duplicated.
947 insert into atacc1 (test) values (3);
949 -- let's do one where the unique constraint fails
950 -- because the column doesn't exist
951 create table atacc1 ( test int );
952 -- add a unique constraint (fails)
953 alter table atacc1 add constraint atacc_test1 unique (test1);
954 ERROR: column "test1" named in key does not exist
956 -- something a little more complicated
957 create table atacc1 ( test int, test2 int);
958 -- add a unique constraint
959 alter table atacc1 add constraint atacc_test1 unique (test, test2);
960 -- insert initial value
961 insert into atacc1 (test,test2) values (4,4);
963 insert into atacc1 (test,test2) values (4,4);
964 ERROR: duplicate key value violates unique constraint "atacc_test1"
965 DETAIL: Key (test, test2)=(4, 4) already exists.
966 -- should all succeed
967 insert into atacc1 (test,test2) values (4,5);
968 insert into atacc1 (test,test2) values (5,4);
969 insert into atacc1 (test,test2) values (5,5);
971 -- lets do some naming tests
972 create table atacc1 (test int, test2 int, unique(test));
973 alter table atacc1 add unique (test2);
974 -- should fail for @@ second one @@
975 insert into atacc1 (test2, test) values (3, 3);
976 insert into atacc1 (test2, test) values (2, 3);
977 ERROR: duplicate key value violates unique constraint "atacc1_test_key"
978 DETAIL: Key (test)=(3) already exists.
980 -- test primary key constraint adding
981 create table atacc1 ( id serial, test int) ;
982 -- add a primary key constraint
983 alter table atacc1 add constraint atacc_test1 primary key (test);
984 -- insert first value
985 insert into atacc1 (test) values (2);
987 insert into atacc1 (test) values (2);
988 ERROR: duplicate key value violates unique constraint "atacc_test1"
989 DETAIL: Key (test)=(2) already exists.
991 insert into atacc1 (test) values (4);
992 -- inserting NULL should fail
993 insert into atacc1 (test) values(NULL);
994 ERROR: null value in column "test" of relation "atacc1" violates not-null constraint
995 DETAIL: Failing row contains (4, null).
996 -- try adding a second primary key (should fail)
997 alter table atacc1 add constraint atacc_oid1 primary key(id);
998 ERROR: multiple primary keys for table "atacc1" are not allowed
999 -- drop first primary key constraint
1000 alter table atacc1 drop constraint atacc_test1 restrict;
1001 -- try adding a primary key on oid (should succeed)
1002 alter table atacc1 add constraint atacc_oid1 primary key(id);
1004 -- let's do one where the primary key constraint fails when added
1005 create table atacc1 ( test int );
1006 -- insert soon to be failing rows
1007 insert into atacc1 (test) values (2);
1008 insert into atacc1 (test) values (2);
1009 -- add a primary key (fails)
1010 alter table atacc1 add constraint atacc_test1 primary key (test);
1011 ERROR: could not create unique index "atacc_test1"
1012 DETAIL: Key (test)=(2) is duplicated.
1013 insert into atacc1 (test) values (3);
1015 -- let's do another one where the primary key constraint fails when added
1016 create table atacc1 ( test int );
1017 -- insert soon to be failing row
1018 insert into atacc1 (test) values (NULL);
1019 -- add a primary key (fails)
1020 alter table atacc1 add constraint atacc_test1 primary key (test);
1021 ERROR: column "test" of relation "atacc1" contains null values
1022 insert into atacc1 (test) values (3);
1024 -- let's do one where the primary key constraint fails
1025 -- because the column doesn't exist
1026 create table atacc1 ( test int );
1027 -- add a primary key constraint (fails)
1028 alter table atacc1 add constraint atacc_test1 primary key (test1);
1029 ERROR: column "test1" of relation "atacc1" does not exist
1031 -- adding a new column as primary key to a non-empty table.
1032 -- should fail unless the column has a non-null default value.
1033 create table atacc1 ( test int );
1034 insert into atacc1 (test) values (0);
1035 -- add a primary key column without a default (fails).
1036 alter table atacc1 add column test2 int primary key;
1037 ERROR: column "test2" of relation "atacc1" contains null values
1038 -- now add a primary key column with a default (succeeds).
1039 alter table atacc1 add column test2 int default 0 primary key;
1041 -- this combination used to have order-of-execution problems (bug #15580)
1042 create table atacc1 (a int);
1043 insert into atacc1 values(1);
1045 add column b float8 not null default random(),
1048 -- additionally, we've seen issues with foreign key validation not being
1049 -- properly delayed until after a table rewrite. Check that works ok.
1050 create table atacc1 (a int primary key);
1051 alter table atacc1 add constraint atacc1_fkey foreign key (a) references atacc1 (a) not valid;
1052 alter table atacc1 validate constraint atacc1_fkey, alter a type bigint;
1054 -- we've also seen issues with check constraints being validated at the wrong
1055 -- time when there's a pending table rewrite.
1056 create table atacc1 (a bigint, b int);
1057 insert into atacc1 values(1,1);
1058 alter table atacc1 add constraint atacc1_chk check(b = 1) not valid;
1059 alter table atacc1 validate constraint atacc1_chk, alter a type int;
1061 -- same as above, but ensure the constraint violation is detected
1062 create table atacc1 (a bigint, b int);
1063 insert into atacc1 values(1,2);
1064 alter table atacc1 add constraint atacc1_chk check(b = 1) not valid;
1065 alter table atacc1 validate constraint atacc1_chk, alter a type int;
1066 ERROR: check constraint "atacc1_chk" of relation "atacc1" is violated by some row
1068 -- something a little more complicated
1069 create table atacc1 ( test int, test2 int);
1070 -- add a primary key constraint
1071 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
1072 -- try adding a second primary key - should fail
1073 alter table atacc1 add constraint atacc_test2 primary key (test);
1074 ERROR: multiple primary keys for table "atacc1" are not allowed
1075 -- insert initial value
1076 insert into atacc1 (test,test2) values (4,4);
1078 insert into atacc1 (test,test2) values (4,4);
1079 ERROR: duplicate key value violates unique constraint "atacc_test1"
1080 DETAIL: Key (test, test2)=(4, 4) already exists.
1081 insert into atacc1 (test,test2) values (NULL,3);
1082 ERROR: null value in column "test" of relation "atacc1" violates not-null constraint
1083 DETAIL: Failing row contains (null, 3).
1084 insert into atacc1 (test,test2) values (3, NULL);
1085 ERROR: null value in column "test2" of relation "atacc1" violates not-null constraint
1086 DETAIL: Failing row contains (3, null).
1087 insert into atacc1 (test,test2) values (NULL,NULL);
1088 ERROR: null value in column "test" of relation "atacc1" violates not-null constraint
1089 DETAIL: Failing row contains (null, null).
1090 -- should all succeed
1091 insert into atacc1 (test,test2) values (4,5);
1092 insert into atacc1 (test,test2) values (5,4);
1093 insert into atacc1 (test,test2) values (5,5);
1095 -- lets do some naming tests
1096 create table atacc1 (test int, test2 int, primary key(test));
1097 -- only first should succeed
1098 insert into atacc1 (test2, test) values (3, 3);
1099 insert into atacc1 (test2, test) values (2, 3);
1100 ERROR: duplicate key value violates unique constraint "atacc1_pkey"
1101 DETAIL: Key (test)=(3) already exists.
1102 insert into atacc1 (test2, test) values (1, NULL);
1103 ERROR: null value in column "test" of relation "atacc1" violates not-null constraint
1104 DETAIL: Failing row contains (null, 1).
1106 -- alter table / alter column [set/drop] not null tests
1107 -- try altering system catalogs, should fail
1108 alter table pg_class alter column relname drop not null;
1109 ERROR: permission denied: "pg_class" is a system catalog
1110 alter table pg_class alter relname set not null;
1111 ERROR: permission denied: "pg_class" is a system catalog
1112 -- try altering non-existent table, should fail
1113 alter table non_existent alter column bar set not null;
1114 ERROR: relation "non_existent" does not exist
1115 alter table non_existent alter column bar drop not null;
1116 ERROR: relation "non_existent" does not exist
1117 -- test setting columns to null and not null and vice versa
1118 -- test checking for null values and primary key
1119 create table atacc1 (test int not null);
1120 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
1122 Table "public.atacc1"
1123 Column | Type | Collation | Nullable | Default
1124 --------+---------+-----------+----------+---------
1125 test | integer | | not null |
1127 "atacc1_pkey" PRIMARY KEY, btree (test)
1129 alter table atacc1 alter column test drop not null;
1130 ERROR: column "test" is in a primary key
1132 Table "public.atacc1"
1133 Column | Type | Collation | Nullable | Default
1134 --------+---------+-----------+----------+---------
1135 test | integer | | not null |
1137 "atacc1_pkey" PRIMARY KEY, btree (test)
1139 alter table atacc1 drop constraint "atacc1_pkey";
1140 alter table atacc1 alter column test drop not null;
1142 Table "public.atacc1"
1143 Column | Type | Collation | Nullable | Default
1144 --------+---------+-----------+----------+---------
1145 test | integer | | |
1147 insert into atacc1 values (null);
1148 alter table atacc1 alter test set not null;
1149 ERROR: column "test" of relation "atacc1" contains null values
1151 alter table atacc1 alter test set not null;
1152 -- try altering a non-existent column, should fail
1153 alter table atacc1 alter bar set not null;
1154 ERROR: column "bar" of relation "atacc1" does not exist
1155 alter table atacc1 alter bar drop not null;
1156 ERROR: column "bar" of relation "atacc1" does not exist
1157 -- try creating a view and altering that, should fail
1158 create view myview as select * from atacc1;
1159 alter table myview alter column test drop not null;
1160 ERROR: ALTER action ALTER COLUMN ... DROP NOT NULL cannot be performed on relation "myview"
1161 DETAIL: This operation is not supported for views.
1162 alter table myview alter column test set not null;
1163 ERROR: ALTER action ALTER COLUMN ... SET NOT NULL cannot be performed on relation "myview"
1164 DETAIL: This operation is not supported for views.
1167 -- set not null verified by constraints
1168 create table atacc1 (test_a int, test_b int);
1169 insert into atacc1 values (null, 1);
1170 -- constraint not cover all values, should fail
1171 alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10);
1172 alter table atacc1 alter test_a set not null;
1173 ERROR: column "test_a" of relation "atacc1" contains null values
1174 alter table atacc1 drop constraint atacc1_constr_or;
1175 -- not valid constraint, should fail
1176 alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid;
1177 alter table atacc1 alter test_a set not null;
1178 ERROR: column "test_a" of relation "atacc1" contains null values
1179 alter table atacc1 drop constraint atacc1_constr_invalid;
1180 -- with valid constraint
1181 update atacc1 set test_a = 1;
1182 alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null);
1183 alter table atacc1 alter test_a set not null;
1185 insert into atacc1 values (2, null);
1186 alter table atacc1 alter test_a drop not null;
1187 -- test multiple set not null at same time
1188 -- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan
1189 alter table atacc1 alter test_a set not null, alter test_b set not null;
1190 ERROR: column "test_b" of relation "atacc1" contains null values
1191 -- commands order has no importance
1192 alter table atacc1 alter test_b set not null, alter test_a set not null;
1193 ERROR: column "test_b" of relation "atacc1" contains null values
1194 -- valid one by table scan, one by check constraints
1195 update atacc1 set test_b = 1;
1196 alter table atacc1 alter test_b set not null, alter test_a set not null;
1197 alter table atacc1 alter test_a drop not null, alter test_b drop not null;
1198 -- both column has check constraints
1199 alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
1200 alter table atacc1 alter test_b set not null, alter test_a set not null;
1203 create table parent (a int);
1204 create table child (b varchar(255)) inherits (parent);
1205 alter table parent alter a set not null;
1206 insert into parent values (NULL);
1207 ERROR: null value in column "a" of relation "parent" violates not-null constraint
1208 DETAIL: Failing row contains (null).
1209 insert into child (a, b) values (NULL, 'foo');
1210 ERROR: null value in column "a" of relation "child" violates not-null constraint
1211 DETAIL: Failing row contains (null, foo).
1212 alter table parent alter a drop not null;
1213 insert into parent values (NULL);
1214 insert into child (a, b) values (NULL, 'foo');
1215 alter table only parent alter a set not null;
1216 ERROR: column "a" of relation "parent" contains null values
1217 alter table child alter a set not null;
1218 ERROR: column "a" of relation "child" contains null values
1220 alter table only parent alter a set not null;
1221 insert into parent values (NULL);
1222 ERROR: null value in column "a" of relation "parent" violates not-null constraint
1223 DETAIL: Failing row contains (null).
1224 alter table child alter a set not null;
1225 insert into child (a, b) values (NULL, 'foo');
1226 ERROR: null value in column "a" of relation "child" violates not-null constraint
1227 DETAIL: Failing row contains (null, foo).
1229 alter table child alter a set not null;
1230 insert into child (a, b) values (NULL, 'foo');
1231 ERROR: null value in column "a" of relation "child" violates not-null constraint
1232 DETAIL: Failing row contains (null, foo).
1235 -- test setting and removing default values
1236 create table def_test (
1238 c2 text default 'initial_default'
1240 insert into def_test default values;
1241 alter table def_test alter column c1 drop default;
1242 insert into def_test default values;
1243 alter table def_test alter column c2 drop default;
1244 insert into def_test default values;
1245 alter table def_test alter column c1 set default 10;
1246 alter table def_test alter column c2 set default 'new_default';
1247 insert into def_test default values;
1248 select * from def_test;
1250 ----+-----------------
1257 -- set defaults to an incorrect type: this should fail
1258 alter table def_test alter column c1 set default 'wrong_datatype';
1259 ERROR: invalid input syntax for type integer: "wrong_datatype"
1260 alter table def_test alter column c2 set default 20;
1261 -- set defaults on a non-existent column: this should fail
1262 alter table def_test alter column c3 set default 30;
1263 ERROR: column "c3" of relation "def_test" does not exist
1264 -- set defaults on views: we need to create a view, add a rule
1265 -- to allow insertions into it, and then alter the view to add
1267 create view def_view_test as select * from def_test;
1268 create rule def_view_test_ins as
1269 on insert to def_view_test
1270 do instead insert into def_test select new.*;
1271 insert into def_view_test default values;
1272 alter table def_view_test alter column c1 set default 45;
1273 insert into def_view_test default values;
1274 alter table def_view_test alter column c2 set default 'view_default';
1275 insert into def_view_test default values;
1276 select * from def_view_test;
1278 ----+-----------------
1288 drop rule def_view_test_ins on def_view_test;
1289 drop view def_view_test;
1290 drop table def_test;
1291 -- alter table / drop column tests
1292 -- try altering system catalogs, should fail
1293 alter table pg_class drop column relname;
1294 ERROR: permission denied: "pg_class" is a system catalog
1295 -- try altering non-existent table, should fail
1296 alter table nosuchtable drop column bar;
1297 ERROR: relation "nosuchtable" does not exist
1298 -- test dropping columns
1299 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
1300 insert into atacc1 values (1, 2, 3, 4);
1301 alter table atacc1 drop a;
1302 alter table atacc1 drop a;
1303 ERROR: column "a" of relation "atacc1" does not exist
1305 select * from atacc1;
1311 select * from atacc1 order by a;
1312 ERROR: column "a" does not exist
1313 LINE 1: select * from atacc1 order by a;
1315 select * from atacc1 order by "........pg.dropped.1........";
1316 ERROR: column "........pg.dropped.1........" does not exist
1317 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
1319 select * from atacc1 group by a;
1320 ERROR: column "a" does not exist
1321 LINE 1: select * from atacc1 group by a;
1323 select * from atacc1 group by "........pg.dropped.1........";
1324 ERROR: column "........pg.dropped.1........" does not exist
1325 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
1327 select atacc1.* from atacc1;
1333 select a from atacc1;
1334 ERROR: column "a" does not exist
1335 LINE 1: select a from atacc1;
1337 select atacc1.a from atacc1;
1338 ERROR: column atacc1.a does not exist
1339 LINE 1: select atacc1.a from atacc1;
1341 select b,c,d from atacc1;
1347 select a,b,c,d from atacc1;
1348 ERROR: column "a" does not exist
1349 LINE 1: select a,b,c,d from atacc1;
1351 select * from atacc1 where a = 1;
1352 ERROR: column "a" does not exist
1353 LINE 1: select * from atacc1 where a = 1;
1355 select "........pg.dropped.1........" from atacc1;
1356 ERROR: column "........pg.dropped.1........" does not exist
1357 LINE 1: select "........pg.dropped.1........" from atacc1;
1359 select atacc1."........pg.dropped.1........" from atacc1;
1360 ERROR: column atacc1.........pg.dropped.1........ does not exist
1361 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
1363 select "........pg.dropped.1........",b,c,d from atacc1;
1364 ERROR: column "........pg.dropped.1........" does not exist
1365 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
1367 select * from atacc1 where "........pg.dropped.1........" = 1;
1368 ERROR: column "........pg.dropped.1........" does not exist
1369 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
1372 update atacc1 set a = 3;
1373 ERROR: column "a" of relation "atacc1" does not exist
1374 LINE 1: update atacc1 set a = 3;
1376 update atacc1 set b = 2 where a = 3;
1377 ERROR: column "a" does not exist
1378 LINE 1: update atacc1 set b = 2 where a = 3;
1380 update atacc1 set "........pg.dropped.1........" = 3;
1381 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1382 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
1384 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
1385 ERROR: column "........pg.dropped.1........" does not exist
1386 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
1389 insert into atacc1 values (10, 11, 12, 13);
1390 ERROR: INSERT has more expressions than target columns
1391 LINE 1: insert into atacc1 values (10, 11, 12, 13);
1393 insert into atacc1 values (default, 11, 12, 13);
1394 ERROR: INSERT has more expressions than target columns
1395 LINE 1: insert into atacc1 values (default, 11, 12, 13);
1397 insert into atacc1 values (11, 12, 13);
1398 insert into atacc1 (a) values (10);
1399 ERROR: column "a" of relation "atacc1" does not exist
1400 LINE 1: insert into atacc1 (a) values (10);
1402 insert into atacc1 (a) values (default);
1403 ERROR: column "a" of relation "atacc1" does not exist
1404 LINE 1: insert into atacc1 (a) values (default);
1406 insert into atacc1 (a,b,c,d) values (10,11,12,13);
1407 ERROR: column "a" of relation "atacc1" does not exist
1408 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
1410 insert into atacc1 (a,b,c,d) values (default,11,12,13);
1411 ERROR: column "a" of relation "atacc1" does not exist
1412 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
1414 insert into atacc1 (b,c,d) values (11,12,13);
1415 insert into atacc1 ("........pg.dropped.1........") values (10);
1416 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1417 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
1419 insert into atacc1 ("........pg.dropped.1........") values (default);
1420 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1421 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
1423 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
1424 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1425 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
1427 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
1428 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1429 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
1432 delete from atacc1 where a = 3;
1433 ERROR: column "a" does not exist
1434 LINE 1: delete from atacc1 where a = 3;
1436 delete from atacc1 where "........pg.dropped.1........" = 3;
1437 ERROR: column "........pg.dropped.1........" does not exist
1438 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
1441 -- try dropping a non-existent column, should fail
1442 alter table atacc1 drop bar;
1443 ERROR: column "bar" of relation "atacc1" does not exist
1444 -- try removing an oid column, should succeed (as it's nonexistent)
1445 alter table atacc1 SET WITHOUT OIDS;
1446 -- try adding an oid column, should fail (not supported)
1447 alter table atacc1 SET WITH OIDS;
1448 ERROR: syntax error at or near "WITH"
1449 LINE 1: alter table atacc1 SET WITH OIDS;
1451 -- try dropping the xmin column, should fail
1452 alter table atacc1 drop xmin;
1453 ERROR: cannot drop system column "xmin"
1454 -- try creating a view and altering that, should fail
1455 create view myview as select * from atacc1;
1456 select * from myview;
1461 alter table myview drop d;
1462 ERROR: ALTER action DROP COLUMN cannot be performed on relation "myview"
1463 DETAIL: This operation is not supported for views.
1465 -- test some commands to make sure they fail on the dropped column
1467 ERROR: column "a" of relation "atacc1" does not exist
1468 analyze atacc1("........pg.dropped.1........");
1469 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1470 vacuum analyze atacc1(a);
1471 ERROR: column "a" of relation "atacc1" does not exist
1472 vacuum analyze atacc1("........pg.dropped.1........");
1473 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1474 comment on column atacc1.a is 'testing';
1475 ERROR: column "a" of relation "atacc1" does not exist
1476 comment on column atacc1."........pg.dropped.1........" is 'testing';
1477 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1478 alter table atacc1 alter a set storage plain;
1479 ERROR: column "a" of relation "atacc1" does not exist
1480 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
1481 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1482 alter table atacc1 alter a set statistics 0;
1483 ERROR: column "a" of relation "atacc1" does not exist
1484 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
1485 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1486 alter table atacc1 alter a set default 3;
1487 ERROR: column "a" of relation "atacc1" does not exist
1488 alter table atacc1 alter "........pg.dropped.1........" set default 3;
1489 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1490 alter table atacc1 alter a drop default;
1491 ERROR: column "a" of relation "atacc1" does not exist
1492 alter table atacc1 alter "........pg.dropped.1........" drop default;
1493 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1494 alter table atacc1 alter a set not null;
1495 ERROR: column "a" of relation "atacc1" does not exist
1496 alter table atacc1 alter "........pg.dropped.1........" set not null;
1497 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1498 alter table atacc1 alter a drop not null;
1499 ERROR: column "a" of relation "atacc1" does not exist
1500 alter table atacc1 alter "........pg.dropped.1........" drop not null;
1501 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1502 alter table atacc1 rename a to x;
1503 ERROR: column "a" does not exist
1504 alter table atacc1 rename "........pg.dropped.1........" to x;
1505 ERROR: column "........pg.dropped.1........" does not exist
1506 alter table atacc1 add primary key(a);
1507 ERROR: column "a" of relation "atacc1" does not exist
1508 alter table atacc1 add primary key("........pg.dropped.1........");
1509 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1510 alter table atacc1 add unique(a);
1511 ERROR: column "a" named in key does not exist
1512 alter table atacc1 add unique("........pg.dropped.1........");
1513 ERROR: column "........pg.dropped.1........" named in key does not exist
1514 alter table atacc1 add check (a > 3);
1515 ERROR: column "a" does not exist
1516 alter table atacc1 add check ("........pg.dropped.1........" > 3);
1517 ERROR: column "........pg.dropped.1........" does not exist
1518 create table atacc2 (id int4 unique);
1519 alter table atacc1 add foreign key (a) references atacc2(id);
1520 ERROR: column "a" referenced in foreign key constraint does not exist
1521 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
1522 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1523 alter table atacc2 add foreign key (id) references atacc1(a);
1524 ERROR: column "a" referenced in foreign key constraint does not exist
1525 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
1526 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1528 create index "testing_idx" on atacc1(a);
1529 ERROR: column "a" does not exist
1530 create index "testing_idx" on atacc1("........pg.dropped.1........");
1531 ERROR: column "........pg.dropped.1........" does not exist
1532 -- test create as and select into
1533 insert into atacc1 values (21, 22, 23);
1534 create table attest1 as select * from atacc1;
1535 select * from attest1;
1542 select * into attest2 from atacc1;
1543 select * from attest2;
1550 -- try dropping all columns
1551 alter table atacc1 drop c;
1552 alter table atacc1 drop d;
1553 alter table atacc1 drop b;
1554 select * from atacc1;
1559 -- test constraint error reporting in presence of dropped columns
1560 create table atacc1 (id serial primary key, value int check (value < 10));
1561 insert into atacc1(value) values (100);
1562 ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check"
1563 DETAIL: Failing row contains (1, 100).
1564 alter table atacc1 drop column value;
1565 alter table atacc1 add column value int check (value < 10);
1566 insert into atacc1(value) values (100);
1567 ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check"
1568 DETAIL: Failing row contains (2, 100).
1569 insert into atacc1(id, value) values (null, 0);
1570 ERROR: null value in column "id" of relation "atacc1" violates not-null constraint
1571 DETAIL: Failing row contains (null, 0).
1574 create table parent (a int, b int, c int);
1575 insert into parent values (1, 2, 3);
1576 alter table parent drop a;
1577 create table child (d varchar(255)) inherits (parent);
1578 insert into child values (12, 13, 'testing');
1579 select * from parent;
1586 select * from child;
1592 alter table parent drop c;
1593 select * from parent;
1600 select * from child;
1608 -- check error cases for inheritance column merging
1609 create table parent (a float8, b numeric(10,4), c text collate "C");
1610 create table child (a float4) inherits (parent); -- fail
1611 NOTICE: merging column "a" with inherited definition
1612 ERROR: column "a" has a type conflict
1613 DETAIL: double precision versus real
1614 create table child (b decimal(10,7)) inherits (parent); -- fail
1615 NOTICE: moving and merging column "b" with inherited definition
1616 DETAIL: User-specified column moved to the position of the inherited column.
1617 ERROR: column "b" has a type conflict
1618 DETAIL: numeric(10,4) versus numeric(10,7)
1619 create table child (c text collate "POSIX") inherits (parent); -- fail
1620 NOTICE: moving and merging column "c" with inherited definition
1621 DETAIL: User-specified column moved to the position of the inherited column.
1622 ERROR: column "c" has a collation conflict
1623 DETAIL: "C" versus "POSIX"
1624 create table child (a double precision, b decimal(10,4)) inherits (parent);
1625 NOTICE: merging column "a" with inherited definition
1626 NOTICE: merging column "b" with inherited definition
1630 create table attest (a int4, b int4, c int4);
1631 insert into attest values (1,2,3);
1632 alter table attest drop a;
1633 copy attest to stdout;
1635 copy attest(a) to stdout;
1636 ERROR: column "a" of relation "attest" does not exist
1637 copy attest("........pg.dropped.1........") to stdout;
1638 ERROR: column "........pg.dropped.1........" of relation "attest" does not exist
1639 copy attest from stdin;
1640 ERROR: extra data after last expected column
1641 CONTEXT: COPY attest, line 1: "10 11 12"
1642 select * from attest;
1648 copy attest from stdin;
1649 select * from attest;
1656 copy attest(a) from stdin;
1657 ERROR: column "a" of relation "attest" does not exist
1658 copy attest("........pg.dropped.1........") from stdin;
1659 ERROR: column "........pg.dropped.1........" of relation "attest" does not exist
1660 copy attest(b,c) from stdin;
1661 select * from attest;
1671 create table dropColumn (a int, b int, e int);
1672 create table dropColumnChild (c int) inherits (dropColumn);
1673 create table dropColumnAnother (d int) inherits (dropColumnChild);
1674 -- these two should fail
1675 alter table dropColumnchild drop column a;
1676 ERROR: cannot drop inherited column "a"
1677 alter table only dropColumnChild drop column b;
1678 ERROR: cannot drop inherited column "b"
1679 -- these three should work
1680 alter table only dropColumn drop column e;
1681 alter table dropColumnChild drop column c;
1682 alter table dropColumn drop column a;
1683 create table renameColumn (a int);
1684 create table renameColumnChild (b int) inherits (renameColumn);
1685 create table renameColumnAnother (c int) inherits (renameColumnChild);
1686 -- these three should fail
1687 alter table renameColumnChild rename column a to d;
1688 ERROR: cannot rename inherited column "a"
1689 alter table only renameColumnChild rename column a to d;
1690 ERROR: inherited column "a" must be renamed in child tables too
1691 alter table only renameColumn rename column a to d;
1692 ERROR: inherited column "a" must be renamed in child tables too
1693 -- these should work
1694 alter table renameColumn rename column a to d;
1695 alter table renameColumnChild rename column b to a;
1696 -- these should work
1697 alter table if exists doesnt_exist_tab rename column a to d;
1698 NOTICE: relation "doesnt_exist_tab" does not exist, skipping
1699 alter table if exists doesnt_exist_tab rename column b to a;
1700 NOTICE: relation "doesnt_exist_tab" does not exist, skipping
1702 alter table renameColumn add column w int;
1704 alter table only renameColumn add column x int;
1705 ERROR: column must be added to child tables too
1706 -- Test corner cases in dropping of inherited columns
1707 create table p1 (f1 int, f2 int);
1708 create table c1 (f1 int not null) inherits(p1);
1709 NOTICE: merging column "f1" with inherited definition
1710 -- should be rejected since c1.f1 is inherited
1711 alter table c1 drop column f1;
1712 ERROR: cannot drop inherited column "f1"
1714 alter table p1 drop column f1;
1715 -- c1.f1 is still there, but no longer inherited
1721 alter table c1 drop column f1;
1723 ERROR: column "f1" does not exist
1724 LINE 1: select f1 from c1;
1726 HINT: Perhaps you meant to reference the column "c1.f2".
1727 drop table p1 cascade;
1728 NOTICE: drop cascades to table c1
1729 create table p1 (f1 int, f2 int);
1730 create table c1 () inherits(p1);
1731 -- should be rejected since c1.f1 is inherited
1732 alter table c1 drop column f1;
1733 ERROR: cannot drop inherited column "f1"
1734 alter table p1 drop column f1;
1735 -- c1.f1 is dropped now, since there is no local definition for it
1737 ERROR: column "f1" does not exist
1738 LINE 1: select f1 from c1;
1740 HINT: Perhaps you meant to reference the column "c1.f2".
1741 drop table p1 cascade;
1742 NOTICE: drop cascades to table c1
1743 create table p1 (f1 int, f2 int);
1744 create table c1 () inherits(p1);
1745 -- should be rejected since c1.f1 is inherited
1746 alter table c1 drop column f1;
1747 ERROR: cannot drop inherited column "f1"
1748 alter table only p1 drop column f1;
1749 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1750 alter table c1 drop column f1;
1751 drop table p1 cascade;
1752 NOTICE: drop cascades to table c1
1753 create table p1 (f1 int, f2 int);
1754 create table c1 (f1 int not null) inherits(p1);
1755 NOTICE: merging column "f1" with inherited definition
1756 -- should be rejected since c1.f1 is inherited
1757 alter table c1 drop column f1;
1758 ERROR: cannot drop inherited column "f1"
1759 alter table only p1 drop column f1;
1760 -- c1.f1 is still there, but no longer inherited
1761 alter table c1 drop column f1;
1762 drop table p1 cascade;
1763 NOTICE: drop cascades to table c1
1764 create table p1(id int, name text);
1765 create table p2(id2 int, name text, height int);
1766 create table c1(age int) inherits(p1,p2);
1767 NOTICE: merging multiple inherited definitions of column "name"
1768 create table gc1() inherits (c1);
1769 select relname, attname, attinhcount, attislocal
1770 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1771 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1772 order by relname, attnum;
1773 relname | attname | attinhcount | attislocal
1774 ---------+---------+-------------+------------
1783 gc1 | height | 1 | f
1793 alter table only p1 drop column name;
1794 -- should work. Now c1.name is local and inhcount is 0.
1795 alter table p2 drop column name;
1796 -- should be rejected since its inherited
1797 alter table gc1 drop column name;
1798 ERROR: cannot drop inherited column "name"
1799 -- should work, and drop gc1.name along
1800 alter table c1 drop column name;
1801 -- should fail: column does not exist
1802 alter table gc1 drop column name;
1803 ERROR: column "name" of relation "gc1" does not exist
1804 -- should work and drop the attribute in all tables
1805 alter table p2 drop column height;
1807 create table dropColumnExists ();
1808 alter table dropColumnExists drop column non_existing; --fail
1809 ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
1810 alter table dropColumnExists drop column if exists non_existing; --succeed
1811 NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
1812 select relname, attname, attinhcount, attislocal
1813 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1814 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1815 order by relname, attnum;
1816 relname | attname | attinhcount | attislocal
1817 ---------+---------+-------------+------------
1828 drop table p1, p2 cascade;
1829 NOTICE: drop cascades to 2 other objects
1830 DETAIL: drop cascades to table c1
1831 drop cascades to table gc1
1832 -- test attinhcount tracking with merged columns
1833 create table depth0();
1834 create table depth1(c text) inherits (depth0);
1835 create table depth2() inherits (depth1);
1836 alter table depth0 add c text;
1837 NOTICE: merging definition of column "c" for child "depth1"
1838 select attrelid::regclass, attname, attinhcount, attislocal
1840 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1841 order by attrelid::regclass::text, attnum;
1842 attrelid | attname | attinhcount | attislocal
1843 ----------+---------+-------------+------------
1849 -- test renumbering of child-table columns in inherited operations
1850 create table p1 (f1 int);
1851 create table c1 (f2 text, f3 int) inherits (p1);
1852 alter table p1 add column a1 int check (a1 > 0);
1853 alter table p1 add column f2 text;
1854 NOTICE: merging definition of column "f2" for child "c1"
1855 insert into p1 values (1,2,'abc');
1856 insert into c1 values(11,'xyz',33,0); -- should fail
1857 ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1858 DETAIL: Failing row contains (11, xyz, 33, 0).
1859 insert into c1 values(11,'xyz',33,22);
1867 update p1 set a1 = a1 + 1, f2 = upper(f2);
1875 drop table p1 cascade;
1876 NOTICE: drop cascades to table c1
1877 -- test that operations with a dropped column do not try to reference
1879 create domain mytype as text;
1880 create temp table foo (f1 text, f2 mytype, f3 text);
1881 insert into foo values('bb','cc','dd');
1888 drop domain mytype cascade;
1889 NOTICE: drop cascades to column f2 of table foo
1896 insert into foo values('qq','rr');
1904 update foo set f3 = 'zz';
1912 select f3,max(f1) from foo group by f3;
1918 -- Simple tests for alter table column type
1919 alter table foo alter f1 TYPE integer; -- fails
1920 ERROR: column "f1" cannot be cast automatically to type integer
1921 HINT: You might need to specify "USING f1::integer".
1922 alter table foo alter f1 TYPE varchar(10);
1923 create table anothertab (atcol1 serial8, atcol2 boolean,
1924 constraint anothertab_chk check (atcol1 <= 3));
1925 insert into anothertab (atcol1, atcol2) values (default, true);
1926 insert into anothertab (atcol1, atcol2) values (default, false);
1927 select * from anothertab;
1934 alter table anothertab alter column atcol1 type boolean; -- fails
1935 ERROR: column "atcol1" cannot be cast automatically to type boolean
1936 HINT: You might need to specify "USING atcol1::boolean".
1937 alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails
1938 ERROR: result of USING clause for column "atcol1" cannot be cast automatically to type boolean
1939 HINT: You might need to add an explicit cast.
1940 alter table anothertab alter column atcol1 type integer;
1941 select * from anothertab;
1948 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1949 ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1950 DETAIL: Failing row contains (45, null).
1951 insert into anothertab (atcol1, atcol2) values (default, null);
1952 select * from anothertab;
1960 alter table anothertab alter column atcol2 type text
1961 using case when atcol2 is true then 'IT WAS TRUE'
1962 when atcol2 is false then 'IT WAS FALSE'
1963 else 'IT WAS NULL!' end;
1964 select * from anothertab;
1966 --------+--------------
1972 alter table anothertab alter column atcol1 type boolean
1973 using case when atcol1 % 2 = 0 then true else false end; -- fails
1974 ERROR: default for column "atcol1" cannot be cast automatically to type boolean
1975 alter table anothertab alter column atcol1 drop default;
1976 alter table anothertab alter column atcol1 type boolean
1977 using case when atcol1 % 2 = 0 then true else false end; -- fails
1978 ERROR: operator does not exist: boolean <= integer
1979 HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
1980 alter table anothertab drop constraint anothertab_chk;
1981 alter table anothertab drop constraint anothertab_chk; -- fails
1982 ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
1983 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1984 NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
1985 alter table anothertab alter column atcol1 type boolean
1986 using case when atcol1 % 2 = 0 then true else false end;
1987 select * from anothertab;
1989 --------+--------------
1995 drop table anothertab;
1996 -- Test index handling in alter table column type (cf. bugs #15835, #15865)
1997 create table anothertab(f1 int primary key, f2 int unique,
1998 f3 int, f4 int, f5 int);
1999 alter table anothertab
2000 add exclude using btree (f3 with =);
2001 alter table anothertab
2002 add exclude using btree (f4 with =) where (f4 is not null);
2003 alter table anothertab
2004 add exclude using btree (f4 with =) where (f5 > 0);
2005 alter table anothertab
2007 create index on anothertab(f2,f3);
2008 create unique index on anothertab(f4);
2010 Table "public.anothertab"
2011 Column | Type | Collation | Nullable | Default
2012 --------+---------+-----------+----------+---------
2013 f1 | integer | | not null |
2019 "anothertab_pkey" PRIMARY KEY, btree (f1)
2020 "anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4)
2021 "anothertab_f2_f3_idx" btree (f2, f3)
2022 "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
2023 "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
2024 "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
2025 "anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0)
2026 "anothertab_f4_idx" UNIQUE, btree (f4)
2028 alter table anothertab alter column f1 type bigint;
2029 alter table anothertab
2030 alter column f2 type bigint,
2031 alter column f3 type bigint,
2032 alter column f4 type bigint;
2033 alter table anothertab alter column f5 type bigint;
2035 Table "public.anothertab"
2036 Column | Type | Collation | Nullable | Default
2037 --------+--------+-----------+----------+---------
2038 f1 | bigint | | not null |
2044 "anothertab_pkey" PRIMARY KEY, btree (f1)
2045 "anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4)
2046 "anothertab_f2_f3_idx" btree (f2, f3)
2047 "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
2048 "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
2049 "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
2050 "anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0)
2051 "anothertab_f4_idx" UNIQUE, btree (f4)
2053 drop table anothertab;
2054 -- test that USING expressions are parsed before column alter type / drop steps
2055 create table another (f1 int, f2 text, f3 text);
2056 insert into another values(1, 'one', 'uno');
2057 insert into another values(2, 'two', 'due');
2058 insert into another values(3, 'three', 'tre');
2059 select * from another;
2068 alter f1 type text using f2 || ' and ' || f3 || ' more',
2069 alter f2 type bigint using f1 * 10,
2071 select * from another;
2073 --------------------+----
2074 one and uno more | 10
2075 two and due more | 20
2076 three and tre more | 30
2080 -- Create an index that skips WAL, then perform a SET DATA TYPE that skips
2081 -- rewriting the index.
2083 create table skip_wal_skip_rewrite_index (c varchar(10) primary key);
2084 alter table skip_wal_skip_rewrite_index alter c type varchar(20);
2086 -- We disallow changing table's row type if it's used for storage
2087 create table at_tab1 (a int, b text);
2088 create table at_tab2 (x int, y at_tab1);
2089 alter table at_tab1 alter column b type varchar; -- fails
2090 ERROR: cannot alter table "at_tab1" because column "at_tab2.y" uses its row type
2092 -- Use of row type in an expression is defended differently
2093 create table at_tab2 (x int, y text, check((x,y)::at_tab1 = (1,'42')::at_tab1));
2094 alter table at_tab1 alter column b type varchar; -- allowed, but ...
2095 insert into at_tab2 values(1,'42'); -- ... this will fail
2096 ERROR: ROW() column has type text instead of type character varying
2097 drop table at_tab1, at_tab2;
2098 -- Check it for a partitioned table, too
2099 create table at_tab1 (a int, b text) partition by list(a);
2100 create table at_tab2 (x int, y at_tab1);
2101 alter table at_tab1 alter column b type varchar; -- fails
2102 ERROR: cannot alter table "at_tab1" because column "at_tab2.y" uses its row type
2103 drop table at_tab1, at_tab2;
2104 -- Alter column type that's part of a partitioned index
2105 create table at_partitioned (a int, b text) partition by range (a);
2106 create table at_part_1 partition of at_partitioned for values from (0) to (1000);
2107 insert into at_partitioned values (512, '0.123');
2108 create table at_part_2 (b text, a int);
2109 insert into at_part_2 values ('1.234', 1024);
2110 create index on at_partitioned (b);
2111 create index on at_partitioned (a);
2113 Table "public.at_part_1"
2114 Column | Type | Collation | Nullable | Default
2115 --------+---------+-----------+----------+---------
2118 Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
2120 "at_part_1_a_idx" btree (a)
2121 "at_part_1_b_idx" btree (b)
2124 Table "public.at_part_2"
2125 Column | Type | Collation | Nullable | Default
2126 --------+---------+-----------+----------+---------
2130 alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000);
2132 Table "public.at_part_2"
2133 Column | Type | Collation | Nullable | Default
2134 --------+---------+-----------+----------+---------
2137 Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
2139 "at_part_2_a_idx" btree (a)
2140 "at_part_2_b_idx" btree (b)
2142 alter table at_partitioned alter column b type numeric using b::numeric;
2144 Table "public.at_part_1"
2145 Column | Type | Collation | Nullable | Default
2146 --------+---------+-----------+----------+---------
2149 Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
2151 "at_part_1_a_idx" btree (a)
2152 "at_part_1_b_idx" btree (b)
2155 Table "public.at_part_2"
2156 Column | Type | Collation | Nullable | Default
2157 --------+---------+-----------+----------+---------
2160 Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
2162 "at_part_2_a_idx" btree (a)
2163 "at_part_2_b_idx" btree (b)
2165 drop table at_partitioned;
2166 -- Alter column type when no table rewrite is required
2167 -- Also check that comments are preserved
2168 create table at_partitioned(id int, name varchar(64), unique (id, name))
2169 partition by hash(id);
2170 comment on constraint at_partitioned_id_name_key on at_partitioned is 'parent constraint';
2171 comment on index at_partitioned_id_name_key is 'parent index';
2172 create table at_partitioned_0 partition of at_partitioned
2173 for values with (modulus 2, remainder 0);
2174 comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint';
2175 comment on index at_partitioned_0_id_name_key is 'child 0 index';
2176 create table at_partitioned_1 partition of at_partitioned
2177 for values with (modulus 2, remainder 1);
2178 comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint';
2179 comment on index at_partitioned_1_id_name_key is 'child 1 index';
2180 insert into at_partitioned values(1, 'foo');
2181 insert into at_partitioned values(3, 'bar');
2182 create temp table old_oids as
2183 select relname, oid as oldoid, relfilenode as oldfilenode
2184 from pg_class where relname like 'at_partitioned%';
2186 c.oid = oldoid as orig_oid,
2189 when c.oid then 'own'
2190 when oldfilenode then 'orig'
2193 obj_description(c.oid, 'pg_class') as desc
2194 from pg_class c left join old_oids using (relname)
2195 where relname like 'at_partitioned%'
2197 relname | orig_oid | storage | desc
2198 ------------------------------+----------+---------+---------------
2199 at_partitioned | t | none |
2200 at_partitioned_0 | t | own |
2201 at_partitioned_0_id_name_key | t | own | child 0 index
2202 at_partitioned_1 | t | own |
2203 at_partitioned_1_id_name_key | t | own | child 1 index
2204 at_partitioned_id_name_key | t | none | parent index
2207 select conname, obj_description(oid, 'pg_constraint') as desc
2208 from pg_constraint where conname like 'at_partitioned%'
2211 ------------------------------+--------------------
2212 at_partitioned_0_id_name_key | child 0 constraint
2213 at_partitioned_1_id_name_key | child 1 constraint
2214 at_partitioned_id_name_key | parent constraint
2217 alter table at_partitioned alter column name type varchar(127);
2218 -- Note: these tests currently show the wrong behavior for comments :-(
2220 c.oid = oldoid as orig_oid,
2223 when c.oid then 'own'
2224 when oldfilenode then 'orig'
2227 obj_description(c.oid, 'pg_class') as desc
2228 from pg_class c left join old_oids using (relname)
2229 where relname like 'at_partitioned%'
2231 relname | orig_oid | storage | desc
2232 ------------------------------+----------+---------+--------------
2233 at_partitioned | t | none |
2234 at_partitioned_0 | t | own |
2235 at_partitioned_0_id_name_key | f | own | parent index
2236 at_partitioned_1 | t | own |
2237 at_partitioned_1_id_name_key | f | own | parent index
2238 at_partitioned_id_name_key | f | none | parent index
2241 select conname, obj_description(oid, 'pg_constraint') as desc
2242 from pg_constraint where conname like 'at_partitioned%'
2245 ------------------------------+-------------------
2246 at_partitioned_0_id_name_key |
2247 at_partitioned_1_id_name_key |
2248 at_partitioned_id_name_key | parent constraint
2251 -- Don't remove this DROP, it exposes bug #15672
2252 drop table at_partitioned;
2253 -- disallow recursive containment of row types
2254 create temp table recur1 (f1 int);
2255 alter table recur1 add column f2 recur1; -- fails
2256 ERROR: composite type recur1 cannot be made a member of itself
2257 alter table recur1 add column f2 recur1[]; -- fails
2258 ERROR: composite type recur1 cannot be made a member of itself
2259 create domain array_of_recur1 as recur1[];
2260 alter table recur1 add column f2 array_of_recur1; -- fails
2261 ERROR: composite type recur1 cannot be made a member of itself
2262 create temp table recur2 (f1 int, f2 recur1);
2263 alter table recur1 add column f2 recur2; -- fails
2264 ERROR: composite type recur1 cannot be made a member of itself
2265 alter table recur1 add column f2 int;
2266 alter table recur1 alter column f2 type recur2; -- fails
2267 ERROR: composite type recur1 cannot be made a member of itself
2268 -- SET STORAGE may need to add a TOAST table
2269 create table test_storage (a text, c text storage plain);
2270 select reltoastrelid <> 0 as has_toast_table
2271 from pg_class where oid = 'test_storage'::regclass;
2277 alter table test_storage alter a set storage plain;
2278 -- rewrite table to remove its TOAST table; need a non-constant column default
2279 alter table test_storage add b int default random()::int;
2280 select reltoastrelid <> 0 as has_toast_table
2281 from pg_class where oid = 'test_storage'::regclass;
2287 alter table test_storage alter a set storage default; -- re-add TOAST table
2288 select reltoastrelid <> 0 as has_toast_table
2289 from pg_class where oid = 'test_storage'::regclass;
2295 -- check STORAGE correctness
2296 create table test_storage_failed (a text, b int storage extended);
2297 ERROR: column data type integer can only have storage PLAIN
2298 -- test that SET STORAGE propagates to index correctly
2299 create index test_storage_idx on test_storage (b, a);
2300 alter table test_storage alter column a set storage external;
2302 Table "public.test_storage"
2303 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2304 --------+---------+-----------+----------+-------------------+----------+--------------+-------------
2305 a | text | | | | external | |
2306 c | text | | | | plain | |
2307 b | integer | | | random()::integer | plain | |
2309 "test_storage_idx" btree (b, a)
2311 \d+ test_storage_idx
2312 Index "public.test_storage_idx"
2313 Column | Type | Key? | Definition | Storage | Stats target
2314 --------+---------+------+------------+----------+--------------
2315 b | integer | yes | b | plain |
2316 a | text | yes | a | external |
2317 btree, for table "public.test_storage"
2319 -- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
2320 CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
2321 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
2323 Table "public.test_inh_check"
2324 Column | Type | Collation | Nullable | Default
2325 --------+------------------+-----------+----------+---------
2326 a | double precision | | |
2327 b | double precision | | |
2329 "test_inh_check_a_check" CHECK (a > 10.2::double precision)
2330 Number of child tables: 1 (Use \d+ to list them.)
2332 \d test_inh_check_child
2333 Table "public.test_inh_check_child"
2334 Column | Type | Collation | Nullable | Default
2335 --------+------------------+-----------+----------+---------
2336 a | double precision | | |
2337 b | double precision | | |
2339 "test_inh_check_a_check" CHECK (a > 10.2::double precision)
2340 Inherits: test_inh_check
2342 select relname, conname, coninhcount, conislocal, connoinherit
2343 from pg_constraint c, pg_class r
2344 where relname like 'test_inh_check%' and c.conrelid = r.oid
2346 relname | conname | coninhcount | conislocal | connoinherit
2347 ----------------------+------------------------+-------------+------------+--------------
2348 test_inh_check | test_inh_check_a_check | 0 | t | f
2349 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2352 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
2354 Table "public.test_inh_check"
2355 Column | Type | Collation | Nullable | Default
2356 --------+------------------+-----------+----------+---------
2358 b | double precision | | |
2360 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2361 Number of child tables: 1 (Use \d+ to list them.)
2363 \d test_inh_check_child
2364 Table "public.test_inh_check_child"
2365 Column | Type | Collation | Nullable | Default
2366 --------+------------------+-----------+----------+---------
2368 b | double precision | | |
2370 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2371 Inherits: test_inh_check
2373 select relname, conname, coninhcount, conislocal, connoinherit
2374 from pg_constraint c, pg_class r
2375 where relname like 'test_inh_check%' and c.conrelid = r.oid
2377 relname | conname | coninhcount | conislocal | connoinherit
2378 ----------------------+------------------------+-------------+------------+--------------
2379 test_inh_check | test_inh_check_a_check | 0 | t | f
2380 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2383 -- also try noinherit, local, and local+inherited cases
2384 ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT;
2385 ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000);
2386 ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1);
2387 ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1);
2388 NOTICE: merging constraint "bmerged" with inherited definition
2390 Table "public.test_inh_check"
2391 Column | Type | Collation | Nullable | Default
2392 --------+------------------+-----------+----------+---------
2394 b | double precision | | |
2396 "bmerged" CHECK (b > 1::double precision)
2397 "bnoinherit" CHECK (b > 100::double precision) NO INHERIT
2398 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2399 Number of child tables: 1 (Use \d+ to list them.)
2401 \d test_inh_check_child
2402 Table "public.test_inh_check_child"
2403 Column | Type | Collation | Nullable | Default
2404 --------+------------------+-----------+----------+---------
2406 b | double precision | | |
2408 "blocal" CHECK (b < 1000::double precision)
2409 "bmerged" CHECK (b > 1::double precision)
2410 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2411 Inherits: test_inh_check
2413 select relname, conname, coninhcount, conislocal, connoinherit
2414 from pg_constraint c, pg_class r
2415 where relname like 'test_inh_check%' and c.conrelid = r.oid
2417 relname | conname | coninhcount | conislocal | connoinherit
2418 ----------------------+------------------------+-------------+------------+--------------
2419 test_inh_check | bmerged | 0 | t | f
2420 test_inh_check | bnoinherit | 0 | t | t
2421 test_inh_check | test_inh_check_a_check | 0 | t | f
2422 test_inh_check_child | blocal | 0 | t | f
2423 test_inh_check_child | bmerged | 1 | t | f
2424 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2427 ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric;
2428 NOTICE: merging constraint "bmerged" with inherited definition
2430 Table "public.test_inh_check"
2431 Column | Type | Collation | Nullable | Default
2432 --------+---------+-----------+----------+---------
2436 "bmerged" CHECK (b::double precision > 1::double precision)
2437 "bnoinherit" CHECK (b::double precision > 100::double precision) NO INHERIT
2438 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2439 Number of child tables: 1 (Use \d+ to list them.)
2441 \d test_inh_check_child
2442 Table "public.test_inh_check_child"
2443 Column | Type | Collation | Nullable | Default
2444 --------+---------+-----------+----------+---------
2448 "blocal" CHECK (b::double precision < 1000::double precision)
2449 "bmerged" CHECK (b::double precision > 1::double precision)
2450 "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
2451 Inherits: test_inh_check
2453 select relname, conname, coninhcount, conislocal, connoinherit
2454 from pg_constraint c, pg_class r
2455 where relname like 'test_inh_check%' and c.conrelid = r.oid
2457 relname | conname | coninhcount | conislocal | connoinherit
2458 ----------------------+------------------------+-------------+------------+--------------
2459 test_inh_check | bmerged | 0 | t | f
2460 test_inh_check | bnoinherit | 0 | t | t
2461 test_inh_check | test_inh_check_a_check | 0 | t | f
2462 test_inh_check_child | blocal | 0 | t | f
2463 test_inh_check_child | bmerged | 1 | t | f
2464 test_inh_check_child | test_inh_check_a_check | 1 | f | f
2467 -- ALTER COLUMN TYPE with different schema in children
2468 -- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com
2469 CREATE TABLE test_type_diff (f1 int);
2470 CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff);
2471 ALTER TABLE test_type_diff ADD COLUMN f2 int;
2472 INSERT INTO test_type_diff_c VALUES (1, 2, 3);
2473 ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint;
2474 CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8);
2475 CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2);
2476 CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4);
2477 CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8);
2478 ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2;
2479 ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2;
2480 ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2;
2481 INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3);
2482 INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6);
2483 INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9);
2484 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8;
2485 -- whole-row references are disallowed
2486 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2));
2487 ERROR: cannot convert whole-row table reference
2488 DETAIL: USING expression contains a whole-row table reference.
2489 -- check for rollback of ANALYZE corrupting table property flags (bug #11638)
2490 CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text);
2491 CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text);
2493 ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey;
2494 ANALYZE check_fk_presence_2;
2496 \d check_fk_presence_2
2497 Table "public.check_fk_presence_2"
2498 Column | Type | Collation | Nullable | Default
2499 --------+---------+-----------+----------+---------
2502 Foreign-key constraints:
2503 "check_fk_presence_2_id_fkey" FOREIGN KEY (id) REFERENCES check_fk_presence_1(id)
2505 DROP TABLE check_fk_presence_1, check_fk_presence_2;
2506 -- check column addition within a view (bug #14876)
2507 create table at_base_table(id int, stuff text);
2508 insert into at_base_table values (23, 'skidoo');
2509 create view at_view_1 as select * from at_base_table bt;
2510 create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1;
2512 View "public.at_view_1"
2513 Column | Type | Collation | Nullable | Default | Storage | Description
2514 --------+---------+-----------+----------+---------+----------+-------------
2515 id | integer | | | | plain |
2516 stuff | text | | | | extended |
2520 FROM at_base_table bt;
2523 View "public.at_view_2"
2524 Column | Type | Collation | Nullable | Default | Storage | Description
2525 --------+---------+-----------+----------+---------+----------+-------------
2526 id | integer | | | | plain |
2527 stuff | text | | | | extended |
2528 j | json | | | | extended |
2535 explain (verbose, costs off) select * from at_view_2;
2537 ----------------------------------------------------------
2538 Seq Scan on public.at_base_table bt
2539 Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff))
2542 select * from at_view_2;
2544 ----+--------+----------------------------
2545 23 | skidoo | {"id":23,"stuff":"skidoo"}
2548 create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt;
2550 View "public.at_view_1"
2551 Column | Type | Collation | Nullable | Default | Storage | Description
2552 --------+---------+-----------+----------+---------+----------+-------------
2553 id | integer | | | | plain |
2554 stuff | text | | | | extended |
2555 more | integer | | | | plain |
2560 FROM at_base_table bt;
2563 View "public.at_view_2"
2564 Column | Type | Collation | Nullable | Default | Storage | Description
2565 --------+---------+-----------+----------+---------+----------+-------------
2566 id | integer | | | | plain |
2567 stuff | text | | | | extended |
2568 j | json | | | | extended |
2575 explain (verbose, costs off) select * from at_view_2;
2577 -------------------------------------------------------------
2578 Seq Scan on public.at_base_table bt
2579 Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff, 4))
2582 select * from at_view_2;
2584 ----+--------+-------------------------------------
2585 23 | skidoo | {"id":23,"stuff":"skidoo","more":4}
2588 drop view at_view_2;
2589 drop view at_view_1;
2590 drop table at_base_table;
2591 -- related case (bug #17811)
2593 create temp table t1 as select * from int8_tbl;
2594 create temp view v1 as select 1::int8 as q1;
2595 create temp view v2 as select * from v1;
2596 create or replace temp view v1 with (security_barrier = true)
2597 as select * from t1;
2598 create temp table log (q1 int8, q2 int8);
2599 create rule v1_upd_rule as on update to v1
2600 do also insert into log values (new.*);
2601 update v2 set q1 = q1 + 1 where q1 = 123;
2604 ------------------+-------------------
2605 4567890123456789 | 123
2606 4567890123456789 | 4567890123456789
2607 4567890123456789 | -4567890123456789
2609 124 | 4567890123456789
2614 -----+------------------
2616 124 | 4567890123456789
2620 -- check adding a column not itself requiring a rewrite, together with
2621 -- a column requiring a default (bug #16038)
2622 -- ensure that rewrites aren't silently optimized away, removing the
2623 -- value of the test
2624 CREATE FUNCTION check_ddl_rewrite(p_tablename regclass, p_ddl text)
2626 LANGUAGE plpgsql AS $$
2630 v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename;
2634 RETURN v_relfilenode <> (SELECT relfilenode FROM pg_class WHERE oid = p_tablename);
2637 CREATE TABLE rewrite_test(col text);
2638 INSERT INTO rewrite_test VALUES ('something');
2639 INSERT INTO rewrite_test VALUES (NULL);
2640 -- empty[12] don't need rewrite, but notempty[12]_rewrite will force one
2641 SELECT check_ddl_rewrite('rewrite_test', $$
2642 ALTER TABLE rewrite_test
2643 ADD COLUMN empty1 text,
2644 ADD COLUMN notempty1_rewrite serial;
2651 SELECT check_ddl_rewrite('rewrite_test', $$
2652 ALTER TABLE rewrite_test
2653 ADD COLUMN notempty2_rewrite serial,
2654 ADD COLUMN empty2 text;
2661 -- also check that fast defaults cause no problem, first without rewrite
2662 SELECT check_ddl_rewrite('rewrite_test', $$
2663 ALTER TABLE rewrite_test
2664 ADD COLUMN empty3 text,
2665 ADD COLUMN notempty3_norewrite int default 42;
2672 SELECT check_ddl_rewrite('rewrite_test', $$
2673 ALTER TABLE rewrite_test
2674 ADD COLUMN notempty4_norewrite int default 42,
2675 ADD COLUMN empty4 text;
2682 -- then with rewrite
2683 SELECT check_ddl_rewrite('rewrite_test', $$
2684 ALTER TABLE rewrite_test
2685 ADD COLUMN empty5 text,
2686 ADD COLUMN notempty5_norewrite int default 42,
2687 ADD COLUMN notempty5_rewrite serial;
2694 SELECT check_ddl_rewrite('rewrite_test', $$
2695 ALTER TABLE rewrite_test
2696 ADD COLUMN notempty6_rewrite serial,
2697 ADD COLUMN empty6 text,
2698 ADD COLUMN notempty6_norewrite int default 42;
2706 DROP FUNCTION check_ddl_rewrite(regclass, text);
2707 DROP TABLE rewrite_test;
2711 drop type lockmodes;
2712 ERROR: type "lockmodes" does not exist
2713 create type lockmodes as enum (
2718 ,'ShareUpdateExclusiveLock'
2720 ,'ShareRowExclusiveLock'
2722 ,'AccessExclusiveLock'
2725 ERROR: view "my_locks" does not exist
2726 create or replace view my_locks as
2727 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
2728 from pg_locks l join pg_class c on l.relation = c.oid
2729 where virtualtransaction = (
2730 select virtualtransaction
2732 where transactionid = pg_current_xact_id()::xid)
2733 and locktype = 'relation'
2734 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
2735 and c.relname != 'my_locks'
2737 create table alterlock (f1 int primary key, f2 text);
2738 insert into alterlock values (1, 'foo');
2739 create table alterlock2 (f3 int primary key, f1 int);
2740 insert into alterlock2 values (1, 1);
2741 begin; alter table alterlock alter column f2 set statistics 150;
2742 select * from my_locks order by 1;
2743 relname | max_lockmode
2744 -----------+--------------------------
2745 alterlock | ShareUpdateExclusiveLock
2749 begin; alter table alterlock cluster on alterlock_pkey;
2750 select * from my_locks order by 1;
2751 relname | max_lockmode
2752 ----------------+--------------------------
2753 alterlock | ShareUpdateExclusiveLock
2754 alterlock_pkey | ShareUpdateExclusiveLock
2758 begin; alter table alterlock set without cluster;
2759 select * from my_locks order by 1;
2760 relname | max_lockmode
2761 -----------+--------------------------
2762 alterlock | ShareUpdateExclusiveLock
2766 begin; alter table alterlock set (fillfactor = 100);
2767 select * from my_locks order by 1;
2768 relname | max_lockmode
2769 -----------+--------------------------
2770 alterlock | ShareUpdateExclusiveLock
2771 pg_toast | ShareUpdateExclusiveLock
2775 begin; alter table alterlock reset (fillfactor);
2776 select * from my_locks order by 1;
2777 relname | max_lockmode
2778 -----------+--------------------------
2779 alterlock | ShareUpdateExclusiveLock
2780 pg_toast | ShareUpdateExclusiveLock
2784 begin; alter table alterlock set (toast.autovacuum_enabled = off);
2785 select * from my_locks order by 1;
2786 relname | max_lockmode
2787 -----------+--------------------------
2788 alterlock | ShareUpdateExclusiveLock
2789 pg_toast | ShareUpdateExclusiveLock
2793 begin; alter table alterlock set (autovacuum_enabled = off);
2794 select * from my_locks order by 1;
2795 relname | max_lockmode
2796 -----------+--------------------------
2797 alterlock | ShareUpdateExclusiveLock
2798 pg_toast | ShareUpdateExclusiveLock
2802 begin; alter table alterlock alter column f2 set (n_distinct = 1);
2803 select * from my_locks order by 1;
2804 relname | max_lockmode
2805 -----------+--------------------------
2806 alterlock | ShareUpdateExclusiveLock
2810 -- test that mixing options with different lock levels works as expected
2811 begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80);
2812 select * from my_locks order by 1;
2813 relname | max_lockmode
2814 -----------+--------------------------
2815 alterlock | ShareUpdateExclusiveLock
2816 pg_toast | ShareUpdateExclusiveLock
2820 begin; alter table alterlock alter column f2 set storage extended;
2821 select * from my_locks order by 1;
2822 relname | max_lockmode
2823 -----------+---------------------
2824 alterlock | AccessExclusiveLock
2828 begin; alter table alterlock alter column f2 set default 'x';
2829 select * from my_locks order by 1;
2830 relname | max_lockmode
2831 -----------+---------------------
2832 alterlock | AccessExclusiveLock
2837 create trigger ttdummy
2838 before delete or update on alterlock
2842 select * from my_locks order by 1;
2843 relname | max_lockmode
2844 -----------+-----------------------
2845 alterlock | ShareRowExclusiveLock
2850 select * from my_locks order by 1;
2851 relname | max_lockmode
2852 ---------+--------------
2855 alter table alterlock2 add foreign key (f1) references alterlock (f1);
2856 select * from my_locks order by 1;
2857 relname | max_lockmode
2858 -----------------+-----------------------
2859 alterlock | ShareRowExclusiveLock
2860 alterlock2 | ShareRowExclusiveLock
2861 alterlock2_pkey | AccessShareLock
2862 alterlock_pkey | AccessShareLock
2867 alter table alterlock2
2868 add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID;
2869 select * from my_locks order by 1;
2870 relname | max_lockmode
2871 ------------+-----------------------
2872 alterlock | ShareRowExclusiveLock
2873 alterlock2 | ShareRowExclusiveLock
2878 alter table alterlock2 validate constraint alterlock2nv;
2879 select * from my_locks order by 1;
2880 relname | max_lockmode
2881 -----------------+--------------------------
2882 alterlock | RowShareLock
2883 alterlock2 | ShareUpdateExclusiveLock
2884 alterlock2_pkey | AccessShareLock
2885 alterlock_pkey | AccessShareLock
2889 create or replace view my_locks as
2890 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
2891 from pg_locks l join pg_class c on l.relation = c.oid
2892 where virtualtransaction = (
2893 select virtualtransaction
2895 where transactionid = pg_current_xact_id()::xid)
2896 and locktype = 'relation'
2897 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
2898 and c.relname = 'my_locks'
2901 alter table my_locks set (autovacuum_enabled = false);
2902 ERROR: unrecognized parameter "autovacuum_enabled"
2903 alter view my_locks set (autovacuum_enabled = false);
2904 ERROR: unrecognized parameter "autovacuum_enabled"
2905 alter table my_locks reset (autovacuum_enabled);
2906 alter view my_locks reset (autovacuum_enabled);
2908 alter view my_locks set (security_barrier=off);
2909 select * from my_locks order by 1;
2910 relname | max_lockmode
2911 ----------+---------------------
2912 my_locks | AccessExclusiveLock
2915 alter view my_locks reset (security_barrier);
2917 -- this test intentionally applies the ALTER TABLE command against a view, but
2918 -- uses a view option so we expect this to succeed. This form of SQL is
2919 -- accepted for historical reasons, as shown in the docs for ALTER VIEW
2921 alter table my_locks set (security_barrier=off);
2922 select * from my_locks order by 1;
2923 relname | max_lockmode
2924 ----------+---------------------
2925 my_locks | AccessExclusiveLock
2928 alter table my_locks reset (security_barrier);
2931 drop table alterlock2;
2932 drop table alterlock;
2934 drop type lockmodes;
2938 create function test_strict(text) returns text as
2939 'select coalesce($1, ''got passed a null'');'
2940 language sql returns null on null input;
2941 select test_strict(NULL);
2947 alter function test_strict(text) called on null input;
2948 select test_strict(NULL);
2954 create function non_strict(text) returns text as
2955 'select coalesce($1, ''got passed a null'');'
2956 language sql called on null input;
2957 select non_strict(NULL);
2963 alter function non_strict(text) returns null on null input;
2964 select non_strict(NULL);
2971 -- alter object set schema
2973 create schema alter1;
2974 create schema alter2;
2975 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
2976 create view alter1.v1 as select * from alter1.t1;
2977 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
2978 create domain alter1.posint integer check (value > 0);
2979 create type alter1.ctype as (f1 int, f2 text);
2980 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
2981 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
2982 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
2983 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
2984 operator 1 alter1.=(alter1.ctype, alter1.ctype);
2985 create conversion alter1.latin1_to_utf8 for 'latin1' to 'utf8' from iso8859_1_to_utf8;
2986 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
2987 create text search configuration alter1.cfg(parser = alter1.prs);
2988 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
2989 create text search dictionary alter1.dict(template = alter1.tmpl);
2990 insert into alter1.t1(f2) values(11);
2991 insert into alter1.t1(f2) values(12);
2992 alter table alter1.t1 set schema alter1; -- no-op, same schema
2993 alter table alter1.t1 set schema alter2;
2994 alter table alter1.v1 set schema alter2;
2995 alter function alter1.plus1(int) set schema alter2;
2996 alter domain alter1.posint set schema alter2;
2997 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
2998 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
2999 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
3000 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
3001 alter type alter1.ctype set schema alter1; -- no-op, same schema
3002 alter type alter1.ctype set schema alter2;
3003 alter conversion alter1.latin1_to_utf8 set schema alter2;
3004 alter text search parser alter1.prs set schema alter2;
3005 alter text search configuration alter1.cfg set schema alter2;
3006 alter text search template alter1.tmpl set schema alter2;
3007 alter text search dictionary alter1.dict set schema alter2;
3008 -- this should succeed because nothing is left in alter1
3010 insert into alter2.t1(f2) values(13);
3011 insert into alter2.t1(f2) values(14);
3012 select * from alter2.t1;
3021 select * from alter2.v1;
3030 select alter2.plus1(41);
3037 drop schema alter2 cascade;
3038 NOTICE: drop cascades to 13 other objects
3039 DETAIL: drop cascades to table alter2.t1
3040 drop cascades to view alter2.v1
3041 drop cascades to function alter2.plus1(integer)
3042 drop cascades to type alter2.posint
3043 drop cascades to type alter2.ctype
3044 drop cascades to function alter2.same(alter2.ctype,alter2.ctype)
3045 drop cascades to operator alter2.=(alter2.ctype,alter2.ctype)
3046 drop cascades to operator family alter2.ctype_hash_ops for access method hash
3047 drop cascades to conversion alter2.latin1_to_utf8
3048 drop cascades to text search parser alter2.prs
3049 drop cascades to text search configuration alter2.cfg
3050 drop cascades to text search template alter2.tmpl
3051 drop cascades to text search dictionary alter2.dict
3055 CREATE TYPE test_type AS (a int);
3057 Composite type "public.test_type"
3058 Column | Type | Collation | Nullable | Default
3059 --------+---------+-----------+----------+---------
3062 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
3063 ERROR: relation "nosuchtype" does not exist
3064 ALTER TYPE test_type ADD ATTRIBUTE b text;
3066 Composite type "public.test_type"
3067 Column | Type | Collation | Nullable | Default
3068 --------+---------+-----------+----------+---------
3072 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
3073 ERROR: column "b" of relation "test_type" already exists
3074 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
3076 Composite type "public.test_type"
3077 Column | Type | Collation | Nullable | Default
3078 --------+-------------------+-----------+----------+---------
3080 b | character varying | | |
3082 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
3084 Composite type "public.test_type"
3085 Column | Type | Collation | Nullable | Default
3086 --------+---------+-----------+----------+---------
3090 ALTER TYPE test_type DROP ATTRIBUTE b;
3092 Composite type "public.test_type"
3093 Column | Type | Collation | Nullable | Default
3094 --------+---------+-----------+----------+---------
3097 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
3098 ERROR: column "c" of relation "test_type" does not exist
3099 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
3100 NOTICE: column "c" of relation "test_type" does not exist, skipping
3101 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
3103 Composite type "public.test_type"
3104 Column | Type | Collation | Nullable | Default
3105 --------+---------+-----------+----------+---------
3108 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
3109 ERROR: column "a" does not exist
3110 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
3112 Composite type "public.test_type"
3113 Column | Type | Collation | Nullable | Default
3114 --------+---------+-----------+----------+---------
3117 DROP TYPE test_type;
3118 CREATE TYPE test_type1 AS (a int, b text);
3119 CREATE TABLE test_tbl1 (x int, y test_type1);
3120 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
3121 ERROR: cannot alter type "test_type1" because column "test_tbl1.y" uses it
3122 DROP TABLE test_tbl1;
3123 CREATE TABLE test_tbl1 (x int, y text);
3124 CREATE INDEX test_tbl1_idx ON test_tbl1((row(x,y)::test_type1));
3125 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
3126 ERROR: cannot alter type "test_type1" because column "test_tbl1_idx.row" uses it
3127 DROP TABLE test_tbl1;
3128 DROP TYPE test_type1;
3129 CREATE TYPE test_type2 AS (a int, b text);
3130 CREATE TABLE test_tbl2 OF test_type2;
3131 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
3133 Composite type "public.test_type2"
3134 Column | Type | Collation | Nullable | Default
3135 --------+---------+-----------+----------+---------
3140 Table "public.test_tbl2"
3141 Column | Type | Collation | Nullable | Default
3142 --------+---------+-----------+----------+---------
3145 Number of child tables: 1 (Use \d+ to list them.)
3146 Typed table of type: test_type2
3148 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
3149 ERROR: cannot alter type "test_type2" because it is the type of a typed table
3150 HINT: Use ALTER ... CASCADE to alter the typed tables too.
3151 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
3153 Composite type "public.test_type2"
3154 Column | Type | Collation | Nullable | Default
3155 --------+---------+-----------+----------+---------
3161 Table "public.test_tbl2"
3162 Column | Type | Collation | Nullable | Default
3163 --------+---------+-----------+----------+---------
3167 Number of child tables: 1 (Use \d+ to list them.)
3168 Typed table of type: test_type2
3170 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
3171 ERROR: cannot alter type "test_type2" because it is the type of a typed table
3172 HINT: Use ALTER ... CASCADE to alter the typed tables too.
3173 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
3175 Composite type "public.test_type2"
3176 Column | Type | Collation | Nullable | Default
3177 --------+-------------------+-----------+----------+---------
3179 b | character varying | | |
3183 Table "public.test_tbl2"
3184 Column | Type | Collation | Nullable | Default
3185 --------+-------------------+-----------+----------+---------
3187 b | character varying | | |
3189 Number of child tables: 1 (Use \d+ to list them.)
3190 Typed table of type: test_type2
3192 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
3193 ERROR: cannot alter type "test_type2" because it is the type of a typed table
3194 HINT: Use ALTER ... CASCADE to alter the typed tables too.
3195 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
3197 Composite type "public.test_type2"
3198 Column | Type | Collation | Nullable | Default
3199 --------+---------+-----------+----------+---------
3204 Table "public.test_tbl2"
3205 Column | Type | Collation | Nullable | Default
3206 --------+---------+-----------+----------+---------
3209 Number of child tables: 1 (Use \d+ to list them.)
3210 Typed table of type: test_type2
3212 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
3213 ERROR: cannot alter type "test_type2" because it is the type of a typed table
3214 HINT: Use ALTER ... CASCADE to alter the typed tables too.
3215 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
3217 Composite type "public.test_type2"
3218 Column | Type | Collation | Nullable | Default
3219 --------+---------+-----------+----------+---------
3224 Table "public.test_tbl2"
3225 Column | Type | Collation | Nullable | Default
3226 --------+---------+-----------+----------+---------
3229 Number of child tables: 1 (Use \d+ to list them.)
3230 Typed table of type: test_type2
3232 \d test_tbl2_subclass
3233 Table "public.test_tbl2_subclass"
3234 Column | Type | Collation | Nullable | Default
3235 --------+---------+-----------+----------+---------
3240 DROP TABLE test_tbl2_subclass, test_tbl2;
3241 DROP TYPE test_type2;
3242 CREATE TYPE test_typex AS (a int, b text);
3243 CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0));
3244 ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails
3245 ERROR: cannot drop column a of composite type test_typex because other objects depend on it
3246 DETAIL: constraint test_tblx_y_check on table test_tblx depends on column a of composite type test_typex
3247 HINT: Use DROP ... CASCADE to drop the dependent objects too.
3248 ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE;
3249 NOTICE: drop cascades to constraint test_tblx_y_check on table test_tblx
3251 Table "public.test_tblx"
3252 Column | Type | Collation | Nullable | Default
3253 --------+------------+-----------+----------+---------
3255 y | test_typex | | |
3257 DROP TABLE test_tblx;
3258 DROP TYPE test_typex;
3259 -- This test isn't that interesting on its own, but the purpose is to leave
3260 -- behind a table to test pg_upgrade with. The table has a composite type
3261 -- column in it, and the composite type has a dropped attribute.
3262 CREATE TYPE test_type3 AS (a int);
3263 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
3264 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
3265 CREATE TYPE test_type_empty AS ();
3266 DROP TYPE test_type_empty;
3268 -- typed tables: OF / NOT OF
3270 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
3271 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
3272 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
3273 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
3274 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
3275 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
3276 CREATE TABLE tt4 (x int); -- too few columns
3277 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
3278 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
3279 CREATE TABLE tt7 (x int, q text, y numeric(8,2));
3280 ALTER TABLE tt7 DROP q; -- OK
3281 ALTER TABLE tt0 OF tt_t0;
3282 ALTER TABLE tt1 OF tt_t0;
3283 ERROR: table "tt1" has different type for column "y"
3284 ALTER TABLE tt2 OF tt_t0;
3285 ERROR: table "tt2" has different type for column "y"
3286 ALTER TABLE tt3 OF tt_t0;
3287 ERROR: table has column "y" where type requires "x"
3288 ALTER TABLE tt4 OF tt_t0;
3289 ERROR: table is missing column "y"
3290 ALTER TABLE tt5 OF tt_t0;
3291 ERROR: table has extra column "z"
3292 ALTER TABLE tt6 OF tt_t0;
3293 ERROR: typed tables cannot inherit
3294 ALTER TABLE tt7 OF tt_t0;
3295 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
3296 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
3297 ALTER TABLE tt7 NOT OF;
3300 Column | Type | Collation | Nullable | Default
3301 --------+--------------+-----------+----------+---------
3303 y | numeric(8,2) | | |
3305 -- make sure we can drop a constraint on the parent but it remains on the child
3306 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
3307 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
3308 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
3310 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
3311 ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
3312 DETAIL: Failing row contains (null).
3313 DROP TABLE test_drop_constr_parent CASCADE;
3314 NOTICE: drop cascades to table test_drop_constr_child
3318 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
3319 NOTICE: relation "tt8" does not exist, skipping
3320 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
3321 NOTICE: relation "tt8" does not exist, skipping
3322 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
3323 NOTICE: relation "tt8" does not exist, skipping
3324 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
3325 NOTICE: relation "tt8" does not exist, skipping
3326 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
3327 NOTICE: relation "tt8" does not exist, skipping
3328 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
3329 NOTICE: relation "tt8" does not exist, skipping
3330 CREATE TABLE tt8(a int);
3331 CREATE SCHEMA alter2;
3332 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
3333 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
3334 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
3335 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
3336 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
3337 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
3340 Column | Type | Collation | Nullable | Default
3341 --------+---------+-----------+----------+---------
3343 f1 | integer | | not null | 0
3345 "xxx" PRIMARY KEY, btree (f1)
3347 "tt8_f_check" CHECK (f1 >= 0 AND f1 <= 10)
3349 DROP TABLE alter2.tt8;
3352 -- Check conflicts between index and CHECK constraint names
3354 CREATE TABLE tt9(c integer);
3355 ALTER TABLE tt9 ADD CHECK(c > 1);
3356 ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name
3357 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
3358 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name
3359 ERROR: constraint "foo" for relation "tt9" already exists
3360 ALTER TABLE tt9 ADD UNIQUE(c);
3361 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
3362 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name
3363 ERROR: relation "tt9_c_key" already exists
3364 ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name
3365 ERROR: constraint "foo" for relation "tt9" already exists
3366 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name
3367 ERROR: constraint "tt9_c_key" for relation "tt9" already exists
3368 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
3369 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
3372 Column | Type | Collation | Nullable | Default
3373 --------+---------+-----------+----------+---------
3376 "tt9_c_key" UNIQUE CONSTRAINT, btree (c)
3377 "tt9_c_key1" UNIQUE CONSTRAINT, btree (c)
3378 "tt9_c_key3" UNIQUE CONSTRAINT, btree (c)
3381 "tt9_c_check" CHECK (c > 1)
3382 "tt9_c_check1" CHECK (c > 2)
3383 "tt9_c_key2" CHECK (c > 6)
3386 -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
3387 CREATE TABLE comment_test (
3389 positive_col int CHECK (positive_col > 0),
3391 CONSTRAINT comment_test_pk PRIMARY KEY (id));
3392 CREATE INDEX comment_test_index ON comment_test(indexed_col);
3393 COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
3394 COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
3395 COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
3396 COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
3397 COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
3398 SELECT col_description('comment_test'::regclass, 1) as comment;
3400 -----------------------------
3401 Column 'id' on comment_test
3404 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;
3406 --------------------+-----------------------------------------------
3407 comment_test_index | Simple index on comment_test
3408 comment_test_pk | Index backing the PRIMARY KEY of comment_test
3411 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
3412 constraint | comment
3413 ---------------------------------+-----------------------------------------------
3414 comment_test_pk | PRIMARY KEY constraint of comment_test
3415 comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
3418 -- Change the datatype of all the columns. ALTER TABLE is optimized to not
3419 -- rebuild an index if the new data type is binary compatible with the old
3420 -- one. Check do a dummy ALTER TABLE that doesn't change the datatype
3421 -- first, to test that no-op codepath, and another one that does.
3422 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int;
3423 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text;
3424 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int;
3425 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
3426 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int;
3427 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint;
3428 -- Check that the comments are intact.
3429 SELECT col_description('comment_test'::regclass, 1) as comment;
3431 -----------------------------
3432 Column 'id' on comment_test
3435 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;
3437 --------------------+-----------------------------------------------
3438 comment_test_index | Simple index on comment_test
3439 comment_test_pk | Index backing the PRIMARY KEY of comment_test
3442 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
3443 constraint | comment
3444 ---------------------------------+-----------------------------------------------
3445 comment_test_pk | PRIMARY KEY constraint of comment_test
3446 comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
3449 -- Check compatibility for foreign keys and comments. This is done
3450 -- separately as rebuilding the column type of the parent leads
3451 -- to an error and would reduce the test scope.
3452 CREATE TABLE comment_test_child (
3453 id text CONSTRAINT comment_test_child_fk REFERENCES comment_test);
3454 CREATE INDEX comment_test_child_fk ON comment_test_child(id);
3455 COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child';
3456 COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child';
3457 COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child';
3458 -- Change column type of parent
3459 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
3460 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer;
3461 ERROR: foreign key constraint "comment_test_child_fk" cannot be implemented
3462 DETAIL: Key columns "id" and "id" are of incompatible types: text and integer.
3463 -- Comments should be intact
3464 SELECT col_description('comment_test_child'::regclass, 1) as comment;
3466 -----------------------------------
3467 Column 'id' on comment_test_child
3470 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;
3472 -----------------------+-----------------------------------------------------
3473 comment_test_child_fk | Index backing the FOREIGN KEY of comment_test_child
3476 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2;
3477 constraint | comment
3478 -----------------------+----------------------------------------------
3479 comment_test_child_fk | FOREIGN KEY constraint of comment_test_child
3482 -- Check that we map relation oids to filenodes and back correctly. Only
3483 -- display bad mappings so the test output doesn't change all the time. A
3484 -- filenode function call can return NULL for a relation dropped concurrently
3485 -- with the call's surrounding query, so ignore a NULL mapped_oid for
3486 -- relations that no longer exist after all calls finish.
3487 CREATE TEMP TABLE filenode_mapping AS
3489 oid, mapped_oid, reltablespace, relfilenode, relname
3491 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid
3492 WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid;
3493 SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid
3494 WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
3495 oid | mapped_oid | reltablespace | relfilenode | relname
3496 -----+------------+---------------+-------------+---------
3499 -- Checks on creating and manipulation of user defined relations in
3501 SHOW allow_system_table_mods;
3502 allow_system_table_mods
3503 -------------------------
3507 -- disallowed because of search_path issues with pg_dump
3508 CREATE TABLE pg_catalog.new_system_table();
3509 ERROR: permission denied to create "pg_catalog.new_system_table"
3510 DETAIL: System catalog modifications are currently disallowed.
3511 -- instead create in public first, move to catalog
3512 CREATE TABLE new_system_table(id serial primary key, othercol text);
3513 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
3514 ALTER TABLE new_system_table SET SCHEMA public;
3515 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
3516 -- will be ignored -- already there:
3517 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
3518 ALTER TABLE new_system_table RENAME TO old_system_table;
3519 CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
3520 INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
3521 UPDATE old_system_table SET id = -id;
3522 DELETE FROM old_system_table WHERE othercol = 'somedata';
3523 TRUNCATE old_system_table;
3524 ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
3525 ALTER TABLE old_system_table DROP COLUMN othercol;
3526 DROP TABLE old_system_table;
3528 CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT); -- has sequence, toast
3529 -- check relpersistence of an unlogged table
3530 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
3532 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
3534 SELECT r.relname || ' 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'
3536 relname | relkind | relpersistence
3537 -----------------------+---------+----------------
3539 unlogged1 toast index | i | u
3540 unlogged1 toast table | t | u
3541 unlogged1_f1_seq | S | u
3542 unlogged1_pkey | i | u
3545 CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
3546 CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
3547 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
3548 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
3549 ERROR: could not change table "unlogged2" to logged because it references unlogged table "unlogged1"
3550 ALTER TABLE unlogged1 SET LOGGED;
3551 -- check relpersistence of an unlogged table after changing to permanent
3552 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
3554 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
3556 SELECT r.relname || ' 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'
3558 relname | relkind | relpersistence
3559 -----------------------+---------+----------------
3561 unlogged1 toast index | i | p
3562 unlogged1 toast table | t | p
3563 unlogged1_f1_seq | S | p
3564 unlogged1_pkey | i | p
3567 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
3568 DROP TABLE unlogged3;
3569 DROP TABLE unlogged2;
3570 DROP TABLE unlogged1;
3572 CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT); -- has sequence, toast
3573 -- check relpersistence of a permanent table
3574 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
3576 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
3578 SELECT r.relname ||' 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'
3580 relname | relkind | relpersistence
3581 ---------------------+---------+----------------
3583 logged1 toast index | i | p
3584 logged1 toast table | t | p
3585 logged1_f1_seq | S | p
3586 logged1_pkey | i | p
3589 CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
3590 CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
3591 ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
3592 ERROR: could not change table "logged1" to unlogged because it references logged table "logged2"
3593 ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
3594 ALTER TABLE logged2 SET UNLOGGED;
3595 ALTER TABLE logged1 SET UNLOGGED;
3596 -- check relpersistence of a permanent table after changing to unlogged
3597 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
3599 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
3601 SELECT r.relname || ' 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'
3603 relname | relkind | relpersistence
3604 ---------------------+---------+----------------
3606 logged1 toast index | i | u
3607 logged1 toast table | t | u
3608 logged1_f1_seq | S | u
3609 logged1_pkey | i | u
3612 ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
3616 -- test ADD COLUMN IF NOT EXISTS
3617 CREATE TABLE test_add_column(c1 integer);
3619 Table "public.test_add_column"
3620 Column | Type | Collation | Nullable | Default
3621 --------+---------+-----------+----------+---------
3624 ALTER TABLE test_add_column
3625 ADD COLUMN c2 integer;
3627 Table "public.test_add_column"
3628 Column | Type | Collation | Nullable | Default
3629 --------+---------+-----------+----------+---------
3633 ALTER TABLE test_add_column
3634 ADD COLUMN c2 integer; -- fail because c2 already exists
3635 ERROR: column "c2" of relation "test_add_column" already exists
3636 ALTER TABLE ONLY test_add_column
3637 ADD COLUMN c2 integer; -- fail because c2 already exists
3638 ERROR: column "c2" of relation "test_add_column" already exists
3640 Table "public.test_add_column"
3641 Column | Type | Collation | Nullable | Default
3642 --------+---------+-----------+----------+---------
3646 ALTER TABLE test_add_column
3647 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
3648 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3649 ALTER TABLE ONLY test_add_column
3650 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
3651 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3653 Table "public.test_add_column"
3654 Column | Type | Collation | Nullable | Default
3655 --------+---------+-----------+----------+---------
3659 ALTER TABLE test_add_column
3660 ADD COLUMN c2 integer, -- fail because c2 already exists
3661 ADD COLUMN c3 integer primary key;
3662 ERROR: column "c2" of relation "test_add_column" already exists
3664 Table "public.test_add_column"
3665 Column | Type | Collation | Nullable | Default
3666 --------+---------+-----------+----------+---------
3670 ALTER TABLE test_add_column
3671 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
3672 ADD COLUMN c3 integer primary key;
3673 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3675 Table "public.test_add_column"
3676 Column | Type | Collation | Nullable | Default
3677 --------+---------+-----------+----------+---------
3680 c3 | integer | | not null |
3682 "test_add_column_pkey" PRIMARY KEY, btree (c3)
3684 ALTER TABLE test_add_column
3685 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
3686 ADD COLUMN IF NOT EXISTS c3 integer primary key; -- skipping because c3 already exists
3687 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3688 NOTICE: column "c3" of relation "test_add_column" already exists, skipping
3690 Table "public.test_add_column"
3691 Column | Type | Collation | Nullable | Default
3692 --------+---------+-----------+----------+---------
3695 c3 | integer | | not null |
3697 "test_add_column_pkey" PRIMARY KEY, btree (c3)
3699 ALTER TABLE test_add_column
3700 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
3701 ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists
3702 ADD COLUMN c4 integer REFERENCES test_add_column;
3703 NOTICE: column "c2" of relation "test_add_column" already exists, skipping
3704 NOTICE: column "c3" of relation "test_add_column" already exists, skipping
3706 Table "public.test_add_column"
3707 Column | Type | Collation | Nullable | Default
3708 --------+---------+-----------+----------+---------
3711 c3 | integer | | not null |
3714 "test_add_column_pkey" PRIMARY KEY, btree (c3)
3715 Foreign-key constraints:
3716 "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3718 TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3720 ALTER TABLE test_add_column
3721 ADD COLUMN IF NOT EXISTS c4 integer REFERENCES test_add_column;
3722 NOTICE: column "c4" of relation "test_add_column" already exists, skipping
3724 Table "public.test_add_column"
3725 Column | Type | Collation | Nullable | Default
3726 --------+---------+-----------+----------+---------
3729 c3 | integer | | not null |
3732 "test_add_column_pkey" PRIMARY KEY, btree (c3)
3733 Foreign-key constraints:
3734 "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3736 TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3738 ALTER TABLE test_add_column
3739 ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 8);
3741 Table "public.test_add_column"
3742 Column | Type | Collation | Nullable | Default
3743 --------+---------+-----------+----------+---------------------------------------------
3746 c3 | integer | | not null |
3748 c5 | integer | | not null | nextval('test_add_column_c5_seq'::regclass)
3750 "test_add_column_pkey" PRIMARY KEY, btree (c3)
3752 "test_add_column_c5_check" CHECK (c5 > 8)
3753 Foreign-key constraints:
3754 "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3756 TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3758 ALTER TABLE test_add_column
3759 ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 10);
3760 NOTICE: column "c5" of relation "test_add_column" already exists, skipping
3762 Table "public.test_add_column"
3763 Column | Type | Collation | Nullable | Default
3764 --------+---------+-----------+----------+---------------------------------------------
3767 c3 | integer | | not null |
3769 c5 | integer | | not null | nextval('test_add_column_c5_seq'::regclass)
3771 "test_add_column_pkey" PRIMARY KEY, btree (c3)
3773 "test_add_column_c5_check" CHECK (c5 > 8)
3774 Foreign-key constraints:
3775 "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3777 TABLE "test_add_column" CONSTRAINT "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
3779 Sequence "public.test_add_column_c5_seq"
3780 Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
3781 ---------+-------+---------+------------+-----------+---------+-------
3782 integer | 1 | 1 | 2147483647 | 1 | no | 1
3783 Owned by: public.test_add_column.c5
3785 Index "public.test_add_column_pkey"
3786 Column | Type | Key? | Definition
3787 --------+---------+------+------------
3788 c3 | integer | yes | c3
3789 primary key, btree, for table "public.test_add_column"
3791 DROP TABLE test_add_column;
3793 -- assorted cases with multiple ALTER TABLE steps
3794 CREATE TABLE ataddindex(f1 INT);
3795 INSERT INTO ataddindex VALUES (42), (43);
3796 CREATE UNIQUE INDEX ataddindexi0 ON ataddindex(f1);
3797 ALTER TABLE ataddindex
3798 ADD PRIMARY KEY USING INDEX ataddindexi0,
3799 ALTER f1 TYPE BIGINT;
3801 Table "public.ataddindex"
3802 Column | Type | Collation | Nullable | Default
3803 --------+--------+-----------+----------+---------
3804 f1 | bigint | | not null |
3806 "ataddindexi0" PRIMARY KEY, btree (f1)
3808 DROP TABLE ataddindex;
3809 CREATE TABLE ataddindex(f1 VARCHAR(10));
3810 INSERT INTO ataddindex(f1) VALUES ('foo'), ('a');
3811 ALTER TABLE ataddindex
3812 ALTER f1 SET DATA TYPE TEXT,
3813 ADD EXCLUDE ((f1 LIKE 'a') WITH =);
3815 Table "public.ataddindex"
3816 Column | Type | Collation | Nullable | Default
3817 --------+------+-----------+----------+---------
3820 "ataddindex_expr_excl" EXCLUDE USING btree ((f1 ~~ 'a'::text) WITH =)
3822 DROP TABLE ataddindex;
3823 CREATE TABLE ataddindex(id int, ref_id int);
3824 ALTER TABLE ataddindex
3825 ADD PRIMARY KEY (id),
3826 ADD FOREIGN KEY (ref_id) REFERENCES ataddindex;
3828 Table "public.ataddindex"
3829 Column | Type | Collation | Nullable | Default
3830 --------+---------+-----------+----------+---------
3831 id | integer | | not null |
3832 ref_id | integer | | |
3834 "ataddindex_pkey" PRIMARY KEY, btree (id)
3835 Foreign-key constraints:
3836 "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
3838 TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
3840 DROP TABLE ataddindex;
3841 CREATE TABLE ataddindex(id int, ref_id int);
3842 ALTER TABLE ataddindex
3844 ADD FOREIGN KEY (ref_id) REFERENCES ataddindex (id);
3846 Table "public.ataddindex"
3847 Column | Type | Collation | Nullable | Default
3848 --------+---------+-----------+----------+---------
3850 ref_id | integer | | |
3852 "ataddindex_id_key" UNIQUE CONSTRAINT, btree (id)
3853 Foreign-key constraints:
3854 "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
3856 TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
3858 DROP TABLE ataddindex;
3859 CREATE TABLE atnotnull1 ();
3860 ALTER TABLE atnotnull1
3862 ALTER a SET NOT NULL;
3863 ALTER TABLE atnotnull1
3865 ADD PRIMARY KEY (c);
3867 Table "public.atnotnull1"
3868 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3869 --------+---------+-----------+----------+---------+---------+--------------+-------------
3870 a | integer | | not null | | plain | |
3871 c | integer | | not null | | plain | |
3873 "atnotnull1_pkey" PRIMARY KEY, btree (c)
3875 -- cannot drop column that is part of the partition key
3876 CREATE TABLE partitioned (
3879 ) PARTITION BY RANGE (a, (a+b+1));
3880 ALTER TABLE partitioned DROP COLUMN a;
3881 ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
3882 ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
3883 ERROR: cannot alter column "a" because it is part of the partition key of relation "partitioned"
3884 ALTER TABLE partitioned DROP COLUMN b;
3885 ERROR: cannot drop column "b" because it is part of the partition key of relation "partitioned"
3886 ALTER TABLE partitioned ALTER COLUMN b TYPE char(5);
3887 ERROR: cannot alter column "b" because it is part of the partition key of relation "partitioned"
3888 -- specifying storage parameters for partitioned tables is not supported
3889 ALTER TABLE partitioned SET (fillfactor=100);
3890 ERROR: cannot specify storage parameters for a partitioned table
3891 HINT: Specify storage parameters for its leaf partitions instead.
3892 -- partitioned table cannot participate in regular inheritance
3893 CREATE TABLE nonpartitioned (
3897 ALTER TABLE partitioned INHERIT nonpartitioned;
3898 ERROR: cannot change inheritance of partitioned table
3899 ALTER TABLE nonpartitioned INHERIT partitioned;
3900 ERROR: cannot inherit from partitioned table "partitioned"
3901 -- cannot add NO INHERIT constraint to partitioned tables
3902 ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT;
3903 ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned"
3904 DROP TABLE partitioned, nonpartitioned;
3908 -- check that target table is partitioned
3909 CREATE TABLE unparted (
3912 CREATE TABLE fail_part (like unparted);
3913 ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a');
3914 ERROR: table "unparted" is not partitioned
3915 DROP TABLE unparted, fail_part;
3916 -- check that partition bound is compatible
3917 CREATE TABLE list_parted (
3919 b char(2) COLLATE "C",
3920 CONSTRAINT check_a CHECK (a > 0)
3921 ) PARTITION BY LIST (a);
3922 CREATE TABLE fail_part (LIKE list_parted);
3923 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10);
3924 ERROR: invalid bound specification for a list partition
3925 LINE 1: ...list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) T...
3927 DROP TABLE fail_part;
3928 -- check that the table being attached exists
3929 ALTER TABLE list_parted ATTACH PARTITION nonexistent FOR VALUES IN (1);
3930 ERROR: relation "nonexistent" does not exist
3931 -- check ownership of the source table
3932 CREATE ROLE regress_test_me;
3933 CREATE ROLE regress_test_not_me;
3934 CREATE TABLE not_owned_by_me (LIKE list_parted);
3935 ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me;
3936 SET SESSION AUTHORIZATION regress_test_me;
3937 CREATE TABLE owned_by_me (
3939 ) PARTITION BY LIST (a);
3940 ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1);
3941 ERROR: must be owner of table not_owned_by_me
3942 RESET SESSION AUTHORIZATION;
3943 DROP TABLE owned_by_me, not_owned_by_me;
3944 DROP ROLE regress_test_not_me;
3945 DROP ROLE regress_test_me;
3946 -- check that the table being attached is not part of regular inheritance
3947 CREATE TABLE parent (LIKE list_parted);
3948 CREATE TABLE child () INHERITS (parent);
3949 ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1);
3950 ERROR: cannot attach inheritance child as partition
3951 ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1);
3952 ERROR: cannot attach inheritance parent as partition
3954 -- now it should work, with a little tweak
3955 ALTER TABLE parent ADD CONSTRAINT check_a CHECK (a > 0);
3956 ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1);
3957 -- test insert/update, per bug #18550
3958 INSERT INTO parent VALUES (1);
3959 UPDATE parent SET a = 2 WHERE a = 1;
3960 ERROR: new row for relation "parent" violates partition constraint
3961 DETAIL: Failing row contains (2, null).
3962 DROP TABLE parent CASCADE;
3963 -- check any TEMP-ness
3964 CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a);
3965 CREATE TABLE perm_part (a int);
3966 ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1);
3967 ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted"
3968 DROP TABLE temp_parted, perm_part;
3969 -- check that the table being attached is not a typed table
3970 CREATE TYPE mytype AS (a int);
3971 CREATE TABLE fail_part OF mytype;
3972 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3973 ERROR: cannot attach a typed table as partition
3974 DROP TYPE mytype CASCADE;
3975 NOTICE: drop cascades to table fail_part
3976 -- check that the table being attached has only columns present in the parent
3977 CREATE TABLE fail_part (like list_parted, c int);
3978 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3979 ERROR: table "fail_part" contains column "c" not found in parent "list_parted"
3980 DETAIL: The new partition may contain only the columns present in parent.
3981 DROP TABLE fail_part;
3982 -- check that the table being attached has every column of the parent
3983 CREATE TABLE fail_part (a int NOT NULL);
3984 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3985 ERROR: child table is missing column "b"
3986 DROP TABLE fail_part;
3987 -- check that columns match in type, collation and NOT NULL status
3988 CREATE TABLE fail_part (
3992 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3993 ERROR: child table "fail_part" has different type for column "b"
3994 ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX";
3995 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
3996 ERROR: child table "fail_part" has different collation for column "b"
3997 DROP TABLE fail_part;
3998 -- check that the table being attached has all constraints of the parent
3999 CREATE TABLE fail_part (
4000 b char(2) COLLATE "C",
4003 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
4004 ERROR: child table is missing constraint "check_a"
4005 -- check that the constraint matches in definition with parent's constraint
4006 ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0);
4007 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
4008 ERROR: child table "fail_part" has different definition for check constraint "check_a"
4009 DROP TABLE fail_part;
4010 -- check the attributes and constraints after partition is attached
4011 CREATE TABLE part_1 (
4013 b char(2) COLLATE "C",
4014 CONSTRAINT check_a CHECK (a > 0)
4016 ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1);
4017 -- attislocal and conislocal are always false for merged attributes and constraints respectively.
4018 SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0;
4019 attislocal | attinhcount
4020 ------------+-------------
4025 SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a';
4026 conislocal | coninhcount
4027 ------------+-------------
4031 -- check that the new partition won't overlap with an existing partition
4032 CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS);
4033 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
4034 ERROR: partition "fail_part" would overlap partition "part_1"
4035 LINE 1: ...LE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
4037 DROP TABLE fail_part;
4038 -- check that an existing table can be attached as a default partition
4039 CREATE TABLE def_part (LIKE list_parted INCLUDING CONSTRAINTS);
4040 ALTER TABLE list_parted ATTACH PARTITION def_part DEFAULT;
4041 -- check attaching default partition fails if a default partition already
4043 CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS);
4044 ALTER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT;
4045 ERROR: partition "fail_def_part" conflicts with existing default partition "def_part"
4046 LINE 1: ...ER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT;
4048 -- check validation when attaching list partitions
4049 CREATE TABLE list_parted2 (
4052 ) PARTITION BY LIST (a);
4053 -- check that violating rows are correctly reported
4054 CREATE TABLE part_2 (LIKE list_parted2);
4055 INSERT INTO part_2 VALUES (3, 'a');
4056 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
4057 ERROR: partition constraint of relation "part_2" is violated by some row
4058 -- should be ok after deleting the bad row
4060 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
4061 -- check partition cannot be attached if default has some row for its values
4062 CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
4063 INSERT INTO list_parted2_def VALUES (11, 'z');
4064 CREATE TABLE part_3 (LIKE list_parted2);
4065 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
4066 ERROR: updated partition constraint for default partition "list_parted2_def" would be violated by some row
4067 -- should be ok after deleting the bad row
4068 DELETE FROM list_parted2_def WHERE a = 11;
4069 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
4070 -- adding constraints that describe the desired partition constraint
4071 -- (or more restrictive) will help skip the validation scan
4072 CREATE TABLE part_3_4 (
4074 CONSTRAINT check_a CHECK (a IN (3))
4076 -- however, if a list partition does not accept nulls, there should be
4077 -- an explicit NOT NULL constraint on the partition key column for the
4078 -- validation scan to be skipped;
4079 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
4080 -- adding a NOT NULL constraint will cause the scan to be skipped
4081 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
4082 ALTER TABLE part_3_4 ALTER a SET NOT NULL;
4083 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
4084 -- check if default partition scan skipped
4085 ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6));
4086 CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);
4087 -- check validation when attaching range partitions
4088 CREATE TABLE range_parted (
4091 ) PARTITION BY RANGE (a, b);
4092 -- check that violating rows are correctly reported
4093 CREATE TABLE part1 (
4094 a int NOT NULL CHECK (a = 1),
4095 b int NOT NULL CHECK (b >= 1 AND b <= 10)
4097 INSERT INTO part1 VALUES (1, 10);
4098 -- Remember the TO bound is exclusive
4099 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
4100 ERROR: partition constraint of relation "part1" is violated by some row
4101 -- should be ok after deleting the bad row
4103 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
4104 -- adding constraints that describe the desired partition constraint
4105 -- (or more restrictive) will help skip the validation scan
4106 CREATE TABLE part2 (
4107 a int NOT NULL CHECK (a = 1),
4108 b int NOT NULL CHECK (b >= 10 AND b < 18)
4110 ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20);
4111 -- Create default partition
4112 CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT;
4113 -- Only one default partition is allowed, hence, following should give error
4114 CREATE TABLE partr_def2 (LIKE part1 INCLUDING CONSTRAINTS);
4115 ALTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT;
4116 ERROR: partition "partr_def2" conflicts with existing default partition "partr_def1"
4117 LINE 1: ...LTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT;
4119 -- Overlapping partitions cannot be attached, hence, following should give error
4120 INSERT INTO partr_def1 VALUES (2, 10);
4121 CREATE TABLE part3 (LIKE range_parted);
4122 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (2, 10) TO (2, 20);
4123 ERROR: updated partition constraint for default partition "partr_def1" would be violated by some row
4124 -- Attaching partitions should be successful when there are no overlapping rows
4125 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (3, 10) TO (3, 20);
4126 -- check that leaf partitions are scanned when attaching a partitioned
4128 CREATE TABLE part_5 (
4130 ) PARTITION BY LIST (b);
4131 -- check that violating rows are correctly reported
4132 CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a');
4133 INSERT INTO part_5_a (a, b) VALUES (6, 'a');
4134 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
4135 ERROR: partition constraint of relation "part_5_a" is violated by some row
4136 -- delete the faulting row and also add a constraint to skip the scan
4137 DELETE FROM part_5_a WHERE a NOT IN (3);
4138 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);
4139 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
4140 ALTER TABLE list_parted2 DETACH PARTITION part_5;
4141 ALTER TABLE part_5 DROP CONSTRAINT check_a;
4142 -- scan should again be skipped, even though NOT NULL is now a column property
4143 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL;
4144 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
4145 -- Check the case where attnos of the partitioning columns in the table being
4146 -- attached differs from the parent. It should not affect the constraint-
4147 -- checking logic that allows to skip the scan.
4148 CREATE TABLE part_6 (
4151 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6)
4153 ALTER TABLE part_6 DROP c;
4154 ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);
4155 -- Similar to above, but the table being attached is a partitioned table
4156 -- whose partition has still different attnos for the root partitioning
4158 CREATE TABLE part_7 (
4160 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
4161 ) PARTITION BY LIST (b);
4162 CREATE TABLE part_7_a_null (
4166 LIKE list_parted2, -- 'a' will have attnum = 4
4167 CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'),
4168 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
4170 ALTER TABLE part_7_a_null DROP c, DROP d, DROP e;
4171 ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null);
4172 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
4173 -- Same example, but check this time that the constraint correctly detects
4175 ALTER TABLE list_parted2 DETACH PARTITION part_7;
4176 ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped
4177 INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a');
4178 SELECT tableoid::regclass, a, b FROM part_7 order by a;
4180 ---------------+---+---
4182 part_7_a_null | 9 | a
4185 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
4186 ERROR: partition constraint of relation "part_7_a_null" is violated by some row
4187 -- check that leaf partitions of default partition are scanned when
4188 -- attaching a partitioned table.
4189 ALTER TABLE part_5 DROP CONSTRAINT check_a;
4190 CREATE TABLE part5_def PARTITION OF part_5 DEFAULT PARTITION BY LIST(a);
4191 CREATE TABLE part5_def_p1 PARTITION OF part5_def FOR VALUES IN (5);
4192 INSERT INTO part5_def_p1 VALUES (5, 'y');
4193 CREATE TABLE part5_p1 (LIKE part_5);
4194 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
4195 ERROR: updated partition constraint for default partition "part5_def_p1" would be violated by some row
4196 -- should be ok after deleting the bad row
4197 DELETE FROM part5_def_p1 WHERE b = 'y';
4198 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
4199 -- check that the table being attached is not already a partition
4200 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
4201 ERROR: "part_2" is already a partition
4202 -- check that circular inheritance is not allowed
4203 ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b');
4204 ERROR: circular inheritance not allowed
4205 DETAIL: "part_5" is already a child of "list_parted2".
4206 ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0);
4207 ERROR: circular inheritance not allowed
4208 DETAIL: "list_parted2" is already a child of "list_parted2".
4209 -- If a partitioned table being created or an existing table being attached
4210 -- as a partition does not have a constraint that would allow validation scan
4211 -- to be skipped, but an individual partition does, then the partition's
4212 -- validation scan is skipped.
4213 CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a);
4214 CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b);
4215 CREATE TABLE quuux_default1 PARTITION OF quuux_default (
4216 CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1)
4217 ) FOR VALUES IN ('b');
4218 CREATE TABLE quuux1 (a int, b text);
4219 ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate!
4220 CREATE TABLE quuux2 (a int, b text);
4221 ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation
4222 DROP TABLE quuux1, quuux2;
4223 -- should validate for quuux1, but not for quuux2
4224 CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);
4225 CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);
4227 -- check validation when attaching hash partitions
4228 -- Use hand-rolled hash functions and operator class to get predictable result
4229 -- on different machines. part_test_int4_ops is defined in test_setup.sql.
4230 -- check that the new partition won't overlap with an existing partition
4231 CREATE TABLE hash_parted (
4234 ) PARTITION BY HASH (a part_test_int4_ops);
4235 CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0);
4236 CREATE TABLE fail_part (LIKE hpart_1);
4237 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4);
4238 ERROR: partition "fail_part" would overlap partition "hpart_1"
4239 LINE 1: ...hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODU...
4241 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 0);
4242 ERROR: partition "fail_part" would overlap partition "hpart_1"
4243 LINE 1: ...hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODU...
4245 DROP TABLE fail_part;
4246 -- check validation when attaching hash partitions
4247 -- check that violating rows are correctly reported
4248 CREATE TABLE hpart_2 (LIKE hash_parted);
4249 INSERT INTO hpart_2 VALUES (3, 0);
4250 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
4251 ERROR: partition constraint of relation "hpart_2" is violated by some row
4252 -- should be ok after deleting the bad row
4253 DELETE FROM hpart_2;
4254 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
4255 -- check that leaf partitions are scanned when attaching a partitioned
4257 CREATE TABLE hpart_5 (
4259 ) PARTITION BY LIST (b);
4260 -- check that violating rows are correctly reported
4261 CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3');
4262 INSERT INTO hpart_5_a (a, b) VALUES (7, 1);
4263 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
4264 ERROR: partition constraint of relation "hpart_5_a" is violated by some row
4265 -- should be ok after deleting the bad row
4266 DELETE FROM hpart_5_a;
4267 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
4268 -- check that the table being attach is with valid modulus and remainder value
4269 CREATE TABLE fail_part(LIKE hash_parted);
4270 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 0, REMAINDER 1);
4271 ERROR: modulus for hash partition must be an integer value greater than zero
4272 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 8);
4273 ERROR: remainder for hash partition must be less than modulus
4274 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2);
4275 ERROR: every hash partition modulus must be a factor of the next larger modulus
4276 DETAIL: The new modulus 3 is not a factor of 4, the modulus of existing partition "hpart_1".
4277 DROP TABLE fail_part;
4281 -- check that the table is partitioned at all
4282 CREATE TABLE regular_table (a int);
4283 ALTER TABLE regular_table DETACH PARTITION any_name;
4284 ERROR: table "regular_table" is not partitioned
4285 DROP TABLE regular_table;
4286 -- check that the partition being detached exists at all
4287 ALTER TABLE list_parted2 DETACH PARTITION part_4;
4288 ERROR: relation "part_4" does not exist
4289 ALTER TABLE hash_parted DETACH PARTITION hpart_4;
4290 ERROR: relation "hpart_4" does not exist
4291 -- check that the partition being detached is actually a partition of the parent
4292 CREATE TABLE not_a_part (a int);
4293 ALTER TABLE list_parted2 DETACH PARTITION not_a_part;
4294 ERROR: relation "not_a_part" is not a partition of relation "list_parted2"
4295 ALTER TABLE list_parted2 DETACH PARTITION part_1;
4296 ERROR: relation "part_1" is not a partition of relation "list_parted2"
4297 ALTER TABLE hash_parted DETACH PARTITION not_a_part;
4298 ERROR: relation "not_a_part" is not a partition of relation "hash_parted"
4299 DROP TABLE not_a_part;
4300 -- check that, after being detached, attinhcount/coninhcount is dropped to 0 and
4301 -- attislocal/conislocal is set to true
4302 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
4303 SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0;
4304 attinhcount | attislocal
4305 -------------+------------
4310 SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a';
4311 coninhcount | conislocal
4312 -------------+------------
4316 DROP TABLE part_3_4;
4317 -- check that a detached partition is not dropped on dropping a partitioned table
4318 CREATE TABLE range_parted2 (
4320 ) PARTITION BY RANGE(a);
4321 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
4322 ALTER TABLE range_parted2 DETACH PARTITION part_rp;
4323 DROP TABLE range_parted2;
4324 SELECT * from part_rp;
4330 -- concurrent detach
4331 CREATE TABLE range_parted2 (
4333 ) PARTITION BY RANGE(a);
4334 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
4336 -- doesn't work in a partition block
4337 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
4338 ERROR: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block
4340 CREATE TABLE part_rpd PARTITION OF range_parted2 DEFAULT;
4341 -- doesn't work if there's a default partition
4342 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
4343 ERROR: cannot detach partitions concurrently when a default partition exists
4344 -- doesn't work for the default partition
4345 ALTER TABLE range_parted2 DETACH PARTITION part_rpd CONCURRENTLY;
4346 ERROR: cannot detach partitions concurrently when a default partition exists
4347 DROP TABLE part_rpd;
4349 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
4351 Partitioned table "public.range_parted2"
4352 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
4353 --------+---------+-----------+----------+---------+---------+--------------+-------------
4354 a | integer | | | | plain | |
4355 Partition key: RANGE (a)
4356 Number of partitions: 0
4358 -- constraint should be created
4360 Table "public.part_rp"
4361 Column | Type | Collation | Nullable | Default
4362 --------+---------+-----------+----------+---------
4365 "part_rp_a_check" CHECK (a IS NOT NULL AND a >= 0 AND a < 100)
4367 CREATE TABLE part_rp100 PARTITION OF range_parted2 (CHECK (a>=123 AND a<133 AND a IS NOT NULL)) FOR VALUES FROM (100) to (200);
4368 ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY;
4369 -- redundant constraint should not be created
4371 Table "public.part_rp100"
4372 Column | Type | Collation | Nullable | Default
4373 --------+---------+-----------+----------+---------
4376 "part_rp100_a_check" CHECK (a >= 123 AND a < 133 AND a IS NOT NULL)
4378 DROP TABLE range_parted2;
4379 -- Check ALTER TABLE commands for partitioned tables and partitions
4380 -- cannot add/drop column to/from *only* the parent
4381 ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
4382 ERROR: column must be added to child tables too
4383 ALTER TABLE ONLY list_parted2 DROP COLUMN b;
4384 ERROR: cannot drop column from only the partitioned table when partitions exist
4385 HINT: Do not specify the ONLY keyword.
4386 -- cannot add a column to partition or drop an inherited one
4387 ALTER TABLE part_2 ADD COLUMN c text;
4388 ERROR: cannot add column to a partition
4389 ALTER TABLE part_2 DROP COLUMN b;
4390 ERROR: cannot drop inherited column "b"
4391 -- Nor rename, alter type
4392 ALTER TABLE part_2 RENAME COLUMN b to c;
4393 ERROR: cannot rename inherited column "b"
4394 ALTER TABLE part_2 ALTER COLUMN b TYPE text;
4395 ERROR: cannot alter inherited column "b"
4396 -- cannot add/drop NOT NULL or check constraints to *only* the parent, when
4398 ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL;
4399 ERROR: constraint must be added to child tables too
4400 DETAIL: Column "b" of relation "part_2" is not already NOT NULL.
4401 HINT: Do not specify the ONLY keyword.
4402 ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
4403 ERROR: constraint must be added to child tables too
4404 ALTER TABLE list_parted2 ALTER b SET NOT NULL;
4405 ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL;
4406 ERROR: cannot remove constraint from only the partitioned table when partitions exist
4407 HINT: Do not specify the ONLY keyword.
4408 ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
4409 ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b;
4410 ERROR: cannot remove constraint from only the partitioned table when partitions exist
4411 HINT: Do not specify the ONLY keyword.
4412 -- It's alright though, if no partitions are yet created
4413 CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a);
4414 ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL;
4415 ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0);
4416 ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL;
4417 ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a;
4418 DROP TABLE parted_no_parts;
4419 -- cannot drop inherited NOT NULL or check constraints from partition
4420 ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0);
4421 ALTER TABLE part_2 ALTER b DROP NOT NULL;
4422 ERROR: column "b" is marked NOT NULL in parent table
4423 ALTER TABLE part_2 DROP CONSTRAINT check_a2;
4424 ERROR: cannot drop inherited constraint "check_a2" of relation "part_2"
4425 -- Doesn't make sense to add NO INHERIT constraints on partitioned tables
4426 ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT;
4427 ERROR: cannot add NO INHERIT constraint to partitioned table "list_parted2"
4428 -- check that a partition cannot participate in regular inheritance
4429 CREATE TABLE inh_test () INHERITS (part_2);
4430 ERROR: cannot inherit from partition "part_2"
4431 CREATE TABLE inh_test (LIKE part_2);
4432 ALTER TABLE inh_test INHERIT part_2;
4433 ERROR: cannot inherit from a partition
4434 ALTER TABLE part_2 INHERIT inh_test;
4435 ERROR: cannot change inheritance of a partition
4436 -- cannot drop or alter type of partition key columns of lower level
4437 -- partitioned tables; for example, part_5, which is list_parted2's
4438 -- partition, is partitioned on b;
4439 ALTER TABLE list_parted2 DROP COLUMN b;
4440 ERROR: cannot drop column "b" because it is part of the partition key of relation "part_5"
4441 ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
4442 ERROR: cannot alter column "b" because it is part of the partition key of relation "part_5"
4443 -- dropping non-partition key columns should be allowed on the parent table.
4444 ALTER TABLE list_parted DROP COLUMN b;
4445 SELECT * FROM list_parted;
4451 DROP TABLE list_parted, list_parted2, range_parted;
4452 DROP TABLE fail_def_part;
4453 DROP TABLE hash_parted;
4454 -- more tests for certain multi-level partitioning scenarios
4455 create table p (a int, b int) partition by range (a, b);
4456 create table p1 (b int, a int not null) partition by range (b);
4457 create table p11 (like p1);
4458 alter table p11 drop a;
4459 alter table p11 add a int;
4460 alter table p11 drop a;
4461 alter table p11 add a int not null;
4462 -- attnum for key attribute 'a' is different in p, p1, and p11
4463 select attrelid::regclass, attname, attnum
4466 and (attrelid = 'p'::regclass
4467 or attrelid = 'p1'::regclass
4468 or attrelid = 'p11'::regclass)
4469 order by attrelid::regclass::text;
4470 attrelid | attname | attnum
4471 ----------+---------+--------
4477 alter table p1 attach partition p11 for values from (2) to (5);
4478 insert into p1 (a, b) values (2, 3);
4479 -- check that partition validation scan correctly detects violating rows
4480 alter table p attach partition p1 for values from (1, 2) to (1, 10);
4481 ERROR: partition constraint of relation "p11" is violated by some row
4485 -- validate constraint on partitioned tables should only scan leaf partitions
4486 create table parted_validate_test (a int) partition by list (a);
4487 create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1);
4488 alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
4489 alter table parted_validate_test validate constraint parted_validate_test_chka;
4490 drop table parted_validate_test;
4491 -- test alter column options
4492 CREATE TABLE attmp(i integer);
4493 INSERT INTO attmp VALUES (1);
4494 ALTER TABLE attmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
4495 ALTER TABLE attmp ALTER COLUMN i RESET (n_distinct_inherited);
4498 DROP USER regress_alter_table_user1;
4499 -- check that violating rows are correctly reported when attaching as the
4500 -- default partition
4501 create table defpart_attach_test (a int) partition by list (a);
4502 create table defpart_attach_test1 partition of defpart_attach_test for values in (1);
4503 create table defpart_attach_test_d (b int, a int);
4504 alter table defpart_attach_test_d drop b;
4505 insert into defpart_attach_test_d values (1), (2);
4506 -- error because its constraint as the default partition would be violated
4507 -- by the row containing 1
4508 alter table defpart_attach_test attach partition defpart_attach_test_d default;
4509 ERROR: partition constraint of relation "defpart_attach_test_d" is violated by some row
4510 delete from defpart_attach_test_d where a = 1;
4511 alter table defpart_attach_test_d add check (a > 1);
4512 -- should be attached successfully and without needing to be scanned
4513 alter table defpart_attach_test attach partition defpart_attach_test_d default;
4514 -- check that attaching a partition correctly reports any rows in the default
4515 -- partition that should not be there for the new partition to be attached
4517 create table defpart_attach_test_2 (like defpart_attach_test_d);
4518 alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2);
4519 ERROR: updated partition constraint for default partition "defpart_attach_test_d" would be violated by some row
4520 drop table defpart_attach_test;
4521 -- check combinations of temporary and permanent relations when attaching
4523 create table perm_part_parent (a int) partition by list (a);
4524 create temp table temp_part_parent (a int) partition by list (a);
4525 create table perm_part_child (a int);
4526 create temp table temp_part_child (a int);
4527 alter table temp_part_parent attach partition perm_part_child default; -- error
4528 ERROR: cannot attach a permanent relation as partition of temporary relation "temp_part_parent"
4529 alter table perm_part_parent attach partition temp_part_child default; -- error
4530 ERROR: cannot attach a temporary relation as partition of permanent relation "perm_part_parent"
4531 alter table temp_part_parent attach partition temp_part_child default; -- ok
4532 drop table perm_part_parent cascade;
4533 drop table temp_part_parent cascade;
4534 -- check that attaching partitions to a table while it is being used is
4536 create table tab_part_attach (a int) partition by list (a);
4537 create or replace function func_part_attach() returns trigger
4538 language plpgsql as $$
4540 execute 'create table tab_part_attach_1 (a int)';
4541 execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)';
4544 create trigger trig_part_attach before insert on tab_part_attach
4545 for each statement execute procedure func_part_attach();
4546 insert into tab_part_attach values (1);
4547 ERROR: cannot ALTER TABLE "tab_part_attach" because it is being used by active queries in this session
4548 CONTEXT: SQL statement "alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)"
4549 PL/pgSQL function func_part_attach() line 4 at EXECUTE
4550 drop table tab_part_attach;
4551 drop function func_part_attach();
4552 -- test case where the partitioning operator is a SQL function whose
4553 -- evaluation results in the table's relcache being rebuilt partway through
4554 -- the execution of an ATTACH PARTITION command
4555 create function at_test_sql_partop (int4, int4) returns int language sql
4556 as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$;
4557 create operator class at_test_sql_partop for type int4 using btree as
4558 operator 1 < (int4, int4), operator 2 <= (int4, int4),
4559 operator 3 = (int4, int4), operator 4 >= (int4, int4),
4560 operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4);
4561 create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop);
4562 create table at_test_sql_partop_1 (a int);
4563 alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10);
4564 drop table at_test_sql_partop;
4565 drop operator class at_test_sql_partop using btree;
4566 drop function at_test_sql_partop;
4567 /* Test case for bug #16242 */
4568 -- We create a parent and child where the child has missing
4569 -- non-null attribute values, and arrange to pass them through
4570 -- tuple conversion from the child to the parent tupdesc
4571 create table bar1 (a integer, b integer not null default 1)
4572 partition by range (a);
4573 create table bar2 (a integer);
4574 insert into bar2 values (1);
4575 alter table bar2 add column b integer not null default 1;
4576 -- (at this point bar2 contains tuple with natts=1)
4577 alter table bar1 attach partition bar2 default;
4585 -- this exercises tuple conversion:
4586 create function xtrig()
4587 returns trigger language plpgsql
4592 for r in select * from old loop
4593 raise info 'a=%, b=%', r.a, r.b;
4598 create trigger xtrig
4599 after update on bar1
4600 referencing old table as old
4601 for each statement execute procedure xtrig();
4602 update bar1 set a = a + 1;
4604 /* End test case for bug #16242 */
4605 /* Test case for bug #17409 */
4606 create table attbl (p1 int constraint pk_attbl primary key);
4607 create table atref (c1 int references attbl(p1));
4608 cluster attbl using pk_attbl;
4609 alter table attbl alter column p1 set data type bigint;
4610 alter table atref alter column c1 set data type bigint;
4611 drop table attbl, atref;
4612 create table attbl (p1 int constraint pk_attbl primary key);
4613 alter table attbl replica identity using index pk_attbl;
4614 create table atref (c1 int references attbl(p1));
4615 alter table attbl alter column p1 set data type bigint;
4616 alter table atref alter column c1 set data type bigint;
4617 drop table attbl, atref;
4618 /* End test case for bug #17409 */
4619 -- Test that ALTER TABLE rewrite preserves a clustered index
4620 -- for normal indexes and indexes on constraints.
4621 create table alttype_cluster (a int);
4622 alter table alttype_cluster add primary key (a);
4623 create index alttype_cluster_ind on alttype_cluster (a);
4624 alter table alttype_cluster cluster on alttype_cluster_ind;
4625 -- Normal index remains clustered.
4626 select indexrelid::regclass, indisclustered from pg_index
4627 where indrelid = 'alttype_cluster'::regclass
4628 order by indexrelid::regclass::text;
4629 indexrelid | indisclustered
4630 ----------------------+----------------
4631 alttype_cluster_ind | t
4632 alttype_cluster_pkey | f
4635 alter table alttype_cluster alter a type bigint;
4636 select indexrelid::regclass, indisclustered from pg_index
4637 where indrelid = 'alttype_cluster'::regclass
4638 order by indexrelid::regclass::text;
4639 indexrelid | indisclustered
4640 ----------------------+----------------
4641 alttype_cluster_ind | t
4642 alttype_cluster_pkey | f
4645 -- Constraint index remains clustered.
4646 alter table alttype_cluster cluster on alttype_cluster_pkey;
4647 select indexrelid::regclass, indisclustered from pg_index
4648 where indrelid = 'alttype_cluster'::regclass
4649 order by indexrelid::regclass::text;
4650 indexrelid | indisclustered
4651 ----------------------+----------------
4652 alttype_cluster_ind | f
4653 alttype_cluster_pkey | t
4656 alter table alttype_cluster alter a type int;
4657 select indexrelid::regclass, indisclustered from pg_index
4658 where indrelid = 'alttype_cluster'::regclass
4659 order by indexrelid::regclass::text;
4660 indexrelid | indisclustered
4661 ----------------------+----------------
4662 alttype_cluster_ind | f
4663 alttype_cluster_pkey | t
4666 drop table alttype_cluster;
4668 -- Check that attaching or detaching a partitioned partition correctly leads
4669 -- to its partitions' constraint being updated to reflect the parent's
4670 -- newly added/removed constraint
4671 create table target_parted (a int, b int) partition by list (a);
4672 create table attach_parted (a int, b int) partition by list (b);
4673 create table attach_parted_part1 partition of attach_parted for values in (1);
4674 -- insert a row directly into the leaf partition so that its partition
4675 -- constraint is built and stored in the relcache
4676 insert into attach_parted_part1 values (1, 1);
4677 -- the following better invalidate the partition constraint of the leaf
4679 alter table target_parted attach partition attach_parted for values in (1);
4680 -- ...such that the following insert fails
4681 insert into attach_parted_part1 values (2, 1);
4682 ERROR: new row for relation "attach_parted_part1" violates partition constraint
4683 DETAIL: Failing row contains (2, 1).
4684 -- ...and doesn't when the partition is detached along with its own partition
4685 alter table target_parted detach partition attach_parted;
4686 insert into attach_parted_part1 values (2, 1);
4687 -- Test altering table having publication
4688 create schema alter1;
4689 create schema alter2;
4690 create table alter1.t1 (a int);
4691 set client_min_messages = 'ERROR';
4692 create publication pub1 for table alter1.t1, tables in schema alter2;
4693 reset client_min_messages;
4694 alter table alter1.t1 set schema alter2;
4697 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
4698 --------+---------+-----------+----------+---------+---------+--------------+-------------
4699 a | integer | | | | plain | |
4703 drop publication pub1;
4704 drop schema alter1 cascade;
4705 drop schema alter2 cascade;
4706 NOTICE: drop cascades to table alter2.t1