granicus.if.org Git - postgresql/blob - src/test/regress/sql/alter_table.sql

   1 --
   2 -- ALTER_TABLE
   3 -- add attribute
   4 --
   5
   6 CREATE TABLE tmp (initial int4);
   7
   8 COMMENT ON TABLE tmp_wrong IS 'table comment';
   9 COMMENT ON TABLE tmp IS 'table comment';
  10 COMMENT ON TABLE tmp IS NULL;
  11
  12 ALTER TABLE tmp ADD COLUMN xmin integer; -- fails
  13
  14 ALTER TABLE tmp ADD COLUMN a int4 default 3;
  15
  16 ALTER TABLE tmp ADD COLUMN b name;
  17
  18 ALTER TABLE tmp ADD COLUMN c text;
  19
  20 ALTER TABLE tmp ADD COLUMN d float8;
  21
  22 ALTER TABLE tmp ADD COLUMN e float4;
  23
  24 ALTER TABLE tmp ADD COLUMN f int2;
  25
  26 ALTER TABLE tmp ADD COLUMN g polygon;
  27
  28 ALTER TABLE tmp ADD COLUMN h abstime;
  29
  30 ALTER TABLE tmp ADD COLUMN i char;
  31
  32 ALTER TABLE tmp ADD COLUMN j abstime[];
  33
  34 ALTER TABLE tmp ADD COLUMN k int4;
  35
  36 ALTER TABLE tmp ADD COLUMN l tid;
  37
  38 ALTER TABLE tmp ADD COLUMN m xid;
  39
  40 ALTER TABLE tmp ADD COLUMN n oidvector;
  41
  42 --ALTER TABLE tmp ADD COLUMN o lock;
  43 ALTER TABLE tmp ADD COLUMN p smgr;
  44
  45 ALTER TABLE tmp ADD COLUMN q point;
  46
  47 ALTER TABLE tmp ADD COLUMN r lseg;
  48
  49 ALTER TABLE tmp ADD COLUMN s path;
  50
  51 ALTER TABLE tmp ADD COLUMN t box;
  52
  53 ALTER TABLE tmp ADD COLUMN u tinterval;
  54
  55 ALTER TABLE tmp ADD COLUMN v timestamp;
  56
  57 ALTER TABLE tmp ADD COLUMN w interval;
  58
  59 ALTER TABLE tmp ADD COLUMN x float8[];
  60
  61 ALTER TABLE tmp ADD COLUMN y float4[];
  62
  63 ALTER TABLE tmp ADD COLUMN z int2[];
  64
  65 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  66         v, w, x, y, z)
  67    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
  68         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
  69         314159, '(1,1)', '512',
  70         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
  71         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
  72         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
  73
  74 SELECT * FROM tmp;
  75
  76 DROP TABLE tmp;
  77
  78 -- the wolf bug - schema mods caused inconsistent row descriptors
  79 CREATE TABLE tmp (
  80         initial         int4
  81 );
  82
  83 ALTER TABLE tmp ADD COLUMN a int4;
  84
  85 ALTER TABLE tmp ADD COLUMN b name;
  86
  87 ALTER TABLE tmp ADD COLUMN c text;
  88
  89 ALTER TABLE tmp ADD COLUMN d float8;
  90
  91 ALTER TABLE tmp ADD COLUMN e float4;
  92
  93 ALTER TABLE tmp ADD COLUMN f int2;
  94
  95 ALTER TABLE tmp ADD COLUMN g polygon;
  96
  97 ALTER TABLE tmp ADD COLUMN h abstime;
  98
  99 ALTER TABLE tmp ADD COLUMN i char;
 100
 101 ALTER TABLE tmp ADD COLUMN j abstime[];
 102
 103 ALTER TABLE tmp ADD COLUMN k int4;
 104
 105 ALTER TABLE tmp ADD COLUMN l tid;
 106
 107 ALTER TABLE tmp ADD COLUMN m xid;
 108
 109 ALTER TABLE tmp ADD COLUMN n oidvector;
 110
 111 --ALTER TABLE tmp ADD COLUMN o lock;
 112 ALTER TABLE tmp ADD COLUMN p smgr;
 113
 114 ALTER TABLE tmp ADD COLUMN q point;
 115
 116 ALTER TABLE tmp ADD COLUMN r lseg;
 117
 118 ALTER TABLE tmp ADD COLUMN s path;
 119
 120 ALTER TABLE tmp ADD COLUMN t box;
 121
 122 ALTER TABLE tmp ADD COLUMN u tinterval;
 123
 124 ALTER TABLE tmp ADD COLUMN v timestamp;
 125
 126 ALTER TABLE tmp ADD COLUMN w interval;
 127
 128 ALTER TABLE tmp ADD COLUMN x float8[];
 129
 130 ALTER TABLE tmp ADD COLUMN y float4[];
 131
 132 ALTER TABLE tmp ADD COLUMN z int2[];
 133
 134 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
 135         v, w, x, y, z)
 136    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
 137         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
 138         314159, '(1,1)', '512',
 139         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
 140         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
 141         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
 142
 143 SELECT * FROM tmp;
 144
 145 DROP TABLE tmp;
 146
 147
 148 --
 149 -- rename - check on both non-temp and temp tables
 150 --
 151 CREATE TABLE tmp (regtable int);
 152 CREATE TEMP TABLE tmp (tmptable int);
 153
 154 ALTER TABLE tmp RENAME TO tmp_new;
 155
 156 SELECT * FROM tmp;
 157 SELECT * FROM tmp_new;
 158
 159 ALTER TABLE tmp RENAME TO tmp_new2;
 160
 161 SELECT * FROM tmp;              -- should fail
 162 SELECT * FROM tmp_new;
 163 SELECT * FROM tmp_new2;
 164
 165 DROP TABLE tmp_new;
 166 DROP TABLE tmp_new2;
 167
 168
 169 -- ALTER TABLE ... RENAME on non-table relations
 170 -- renaming indexes (FIXME: this should probably test the index's functionality)
 171 ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
 172 ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
 173
 174 ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
 175 ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
 176 -- renaming views
 177 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
 178 ALTER TABLE tmp_view RENAME TO tmp_view_new;
 179
 180 -- hack to ensure we get an indexscan here
 181 ANALYZE tenk1;
 182 set enable_seqscan to off;
 183 set enable_bitmapscan to off;
 184 -- 5 values, sorted
 185 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 186 reset enable_seqscan;
 187 reset enable_bitmapscan;
 188
 189 DROP VIEW tmp_view_new;
 190 -- toast-like relation name
 191 alter table stud_emp rename to pg_toast_stud_emp;
 192 alter table pg_toast_stud_emp rename to stud_emp;
 193
 194 -- renaming index should rename constraint as well
 195 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
 196 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
 197 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
 198
 199 -- renaming constraint
 200 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
 201 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
 202 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
 203
 204 -- renaming constraint should rename index as well
 205 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
 206 DROP INDEX onek_unique1_constraint;  -- to see whether it's there
 207 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
 208 DROP INDEX onek_unique1_constraint_foo;  -- to see whether it's there
 209 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
 210
 211 -- renaming constraints vs. inheritance
 212 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
 213 \d constraint_rename_test
 214 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
 215 \d constraint_rename_test2
 216 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
 217 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
 218 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
 219 \d constraint_rename_test
 220 \d constraint_rename_test2
 221 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK NO INHERIT (b > 0);
 222 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
 223 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
 224 \d constraint_rename_test
 225 \d constraint_rename_test2
 226 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
 227 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
 228 \d constraint_rename_test
 229 \d constraint_rename_test2
 230 DROP TABLE constraint_rename_test2;
 231 DROP TABLE constraint_rename_test;
 232 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
 233
 234 -- FOREIGN KEY CONSTRAINT adding TEST
 235
 236 CREATE TABLE tmp2 (a int primary key);
 237
 238 CREATE TABLE tmp3 (a int, b int);
 239
 240 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 241
 242 CREATE TABLE tmp5 (a int, b int);
 243
 244 -- Insert rows into tmp2 (pktable)
 245 INSERT INTO tmp2 values (1);
 246 INSERT INTO tmp2 values (2);
 247 INSERT INTO tmp2 values (3);
 248 INSERT INTO tmp2 values (4);
 249
 250 -- Insert rows into tmp3
 251 INSERT INTO tmp3 values (1,10);
 252 INSERT INTO tmp3 values (1,20);
 253 INSERT INTO tmp3 values (5,50);
 254
 255 -- Try (and fail) to add constraint due to invalid source columns
 256 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
 257
 258 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
 259 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
 260
 261 -- Try (and fail) to add constraint due to invalid data
 262 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 263
 264 -- Delete failing row
 265 DELETE FROM tmp3 where a=5;
 266
 267 -- Try (and succeed)
 268 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 269 ALTER TABLE tmp3 drop constraint tmpconstr;
 270
 271 INSERT INTO tmp3 values (5,50);
 272
 273 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
 274 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
 275 ALTER TABLE tmp3 validate constraint tmpconstr;
 276
 277 -- Delete failing row
 278 DELETE FROM tmp3 where a=5;
 279
 280 -- Try (and succeed) and repeat to show it works on already valid constraint
 281 ALTER TABLE tmp3 validate constraint tmpconstr;
 282 ALTER TABLE tmp3 validate constraint tmpconstr;
 283
 284 -- Try a non-verified CHECK constraint
 285 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
 286 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
 287 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
 288 DELETE FROM tmp3 WHERE NOT b > 10;
 289 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
 290 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
 291
 292 -- Test inherited NOT VALID CHECK constraints
 293 select * from tmp3;
 294 CREATE TABLE tmp6 () INHERITS (tmp3);
 295 CREATE TABLE tmp7 () INHERITS (tmp3);
 296
 297 INSERT INTO tmp6 VALUES (6, 30), (7, 16);
 298 ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
 299 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20;   -- fails
 300 DELETE FROM tmp6 WHERE b > 20;
 301 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20;   -- succeeds
 302
 303 -- An already validated constraint must not be revalidated
 304 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
 305 INSERT INTO tmp7 VALUES (8, 18);
 306 ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
 307 ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
 308 ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
 309
 310 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 311 -- tmp4 is a,b
 312
 313 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 314
 315 DROP TABLE tmp7;
 316
 317 DROP TABLE tmp6;
 318
 319 DROP TABLE tmp5;
 320
 321 DROP TABLE tmp4;
 322
 323 DROP TABLE tmp3;
 324
 325 DROP TABLE tmp2;
 326
 327 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
 328 -- exclusion until validated
 329 set constraint_exclusion TO 'partition';
 330 create table nv_parent (d date);
 331 create table nv_child_2010 () inherits (nv_parent);
 332 create table nv_child_2011 () inherits (nv_parent);
 333 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
 334 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
 335 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
 336 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
 337 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
 338 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
 339 -- after validation, the constraint should be used
 340 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
 341 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
 342
 343
 344 -- Foreign key adding test with mixed types
 345
 346 -- Note: these tables are TEMP to avoid name conflicts when this test
 347 -- is run in parallel with foreign_key.sql.
 348
 349 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 350 INSERT INTO PKTABLE VALUES(42);
 351 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 352 -- This next should fail, because int=inet does not exist
 353 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 354 -- This should also fail for the same reason, but here we
 355 -- give the column name
 356 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 357 DROP TABLE FKTABLE;
 358 -- This should succeed, even though they are different types,
 359 -- because int=int8 exists and is a member of the integer opfamily
 360 CREATE TEMP TABLE FKTABLE (ftest1 int8);
 361 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 362 -- Check it actually works
 363 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 364 INSERT INTO FKTABLE VALUES(43);         -- should fail
 365 DROP TABLE FKTABLE;
 366 -- This should fail, because we'd have to cast numeric to int which is
 367 -- not an implicit coercion (or use numeric=numeric, but that's not part
 368 -- of the integer opfamily)
 369 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
 370 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 371 DROP TABLE FKTABLE;
 372 DROP TABLE PKTABLE;
 373 -- On the other hand, this should work because int implicitly promotes to
 374 -- numeric, and we allow promotion on the FK side
 375 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
 376 INSERT INTO PKTABLE VALUES(42);
 377 CREATE TEMP TABLE FKTABLE (ftest1 int);
 378 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 379 -- Check it actually works
 380 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 381 INSERT INTO FKTABLE VALUES(43);         -- should fail
 382 DROP TABLE FKTABLE;
 383 DROP TABLE PKTABLE;
 384
 385 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 386                            PRIMARY KEY(ptest1, ptest2));
 387 -- This should fail, because we just chose really odd types
 388 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 389 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 390 DROP TABLE FKTABLE;
 391 -- Again, so should this...
 392 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 393 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 394      references pktable(ptest1, ptest2);
 395 DROP TABLE FKTABLE;
 396 -- This fails because we mixed up the column ordering
 397 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 398 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 399      references pktable(ptest2, ptest1);
 400 -- As does this...
 401 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 402      references pktable(ptest1, ptest2);
 403
 404 -- temp tables should go away by themselves, need not drop them.
 405
 406 -- test check constraint adding
 407
 408 create table atacc1 ( test int );
 409 -- add a check constraint
 410 alter table atacc1 add constraint atacc_test1 check (test>3);
 411 -- should fail
 412 insert into atacc1 (test) values (2);
 413 -- should succeed
 414 insert into atacc1 (test) values (4);
 415 drop table atacc1;
 416
 417 -- let's do one where the check fails when added
 418 create table atacc1 ( test int );
 419 -- insert a soon to be failing row
 420 insert into atacc1 (test) values (2);
 421 -- add a check constraint (fails)
 422 alter table atacc1 add constraint atacc_test1 check (test>3);
 423 insert into atacc1 (test) values (4);
 424 drop table atacc1;
 425
 426 -- let's do one where the check fails because the column doesn't exist
 427 create table atacc1 ( test int );
 428 -- add a check constraint (fails)
 429 alter table atacc1 add constraint atacc_test1 check (test1>3);
 430 drop table atacc1;
 431
 432 -- something a little more complicated
 433 create table atacc1 ( test int, test2 int, test3 int);
 434 -- add a check constraint (fails)
 435 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 436 -- should fail
 437 insert into atacc1 (test,test2,test3) values (4,4,2);
 438 -- should succeed
 439 insert into atacc1 (test,test2,test3) values (4,4,5);
 440 drop table atacc1;
 441
 442 -- lets do some naming tests
 443 create table atacc1 (test int check (test>3), test2 int);
 444 alter table atacc1 add check (test2>test);
 445 -- should fail for $2
 446 insert into atacc1 (test2, test) values (3, 4);
 447 drop table atacc1;
 448
 449 -- inheritance related tests
 450 create table atacc1 (test int);
 451 create table atacc2 (test2 int);
 452 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 453 alter table atacc2 add constraint foo check (test2>0);
 454 -- fail and then succeed on atacc2
 455 insert into atacc2 (test2) values (-3);
 456 insert into atacc2 (test2) values (3);
 457 -- fail and then succeed on atacc3
 458 insert into atacc3 (test2) values (-3);
 459 insert into atacc3 (test2) values (3);
 460 drop table atacc3;
 461 drop table atacc2;
 462 drop table atacc1;
 463
 464 -- same things with one created with INHERIT
 465 create table atacc1 (test int);
 466 create table atacc2 (test2 int);
 467 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 468 alter table atacc3 no inherit atacc2;
 469 -- fail
 470 alter table atacc3 no inherit atacc2;
 471 -- make sure it really isn't a child
 472 insert into atacc3 (test2) values (3);
 473 select test2 from atacc2;
 474 -- fail due to missing constraint
 475 alter table atacc2 add constraint foo check (test2>0);
 476 alter table atacc3 inherit atacc2;
 477 -- fail due to missing column
 478 alter table atacc3 rename test2 to testx;
 479 alter table atacc3 inherit atacc2;
 480 -- fail due to mismatched data type
 481 alter table atacc3 add test2 bool;
 482 alter table atacc3 inherit atacc2;
 483 alter table atacc3 drop test2;
 484 -- succeed
 485 alter table atacc3 add test2 int;
 486 update atacc3 set test2 = 4 where test2 is null;
 487 alter table atacc3 add constraint foo check (test2>0);
 488 alter table atacc3 inherit atacc2;
 489 -- fail due to duplicates and circular inheritance
 490 alter table atacc3 inherit atacc2;
 491 alter table atacc2 inherit atacc3;
 492 alter table atacc2 inherit atacc2;
 493 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
 494 select test2 from atacc2;
 495 drop table atacc2 cascade;
 496 drop table atacc1;
 497
 498 -- adding only to a parent is allowed as of 9.2
 499
 500 create table atacc1 (test int);
 501 create table atacc2 (test2 int) inherits (atacc1);
 502 -- ok:
 503 alter table atacc1 add constraint foo check no inherit (test>0);
 504 -- check constraint is not there on child
 505 insert into atacc2 (test) values (-3);
 506 -- check constraint is there on parent
 507 insert into atacc1 (test) values (-3);
 508 insert into atacc1 (test) values (3);
 509 -- fail, violating row:
 510 alter table atacc2 add constraint foo check no inherit (test>0);
 511 drop table atacc2;
 512 drop table atacc1;
 513
 514 -- test unique constraint adding
 515
 516 create table atacc1 ( test int ) with oids;
 517 -- add a unique constraint
 518 alter table atacc1 add constraint atacc_test1 unique (test);
 519 -- insert first value
 520 insert into atacc1 (test) values (2);
 521 -- should fail
 522 insert into atacc1 (test) values (2);
 523 -- should succeed
 524 insert into atacc1 (test) values (4);
 525 -- try adding a unique oid constraint
 526 alter table atacc1 add constraint atacc_oid1 unique(oid);
 527 -- try to create duplicates via alter table using - should fail
 528 alter table atacc1 alter column test type integer using 0;
 529 drop table atacc1;
 530
 531 -- let's do one where the unique constraint fails when added
 532 create table atacc1 ( test int );
 533 -- insert soon to be failing rows
 534 insert into atacc1 (test) values (2);
 535 insert into atacc1 (test) values (2);
 536 -- add a unique constraint (fails)
 537 alter table atacc1 add constraint atacc_test1 unique (test);
 538 insert into atacc1 (test) values (3);
 539 drop table atacc1;
 540
 541 -- let's do one where the unique constraint fails
 542 -- because the column doesn't exist
 543 create table atacc1 ( test int );
 544 -- add a unique constraint (fails)
 545 alter table atacc1 add constraint atacc_test1 unique (test1);
 546 drop table atacc1;
 547
 548 -- something a little more complicated
 549 create table atacc1 ( test int, test2 int);
 550 -- add a unique constraint
 551 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 552 -- insert initial value
 553 insert into atacc1 (test,test2) values (4,4);
 554 -- should fail
 555 insert into atacc1 (test,test2) values (4,4);
 556 -- should all succeed
 557 insert into atacc1 (test,test2) values (4,5);
 558 insert into atacc1 (test,test2) values (5,4);
 559 insert into atacc1 (test,test2) values (5,5);
 560 drop table atacc1;
 561
 562 -- lets do some naming tests
 563 create table atacc1 (test int, test2 int, unique(test));
 564 alter table atacc1 add unique (test2);
 565 -- should fail for @@ second one @@
 566 insert into atacc1 (test2, test) values (3, 3);
 567 insert into atacc1 (test2, test) values (2, 3);
 568 drop table atacc1;
 569
 570 -- test primary key constraint adding
 571
 572 create table atacc1 ( test int ) with oids;
 573 -- add a primary key constraint
 574 alter table atacc1 add constraint atacc_test1 primary key (test);
 575 -- insert first value
 576 insert into atacc1 (test) values (2);
 577 -- should fail
 578 insert into atacc1 (test) values (2);
 579 -- should succeed
 580 insert into atacc1 (test) values (4);
 581 -- inserting NULL should fail
 582 insert into atacc1 (test) values(NULL);
 583 -- try adding a second primary key (should fail)
 584 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 585 -- drop first primary key constraint
 586 alter table atacc1 drop constraint atacc_test1 restrict;
 587 -- try adding a primary key on oid (should succeed)
 588 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 589 drop table atacc1;
 590
 591 -- let's do one where the primary key constraint fails when added
 592 create table atacc1 ( test int );
 593 -- insert soon to be failing rows
 594 insert into atacc1 (test) values (2);
 595 insert into atacc1 (test) values (2);
 596 -- add a primary key (fails)
 597 alter table atacc1 add constraint atacc_test1 primary key (test);
 598 insert into atacc1 (test) values (3);
 599 drop table atacc1;
 600
 601 -- let's do another one where the primary key constraint fails when added
 602 create table atacc1 ( test int );
 603 -- insert soon to be failing row
 604 insert into atacc1 (test) values (NULL);
 605 -- add a primary key (fails)
 606 alter table atacc1 add constraint atacc_test1 primary key (test);
 607 insert into atacc1 (test) values (3);
 608 drop table atacc1;
 609
 610 -- let's do one where the primary key constraint fails
 611 -- because the column doesn't exist
 612 create table atacc1 ( test int );
 613 -- add a primary key constraint (fails)
 614 alter table atacc1 add constraint atacc_test1 primary key (test1);
 615 drop table atacc1;
 616
 617 -- adding a new column as primary key to a non-empty table.
 618 -- should fail unless the column has a non-null default value.
 619 create table atacc1 ( test int );
 620 insert into atacc1 (test) values (0);
 621 -- add a primary key column without a default (fails).
 622 alter table atacc1 add column test2 int primary key;
 623 -- now add a primary key column with a default (succeeds).
 624 alter table atacc1 add column test2 int default 0 primary key;
 625 drop table atacc1;
 626
 627 -- something a little more complicated
 628 create table atacc1 ( test int, test2 int);
 629 -- add a primary key constraint
 630 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 631 -- try adding a second primary key - should fail
 632 alter table atacc1 add constraint atacc_test2 primary key (test);
 633 -- insert initial value
 634 insert into atacc1 (test,test2) values (4,4);
 635 -- should fail
 636 insert into atacc1 (test,test2) values (4,4);
 637 insert into atacc1 (test,test2) values (NULL,3);
 638 insert into atacc1 (test,test2) values (3, NULL);
 639 insert into atacc1 (test,test2) values (NULL,NULL);
 640 -- should all succeed
 641 insert into atacc1 (test,test2) values (4,5);
 642 insert into atacc1 (test,test2) values (5,4);
 643 insert into atacc1 (test,test2) values (5,5);
 644 drop table atacc1;
 645
 646 -- lets do some naming tests
 647 create table atacc1 (test int, test2 int, primary key(test));
 648 -- only first should succeed
 649 insert into atacc1 (test2, test) values (3, 3);
 650 insert into atacc1 (test2, test) values (2, 3);
 651 insert into atacc1 (test2, test) values (1, NULL);
 652 drop table atacc1;
 653
 654 -- alter table / alter column [set/drop] not null tests
 655 -- try altering system catalogs, should fail
 656 alter table pg_class alter column relname drop not null;
 657 alter table pg_class alter relname set not null;
 658
 659 -- try altering non-existent table, should fail
 660 alter table non_existent alter column bar set not null;
 661 alter table non_existent alter column bar drop not null;
 662
 663 -- test setting columns to null and not null and vice versa
 664 -- test checking for null values and primary key
 665 create table atacc1 (test int not null) with oids;
 666 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 667 alter table atacc1 alter column test drop not null;
 668 alter table atacc1 drop constraint "atacc1_pkey";
 669 alter table atacc1 alter column test drop not null;
 670 insert into atacc1 values (null);
 671 alter table atacc1 alter test set not null;
 672 delete from atacc1;
 673 alter table atacc1 alter test set not null;
 674
 675 -- try altering a non-existent column, should fail
 676 alter table atacc1 alter bar set not null;
 677 alter table atacc1 alter bar drop not null;
 678
 679 -- try altering the oid column, should fail
 680 alter table atacc1 alter oid set not null;
 681 alter table atacc1 alter oid drop not null;
 682
 683 -- try creating a view and altering that, should fail
 684 create view myview as select * from atacc1;
 685 alter table myview alter column test drop not null;
 686 alter table myview alter column test set not null;
 687 drop view myview;
 688
 689 drop table atacc1;
 690
 691 -- test inheritance
 692 create table parent (a int);
 693 create table child (b varchar(255)) inherits (parent);
 694
 695 alter table parent alter a set not null;
 696 insert into parent values (NULL);
 697 insert into child (a, b) values (NULL, 'foo');
 698 alter table parent alter a drop not null;
 699 insert into parent values (NULL);
 700 insert into child (a, b) values (NULL, 'foo');
 701 alter table only parent alter a set not null;
 702 alter table child alter a set not null;
 703 delete from parent;
 704 alter table only parent alter a set not null;
 705 insert into parent values (NULL);
 706 alter table child alter a set not null;
 707 insert into child (a, b) values (NULL, 'foo');
 708 delete from child;
 709 alter table child alter a set not null;
 710 insert into child (a, b) values (NULL, 'foo');
 711 drop table child;
 712 drop table parent;
 713
 714 -- test setting and removing default values
 715 create table def_test (
 716         c1      int4 default 5,
 717         c2      text default 'initial_default'
 718 );
 719 insert into def_test default values;
 720 alter table def_test alter column c1 drop default;
 721 insert into def_test default values;
 722 alter table def_test alter column c2 drop default;
 723 insert into def_test default values;
 724 alter table def_test alter column c1 set default 10;
 725 alter table def_test alter column c2 set default 'new_default';
 726 insert into def_test default values;
 727 select * from def_test;
 728
 729 -- set defaults to an incorrect type: this should fail
 730 alter table def_test alter column c1 set default 'wrong_datatype';
 731 alter table def_test alter column c2 set default 20;
 732
 733 -- set defaults on a non-existent column: this should fail
 734 alter table def_test alter column c3 set default 30;
 735
 736 -- set defaults on views: we need to create a view, add a rule
 737 -- to allow insertions into it, and then alter the view to add
 738 -- a default
 739 create view def_view_test as select * from def_test;
 740 create rule def_view_test_ins as
 741         on insert to def_view_test
 742         do instead insert into def_test select new.*;
 743 insert into def_view_test default values;
 744 alter table def_view_test alter column c1 set default 45;
 745 insert into def_view_test default values;
 746 alter table def_view_test alter column c2 set default 'view_default';
 747 insert into def_view_test default values;
 748 select * from def_view_test;
 749
 750 drop rule def_view_test_ins on def_view_test;
 751 drop view def_view_test;
 752 drop table def_test;
 753
 754 -- alter table / drop column tests
 755 -- try altering system catalogs, should fail
 756 alter table pg_class drop column relname;
 757
 758 -- try altering non-existent table, should fail
 759 alter table nosuchtable drop column bar;
 760
 761 -- test dropping columns
 762 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
 763 insert into atacc1 values (1, 2, 3, 4);
 764 alter table atacc1 drop a;
 765 alter table atacc1 drop a;
 766
 767 -- SELECTs
 768 select * from atacc1;
 769 select * from atacc1 order by a;
 770 select * from atacc1 order by "........pg.dropped.1........";
 771 select * from atacc1 group by a;
 772 select * from atacc1 group by "........pg.dropped.1........";
 773 select atacc1.* from atacc1;
 774 select a from atacc1;
 775 select atacc1.a from atacc1;
 776 select b,c,d from atacc1;
 777 select a,b,c,d from atacc1;
 778 select * from atacc1 where a = 1;
 779 select "........pg.dropped.1........" from atacc1;
 780 select atacc1."........pg.dropped.1........" from atacc1;
 781 select "........pg.dropped.1........",b,c,d from atacc1;
 782 select * from atacc1 where "........pg.dropped.1........" = 1;
 783
 784 -- UPDATEs
 785 update atacc1 set a = 3;
 786 update atacc1 set b = 2 where a = 3;
 787 update atacc1 set "........pg.dropped.1........" = 3;
 788 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
 789
 790 -- INSERTs
 791 insert into atacc1 values (10, 11, 12, 13);
 792 insert into atacc1 values (default, 11, 12, 13);
 793 insert into atacc1 values (11, 12, 13);
 794 insert into atacc1 (a) values (10);
 795 insert into atacc1 (a) values (default);
 796 insert into atacc1 (a,b,c,d) values (10,11,12,13);
 797 insert into atacc1 (a,b,c,d) values (default,11,12,13);
 798 insert into atacc1 (b,c,d) values (11,12,13);
 799 insert into atacc1 ("........pg.dropped.1........") values (10);
 800 insert into atacc1 ("........pg.dropped.1........") values (default);
 801 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
 802 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
 803
 804 -- DELETEs
 805 delete from atacc1 where a = 3;
 806 delete from atacc1 where "........pg.dropped.1........" = 3;
 807 delete from atacc1;
 808
 809 -- try dropping a non-existent column, should fail
 810 alter table atacc1 drop bar;
 811
 812 -- try dropping the oid column, should succeed
 813 alter table atacc1 drop oid;
 814
 815 -- try dropping the xmin column, should fail
 816 alter table atacc1 drop xmin;
 817
 818 -- try creating a view and altering that, should fail
 819 create view myview as select * from atacc1;
 820 select * from myview;
 821 alter table myview drop d;
 822 drop view myview;
 823
 824 -- test some commands to make sure they fail on the dropped column
 825 analyze atacc1(a);
 826 analyze atacc1("........pg.dropped.1........");
 827 vacuum analyze atacc1(a);
 828 vacuum analyze atacc1("........pg.dropped.1........");
 829 comment on column atacc1.a is 'testing';
 830 comment on column atacc1."........pg.dropped.1........" is 'testing';
 831 alter table atacc1 alter a set storage plain;
 832 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
 833 alter table atacc1 alter a set statistics 0;
 834 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
 835 alter table atacc1 alter a set default 3;
 836 alter table atacc1 alter "........pg.dropped.1........" set default 3;
 837 alter table atacc1 alter a drop default;
 838 alter table atacc1 alter "........pg.dropped.1........" drop default;
 839 alter table atacc1 alter a set not null;
 840 alter table atacc1 alter "........pg.dropped.1........" set not null;
 841 alter table atacc1 alter a drop not null;
 842 alter table atacc1 alter "........pg.dropped.1........" drop not null;
 843 alter table atacc1 rename a to x;
 844 alter table atacc1 rename "........pg.dropped.1........" to x;
 845 alter table atacc1 add primary key(a);
 846 alter table atacc1 add primary key("........pg.dropped.1........");
 847 alter table atacc1 add unique(a);
 848 alter table atacc1 add unique("........pg.dropped.1........");
 849 alter table atacc1 add check (a > 3);
 850 alter table atacc1 add check ("........pg.dropped.1........" > 3);
 851 create table atacc2 (id int4 unique);
 852 alter table atacc1 add foreign key (a) references atacc2(id);
 853 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
 854 alter table atacc2 add foreign key (id) references atacc1(a);
 855 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
 856 drop table atacc2;
 857 create index "testing_idx" on atacc1(a);
 858 create index "testing_idx" on atacc1("........pg.dropped.1........");
 859
 860 -- test create as and select into
 861 insert into atacc1 values (21, 22, 23);
 862 create table test1 as select * from atacc1;
 863 select * from test1;
 864 drop table test1;
 865 select * into test2 from atacc1;
 866 select * from test2;
 867 drop table test2;
 868
 869 -- try dropping all columns
 870 alter table atacc1 drop c;
 871 alter table atacc1 drop d;
 872 alter table atacc1 drop b;
 873 select * from atacc1;
 874
 875 drop table atacc1;
 876
 877 -- test inheritance
 878 create table parent (a int, b int, c int);
 879 insert into parent values (1, 2, 3);
 880 alter table parent drop a;
 881 create table child (d varchar(255)) inherits (parent);
 882 insert into child values (12, 13, 'testing');
 883
 884 select * from parent;
 885 select * from child;
 886 alter table parent drop c;
 887 select * from parent;
 888 select * from child;
 889
 890 drop table child;
 891 drop table parent;
 892
 893 -- test copy in/out
 894 create table test (a int4, b int4, c int4);
 895 insert into test values (1,2,3);
 896 alter table test drop a;
 897 copy test to stdout;
 898 copy test(a) to stdout;
 899 copy test("........pg.dropped.1........") to stdout;
 900 copy test from stdin;
 901 10      11      12
 902 \.
 903 select * from test;
 904 copy test from stdin;
 905 21      22
 906 \.
 907 select * from test;
 908 copy test(a) from stdin;
 909 copy test("........pg.dropped.1........") from stdin;
 910 copy test(b,c) from stdin;
 911 31      32
 912 \.
 913 select * from test;
 914 drop table test;
 915
 916 -- test inheritance
 917
 918 create table dropColumn (a int, b int, e int);
 919 create table dropColumnChild (c int) inherits (dropColumn);
 920 create table dropColumnAnother (d int) inherits (dropColumnChild);
 921
 922 -- these two should fail
 923 alter table dropColumnchild drop column a;
 924 alter table only dropColumnChild drop column b;
 925
 926
 927
 928 -- these three should work
 929 alter table only dropColumn drop column e;
 930 alter table dropColumnChild drop column c;
 931 alter table dropColumn drop column a;
 932
 933 create table renameColumn (a int);
 934 create table renameColumnChild (b int) inherits (renameColumn);
 935 create table renameColumnAnother (c int) inherits (renameColumnChild);
 936
 937 -- these three should fail
 938 alter table renameColumnChild rename column a to d;
 939 alter table only renameColumnChild rename column a to d;
 940 alter table only renameColumn rename column a to d;
 941
 942 -- these should work
 943 alter table renameColumn rename column a to d;
 944 alter table renameColumnChild rename column b to a;
 945
 946 -- these should work
 947 alter table if exists doesnt_exist_tab rename column a to d;
 948 alter table if exists doesnt_exist_tab rename column b to a;
 949
 950 -- this should work
 951 alter table renameColumn add column w int;
 952
 953 -- this should fail
 954 alter table only renameColumn add column x int;
 955
 956
 957 -- Test corner cases in dropping of inherited columns
 958
 959 create table p1 (f1 int, f2 int);
 960 create table c1 (f1 int not null) inherits(p1);
 961
 962 -- should be rejected since c1.f1 is inherited
 963 alter table c1 drop column f1;
 964 -- should work
 965 alter table p1 drop column f1;
 966 -- c1.f1 is still there, but no longer inherited
 967 select f1 from c1;
 968 alter table c1 drop column f1;
 969 select f1 from c1;
 970
 971 drop table p1 cascade;
 972
 973 create table p1 (f1 int, f2 int);
 974 create table c1 () inherits(p1);
 975
 976 -- should be rejected since c1.f1 is inherited
 977 alter table c1 drop column f1;
 978 alter table p1 drop column f1;
 979 -- c1.f1 is dropped now, since there is no local definition for it
 980 select f1 from c1;
 981
 982 drop table p1 cascade;
 983
 984 create table p1 (f1 int, f2 int);
 985 create table c1 () inherits(p1);
 986
 987 -- should be rejected since c1.f1 is inherited
 988 alter table c1 drop column f1;
 989 alter table only p1 drop column f1;
 990 -- c1.f1 is NOT dropped, but must now be considered non-inherited
 991 alter table c1 drop column f1;
 992
 993 drop table p1 cascade;
 994
 995 create table p1 (f1 int, f2 int);
 996 create table c1 (f1 int not null) inherits(p1);
 997
 998 -- should be rejected since c1.f1 is inherited
 999 alter table c1 drop column f1;
1000 alter table only p1 drop column f1;
1001 -- c1.f1 is still there, but no longer inherited
1002 alter table c1 drop column f1;
1003
1004 drop table p1 cascade;
1005
1006 create table p1(id int, name text);
1007 create table p2(id2 int, name text, height int);
1008 create table c1(age int) inherits(p1,p2);
1009 create table gc1() inherits (c1);
1010
1011 select relname, attname, attinhcount, attislocal
1012 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1013 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1014 order by relname, attnum;
1015
1016 -- should work
1017 alter table only p1 drop column name;
1018 -- should work. Now c1.name is local and inhcount is 0.
1019 alter table p2 drop column name;
1020 -- should be rejected since its inherited
1021 alter table gc1 drop column name;
1022 -- should work, and drop gc1.name along
1023 alter table c1 drop column name;
1024 -- should fail: column does not exist
1025 alter table gc1 drop column name;
1026 -- should work and drop the attribute in all tables
1027 alter table p2 drop column height;
1028
1029 -- IF EXISTS test
1030 create table dropColumnExists ();
1031 alter table dropColumnExists drop column non_existing; --fail
1032 alter table dropColumnExists drop column if exists non_existing; --succeed
1033
1034 select relname, attname, attinhcount, attislocal
1035 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1036 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1037 order by relname, attnum;
1038
1039 drop table p1, p2 cascade;
1040
1041 -- test attinhcount tracking with merged columns
1042
1043 create table depth0();
1044 create table depth1(c text) inherits (depth0);
1045 create table depth2() inherits (depth1);
1046 alter table depth0 add c text;
1047
1048 select attrelid::regclass, attname, attinhcount, attislocal
1049 from pg_attribute
1050 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1051 order by attrelid::regclass::text, attnum;
1052
1053 --
1054 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
1055 --
1056 create table altstartwith (col integer) with oids;
1057
1058 insert into altstartwith values (1);
1059
1060 select oid > 0, * from altstartwith;
1061
1062 alter table altstartwith set without oids;
1063
1064 select oid > 0, * from altstartwith; -- fails
1065 select * from altstartwith;
1066
1067 alter table altstartwith set with oids;
1068
1069 select oid > 0, * from altstartwith;
1070
1071 drop table altstartwith;
1072
1073 -- Check inheritance cases
1074 create table altwithoid (col integer) with oids;
1075
1076 -- Inherits parents oid column anyway
1077 create table altinhoid () inherits (altwithoid) without oids;
1078
1079 insert into altinhoid values (1);
1080
1081 select oid > 0, * from altwithoid;
1082 select oid > 0, * from altinhoid;
1083
1084 alter table altwithoid set without oids;
1085
1086 select oid > 0, * from altwithoid; -- fails
1087 select oid > 0, * from altinhoid; -- fails
1088 select * from altwithoid;
1089 select * from altinhoid;
1090
1091 alter table altwithoid set with oids;
1092
1093 select oid > 0, * from altwithoid;
1094 select oid > 0, * from altinhoid;
1095
1096 drop table altwithoid cascade;
1097
1098 create table altwithoid (col integer) without oids;
1099
1100 -- child can have local oid column
1101 create table altinhoid () inherits (altwithoid) with oids;
1102
1103 insert into altinhoid values (1);
1104
1105 select oid > 0, * from altwithoid; -- fails
1106 select oid > 0, * from altinhoid;
1107
1108 alter table altwithoid set with oids;
1109
1110 select oid > 0, * from altwithoid;
1111 select oid > 0, * from altinhoid;
1112
1113 -- the child's local definition should remain
1114 alter table altwithoid set without oids;
1115
1116 select oid > 0, * from altwithoid; -- fails
1117 select oid > 0, * from altinhoid;
1118
1119 drop table altwithoid cascade;
1120
1121 -- test renumbering of child-table columns in inherited operations
1122
1123 create table p1 (f1 int);
1124 create table c1 (f2 text, f3 int) inherits (p1);
1125
1126 alter table p1 add column a1 int check (a1 > 0);
1127 alter table p1 add column f2 text;
1128
1129 insert into p1 values (1,2,'abc');
1130 insert into c1 values(11,'xyz',33,0); -- should fail
1131 insert into c1 values(11,'xyz',33,22);
1132
1133 select * from p1;
1134 update p1 set a1 = a1 + 1, f2 = upper(f2);
1135 select * from p1;
1136
1137 drop table p1 cascade;
1138
1139 -- test that operations with a dropped column do not try to reference
1140 -- its datatype
1141
1142 create domain mytype as text;
1143 create temp table foo (f1 text, f2 mytype, f3 text);
1144
1145 insert into foo values('bb','cc','dd');
1146 select * from foo;
1147
1148 drop domain mytype cascade;
1149
1150 select * from foo;
1151 insert into foo values('qq','rr');
1152 select * from foo;
1153 update foo set f3 = 'zz';
1154 select * from foo;
1155 select f3,max(f1) from foo group by f3;
1156
1157 -- Simple tests for alter table column type
1158 alter table foo alter f1 TYPE integer; -- fails
1159 alter table foo alter f1 TYPE varchar(10);
1160
1161 create table anothertab (atcol1 serial8, atcol2 boolean,
1162         constraint anothertab_chk check (atcol1 <= 3));
1163
1164 insert into anothertab (atcol1, atcol2) values (default, true);
1165 insert into anothertab (atcol1, atcol2) values (default, false);
1166 select * from anothertab;
1167
1168 alter table anothertab alter column atcol1 type boolean; -- fails
1169 alter table anothertab alter column atcol1 type integer;
1170
1171 select * from anothertab;
1172
1173 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1174 insert into anothertab (atcol1, atcol2) values (default, null);
1175
1176 select * from anothertab;
1177
1178 alter table anothertab alter column atcol2 type text
1179       using case when atcol2 is true then 'IT WAS TRUE'
1180                  when atcol2 is false then 'IT WAS FALSE'
1181                  else 'IT WAS NULL!' end;
1182
1183 select * from anothertab;
1184 alter table anothertab alter column atcol1 type boolean
1185         using case when atcol1 % 2 = 0 then true else false end; -- fails
1186 alter table anothertab alter column atcol1 drop default;
1187 alter table anothertab alter column atcol1 type boolean
1188         using case when atcol1 % 2 = 0 then true else false end; -- fails
1189 alter table anothertab drop constraint anothertab_chk;
1190 alter table anothertab drop constraint anothertab_chk; -- fails
1191 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1192
1193 alter table anothertab alter column atcol1 type boolean
1194         using case when atcol1 % 2 = 0 then true else false end;
1195
1196 select * from anothertab;
1197
1198 drop table anothertab;
1199
1200 create table another (f1 int, f2 text);
1201
1202 insert into another values(1, 'one');
1203 insert into another values(2, 'two');
1204 insert into another values(3, 'three');
1205
1206 select * from another;
1207
1208 alter table another
1209   alter f1 type text using f2 || ' more',
1210   alter f2 type bigint using f1 * 10;
1211
1212 select * from another;
1213
1214 drop table another;
1215
1216 -- table's row type
1217 create table tab1 (a int, b text);
1218 create table tab2 (x int, y tab1);
1219 alter table tab1 alter column b type varchar; -- fails
1220
1221 -- disallow recursive containment of row types
1222 create temp table recur1 (f1 int);
1223 alter table recur1 add column f2 recur1; -- fails
1224 alter table recur1 add column f2 recur1[]; -- fails
1225 create domain array_of_recur1 as recur1[];
1226 alter table recur1 add column f2 array_of_recur1; -- fails
1227 create temp table recur2 (f1 int, f2 recur1);
1228 alter table recur1 add column f2 recur2; -- fails
1229 alter table recur1 add column f2 int;
1230 alter table recur1 alter column f2 type recur2; -- fails
1231
1232 -- SET STORAGE may need to add a TOAST table
1233 create table test_storage (a text);
1234 alter table test_storage alter a set storage plain;
1235 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1236 alter table test_storage alter a set storage extended; -- re-add TOAST table
1237
1238 select reltoastrelid <> 0 as has_toast_table
1239 from pg_class
1240 where oid = 'test_storage'::regclass;
1241
1242 --
1243 -- lock levels
1244 --
1245 drop type lockmodes;
1246 create type lockmodes as enum (
1247  'AccessShareLock'
1248 ,'RowShareLock'
1249 ,'RowExclusiveLock'
1250 ,'ShareUpdateExclusiveLock'
1251 ,'ShareLock'
1252 ,'ShareRowExclusiveLock'
1253 ,'ExclusiveLock'
1254 ,'AccessExclusiveLock'
1255 );
1256
1257 drop view my_locks;
1258 create or replace view my_locks as
1259 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1260 from pg_locks l join pg_class c on l.relation = c.oid
1261 where virtualtransaction = (
1262         select virtualtransaction
1263         from pg_locks
1264         where transactionid = txid_current()::integer)
1265 and locktype = 'relation'
1266 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1267 and c.relname != 'my_locks'
1268 group by c.relname;
1269
1270 create table alterlock (f1 int primary key, f2 text);
1271
1272 begin; alter table alterlock alter column f2 set statistics 150;
1273 select * from my_locks order by 1;
1274 rollback;
1275
1276 begin; alter table alterlock cluster on alterlock_pkey;
1277 select * from my_locks order by 1;
1278 commit;
1279
1280 begin; alter table alterlock set without cluster;
1281 select * from my_locks order by 1;
1282 commit;
1283
1284 begin; alter table alterlock set (fillfactor = 100);
1285 select * from my_locks order by 1;
1286 commit;
1287
1288 begin; alter table alterlock reset (fillfactor);
1289 select * from my_locks order by 1;
1290 commit;
1291
1292 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1293 select * from my_locks order by 1;
1294 commit;
1295
1296 begin; alter table alterlock set (autovacuum_enabled = off);
1297 select * from my_locks order by 1;
1298 commit;
1299
1300 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1301 select * from my_locks order by 1;
1302 rollback;
1303
1304 begin; alter table alterlock alter column f2 set storage extended;
1305 select * from my_locks order by 1;
1306 rollback;
1307
1308 begin; alter table alterlock alter column f2 set default 'x';
1309 select * from my_locks order by 1;
1310 rollback;
1311
1312 -- cleanup
1313 drop table alterlock;
1314 drop view my_locks;
1315 drop type lockmodes;
1316
1317 --
1318 -- alter function
1319 --
1320 create function test_strict(text) returns text as
1321     'select coalesce($1, ''got passed a null'');'
1322     language sql returns null on null input;
1323 select test_strict(NULL);
1324 alter function test_strict(text) called on null input;
1325 select test_strict(NULL);
1326
1327 create function non_strict(text) returns text as
1328     'select coalesce($1, ''got passed a null'');'
1329     language sql called on null input;
1330 select non_strict(NULL);
1331 alter function non_strict(text) returns null on null input;
1332 select non_strict(NULL);
1333
1334 --
1335 -- alter object set schema
1336 --
1337
1338 create schema alter1;
1339 create schema alter2;
1340
1341 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1342
1343 create view alter1.v1 as select * from alter1.t1;
1344
1345 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1346
1347 create domain alter1.posint integer check (value > 0);
1348
1349 create type alter1.ctype as (f1 int, f2 text);
1350
1351 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1352 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1353
1354 create operator alter1.=(procedure = alter1.same, leftarg  = alter1.ctype, rightarg = alter1.ctype);
1355
1356 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1357   operator 1 alter1.=(alter1.ctype, alter1.ctype);
1358
1359 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1360
1361 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1362 create text search configuration alter1.cfg(parser = alter1.prs);
1363 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1364 create text search dictionary alter1.dict(template = alter1.tmpl);
1365
1366 insert into alter1.t1(f2) values(11);
1367 insert into alter1.t1(f2) values(12);
1368
1369 alter table alter1.t1 set schema alter2;
1370 alter table alter1.v1 set schema alter2;
1371 alter function alter1.plus1(int) set schema alter2;
1372 alter domain alter1.posint set schema alter2;
1373 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1374 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1375 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1376 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1377 alter type alter1.ctype set schema alter2;
1378 alter conversion alter1.ascii_to_utf8 set schema alter2;
1379 alter text search parser alter1.prs set schema alter2;
1380 alter text search configuration alter1.cfg set schema alter2;
1381 alter text search template alter1.tmpl set schema alter2;
1382 alter text search dictionary alter1.dict set schema alter2;
1383
1384 -- this should succeed because nothing is left in alter1
1385 drop schema alter1;
1386
1387 insert into alter2.t1(f2) values(13);
1388 insert into alter2.t1(f2) values(14);
1389
1390 select * from alter2.t1;
1391
1392 select * from alter2.v1;
1393
1394 select alter2.plus1(41);
1395
1396 -- clean up
1397 drop schema alter2 cascade;
1398
1399 --
1400 -- composite types
1401 --
1402
1403 CREATE TYPE test_type AS (a int);
1404 \d test_type
1405
1406 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1407
1408 ALTER TYPE test_type ADD ATTRIBUTE b text;
1409 \d test_type
1410
1411 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1412
1413 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1414 \d test_type
1415
1416 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1417 \d test_type
1418
1419 ALTER TYPE test_type DROP ATTRIBUTE b;
1420 \d test_type
1421
1422 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1423
1424 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1425
1426 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1427 \d test_type
1428
1429 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1430 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1431 \d test_type
1432
1433 DROP TYPE test_type;
1434
1435 CREATE TYPE test_type1 AS (a int, b text);
1436 CREATE TABLE test_tbl1 (x int, y test_type1);
1437 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1438
1439 CREATE TYPE test_type2 AS (a int, b text);
1440 CREATE TABLE test_tbl2 OF test_type2;
1441 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
1442 \d test_type2
1443 \d test_tbl2
1444
1445 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1446 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1447 \d test_type2
1448 \d test_tbl2
1449
1450 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1451 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1452 \d test_type2
1453 \d test_tbl2
1454
1455 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1456 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
1457 \d test_type2
1458 \d test_tbl2
1459
1460 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
1461 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
1462 \d test_type2
1463 \d test_tbl2
1464 \d test_tbl2_subclass
1465
1466 DROP TABLE test_tbl2_subclass;
1467
1468 -- This test isn't that interesting on its own, but the purpose is to leave
1469 -- behind a table to test pg_upgrade with. The table has a composite type
1470 -- column in it, and the composite type has a dropped attribute.
1471 CREATE TYPE test_type3 AS (a int);
1472 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
1473 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
1474
1475 CREATE TYPE test_type_empty AS ();
1476 DROP TYPE test_type_empty;
1477
1478 --
1479 -- typed tables: OF / NOT OF
1480 --
1481
1482 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
1483 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
1484 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2));      -- OK
1485 CREATE TABLE tt1 (x int, y bigint);                                     -- wrong base type
1486 CREATE TABLE tt2 (x int, y numeric(9,2));                       -- wrong typmod
1487 CREATE TABLE tt3 (y numeric(8,2), x int);                       -- wrong column order
1488 CREATE TABLE tt4 (x int);                                                       -- too few columns
1489 CREATE TABLE tt5 (x int, y numeric(8,2), z int);        -- too few columns
1490 CREATE TABLE tt6 () INHERITS (tt0);                                     -- can't have a parent
1491 CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
1492 ALTER TABLE tt7 DROP q;                                                         -- OK
1493
1494 ALTER TABLE tt0 OF tt_t0;
1495 ALTER TABLE tt1 OF tt_t0;
1496 ALTER TABLE tt2 OF tt_t0;
1497 ALTER TABLE tt3 OF tt_t0;
1498 ALTER TABLE tt4 OF tt_t0;
1499 ALTER TABLE tt5 OF tt_t0;
1500 ALTER TABLE tt6 OF tt_t0;
1501 ALTER TABLE tt7 OF tt_t0;
1502
1503 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
1504 ALTER TABLE tt7 OF tt_t1;                       -- reassign an already-typed table
1505 ALTER TABLE tt7 NOT OF;
1506 \d tt7
1507
1508 -- make sure we can drop a constraint on the parent but it remains on the child
1509 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
1510 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
1511 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
1512 -- should fail
1513 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
1514 DROP TABLE test_drop_constr_parent CASCADE;
1515
1516 --
1517 -- IF EXISTS test
1518 --
1519 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
1520 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
1521 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
1522 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
1523 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
1524 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
1525
1526 CREATE TABLE tt8(a int);
1527 CREATE SCHEMA alter2;
1528
1529 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
1530 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
1531 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
1532 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
1533 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
1534 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
1535
1536 \d alter2.tt8
1537
1538 DROP TABLE alter2.tt8;
1539 DROP SCHEMA alter2;