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 a int4 default 3;
  13
  14 ALTER TABLE tmp ADD COLUMN b name;
  15
  16 ALTER TABLE tmp ADD COLUMN c text;
  17
  18 ALTER TABLE tmp ADD COLUMN d float8;
  19
  20 ALTER TABLE tmp ADD COLUMN e float4;
  21
  22 ALTER TABLE tmp ADD COLUMN f int2;
  23
  24 ALTER TABLE tmp ADD COLUMN g polygon;
  25
  26 ALTER TABLE tmp ADD COLUMN h abstime;
  27
  28 ALTER TABLE tmp ADD COLUMN i char;
  29
  30 ALTER TABLE tmp ADD COLUMN j abstime[];
  31
  32 ALTER TABLE tmp ADD COLUMN k int4;
  33
  34 ALTER TABLE tmp ADD COLUMN l tid;
  35
  36 ALTER TABLE tmp ADD COLUMN m xid;
  37
  38 ALTER TABLE tmp ADD COLUMN n oidvector;
  39
  40 --ALTER TABLE tmp ADD COLUMN o lock;
  41 ALTER TABLE tmp ADD COLUMN p smgr;
  42
  43 ALTER TABLE tmp ADD COLUMN q point;
  44
  45 ALTER TABLE tmp ADD COLUMN r lseg;
  46
  47 ALTER TABLE tmp ADD COLUMN s path;
  48
  49 ALTER TABLE tmp ADD COLUMN t box;
  50
  51 ALTER TABLE tmp ADD COLUMN u tinterval;
  52
  53 ALTER TABLE tmp ADD COLUMN v timestamp;
  54
  55 ALTER TABLE tmp ADD COLUMN w interval;
  56
  57 ALTER TABLE tmp ADD COLUMN x float8[];
  58
  59 ALTER TABLE tmp ADD COLUMN y float4[];
  60
  61 ALTER TABLE tmp ADD COLUMN z int2[];
  62
  63 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  64         v, w, x, y, z)
  65    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
  66         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
  67         314159, '(1,1)', '512',
  68         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
  69         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
  70         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
  71
  72 SELECT * FROM tmp;
  73
  74 DROP TABLE tmp;
  75
  76 -- the wolf bug - schema mods caused inconsistent row descriptors
  77 CREATE TABLE tmp (
  78         initial         int4
  79 );
  80
  81 ALTER TABLE tmp ADD COLUMN a int4;
  82
  83 ALTER TABLE tmp ADD COLUMN b name;
  84
  85 ALTER TABLE tmp ADD COLUMN c text;
  86
  87 ALTER TABLE tmp ADD COLUMN d float8;
  88
  89 ALTER TABLE tmp ADD COLUMN e float4;
  90
  91 ALTER TABLE tmp ADD COLUMN f int2;
  92
  93 ALTER TABLE tmp ADD COLUMN g polygon;
  94
  95 ALTER TABLE tmp ADD COLUMN h abstime;
  96
  97 ALTER TABLE tmp ADD COLUMN i char;
  98
  99 ALTER TABLE tmp ADD COLUMN j abstime[];
 100
 101 ALTER TABLE tmp ADD COLUMN k int4;
 102
 103 ALTER TABLE tmp ADD COLUMN l tid;
 104
 105 ALTER TABLE tmp ADD COLUMN m xid;
 106
 107 ALTER TABLE tmp ADD COLUMN n oidvector;
 108
 109 --ALTER TABLE tmp ADD COLUMN o lock;
 110 ALTER TABLE tmp ADD COLUMN p smgr;
 111
 112 ALTER TABLE tmp ADD COLUMN q point;
 113
 114 ALTER TABLE tmp ADD COLUMN r lseg;
 115
 116 ALTER TABLE tmp ADD COLUMN s path;
 117
 118 ALTER TABLE tmp ADD COLUMN t box;
 119
 120 ALTER TABLE tmp ADD COLUMN u tinterval;
 121
 122 ALTER TABLE tmp ADD COLUMN v timestamp;
 123
 124 ALTER TABLE tmp ADD COLUMN w interval;
 125
 126 ALTER TABLE tmp ADD COLUMN x float8[];
 127
 128 ALTER TABLE tmp ADD COLUMN y float4[];
 129
 130 ALTER TABLE tmp ADD COLUMN z int2[];
 131
 132 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
 133         v, w, x, y, z)
 134    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
 135         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
 136         314159, '(1,1)', '512',
 137         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
 138         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
 139         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
 140
 141 SELECT * FROM tmp;
 142
 143 DROP TABLE tmp;
 144
 145
 146 --
 147 -- rename - check on both non-temp and temp tables
 148 --
 149 CREATE TABLE tmp (regtable int);
 150 CREATE TEMP TABLE tmp (tmptable int);
 151
 152 ALTER TABLE tmp RENAME TO tmp_new;
 153
 154 SELECT * FROM tmp;
 155 SELECT * FROM tmp_new;
 156
 157 ALTER TABLE tmp RENAME TO tmp_new2;
 158
 159 SELECT * FROM tmp;              -- should fail
 160 SELECT * FROM tmp_new;
 161 SELECT * FROM tmp_new2;
 162
 163 DROP TABLE tmp_new;
 164 DROP TABLE tmp_new2;
 165
 166
 167 -- ALTER TABLE ... RENAME on non-table relations
 168 -- renaming indexes (FIXME: this should probably test the index's functionality)
 169 ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
 170 ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
 171 -- renaming views
 172 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
 173 ALTER TABLE tmp_view RENAME TO tmp_view_new;
 174
 175 -- hack to ensure we get an indexscan here
 176 ANALYZE tenk1;
 177 set enable_seqscan to off;
 178 set enable_bitmapscan to off;
 179 -- 5 values, sorted
 180 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 181 reset enable_seqscan;
 182 reset enable_bitmapscan;
 183
 184 DROP VIEW tmp_view_new;
 185 -- toast-like relation name
 186 alter table stud_emp rename to pg_toast_stud_emp;
 187 alter table pg_toast_stud_emp rename to stud_emp;
 188
 189 -- FOREIGN KEY CONSTRAINT adding TEST
 190
 191 CREATE TABLE tmp2 (a int primary key);
 192
 193 CREATE TABLE tmp3 (a int, b int);
 194
 195 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 196
 197 CREATE TABLE tmp5 (a int, b int);
 198
 199 -- Insert rows into tmp2 (pktable)
 200 INSERT INTO tmp2 values (1);
 201 INSERT INTO tmp2 values (2);
 202 INSERT INTO tmp2 values (3);
 203 INSERT INTO tmp2 values (4);
 204
 205 -- Insert rows into tmp3
 206 INSERT INTO tmp3 values (1,10);
 207 INSERT INTO tmp3 values (1,20);
 208 INSERT INTO tmp3 values (5,50);
 209
 210 -- Try (and fail) to add constraint due to invalid source columns
 211 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
 212
 213 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
 214 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
 215
 216 -- Try (and fail) to add constraint due to invalid data
 217 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 218
 219 -- Delete failing row
 220 DELETE FROM tmp3 where a=5;
 221
 222 -- Try (and succeed)
 223 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 224
 225 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 226 -- tmp4 is a,b
 227
 228 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 229
 230 DROP TABLE tmp5;
 231
 232 DROP TABLE tmp4;
 233
 234 DROP TABLE tmp3;
 235
 236 DROP TABLE tmp2;
 237
 238 -- Foreign key adding test with mixed types
 239
 240 -- Note: these tables are TEMP to avoid name conflicts when this test
 241 -- is run in parallel with foreign_key.sql.
 242
 243 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 244 INSERT INTO PKTABLE VALUES(42);
 245 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 246 -- This next should fail, because int=inet does not exist
 247 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 248 -- This should also fail for the same reason, but here we
 249 -- give the column name
 250 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 251 DROP TABLE FKTABLE;
 252 -- This should succeed, even though they are different types,
 253 -- because int=int8 exists and is a member of the integer opfamily
 254 CREATE TEMP TABLE FKTABLE (ftest1 int8);
 255 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 256 -- Check it actually works
 257 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 258 INSERT INTO FKTABLE VALUES(43);         -- should fail
 259 DROP TABLE FKTABLE;
 260 -- This should fail, because we'd have to cast numeric to int which is
 261 -- not an implicit coercion (or use numeric=numeric, but that's not part
 262 -- of the integer opfamily)
 263 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
 264 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 265 DROP TABLE FKTABLE;
 266 DROP TABLE PKTABLE;
 267 -- On the other hand, this should work because int implicitly promotes to
 268 -- numeric, and we allow promotion on the FK side
 269 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
 270 INSERT INTO PKTABLE VALUES(42);
 271 CREATE TEMP TABLE FKTABLE (ftest1 int);
 272 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 273 -- Check it actually works
 274 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 275 INSERT INTO FKTABLE VALUES(43);         -- should fail
 276 DROP TABLE FKTABLE;
 277 DROP TABLE PKTABLE;
 278
 279 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 280                            PRIMARY KEY(ptest1, ptest2));
 281 -- This should fail, because we just chose really odd types
 282 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 283 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 284 DROP TABLE FKTABLE;
 285 -- Again, so should this...
 286 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 287 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 288      references pktable(ptest1, ptest2);
 289 DROP TABLE FKTABLE;
 290 -- This fails because we mixed up the column ordering
 291 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 292 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 293      references pktable(ptest2, ptest1);
 294 -- As does this...
 295 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 296      references pktable(ptest1, ptest2);
 297
 298 -- temp tables should go away by themselves, need not drop them.
 299
 300 -- test check constraint adding
 301
 302 create table atacc1 ( test int );
 303 -- add a check constraint
 304 alter table atacc1 add constraint atacc_test1 check (test>3);
 305 -- should fail
 306 insert into atacc1 (test) values (2);
 307 -- should succeed
 308 insert into atacc1 (test) values (4);
 309 drop table atacc1;
 310
 311 -- let's do one where the check fails when added
 312 create table atacc1 ( test int );
 313 -- insert a soon to be failing row
 314 insert into atacc1 (test) values (2);
 315 -- add a check constraint (fails)
 316 alter table atacc1 add constraint atacc_test1 check (test>3);
 317 insert into atacc1 (test) values (4);
 318 drop table atacc1;
 319
 320 -- let's do one where the check fails because the column doesn't exist
 321 create table atacc1 ( test int );
 322 -- add a check constraint (fails)
 323 alter table atacc1 add constraint atacc_test1 check (test1>3);
 324 drop table atacc1;
 325
 326 -- something a little more complicated
 327 create table atacc1 ( test int, test2 int, test3 int);
 328 -- add a check constraint (fails)
 329 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 330 -- should fail
 331 insert into atacc1 (test,test2,test3) values (4,4,2);
 332 -- should succeed
 333 insert into atacc1 (test,test2,test3) values (4,4,5);
 334 drop table atacc1;
 335
 336 -- lets do some naming tests
 337 create table atacc1 (test int check (test>3), test2 int);
 338 alter table atacc1 add check (test2>test);
 339 -- should fail for $2
 340 insert into atacc1 (test2, test) values (3, 4);
 341 drop table atacc1;
 342
 343 -- inheritance related tests
 344 create table atacc1 (test int);
 345 create table atacc2 (test2 int);
 346 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 347 alter table atacc2 add constraint foo check (test2>0);
 348 -- fail and then succeed on atacc2
 349 insert into atacc2 (test2) values (-3);
 350 insert into atacc2 (test2) values (3);
 351 -- fail and then succeed on atacc3
 352 insert into atacc3 (test2) values (-3);
 353 insert into atacc3 (test2) values (3);
 354 drop table atacc3;
 355 drop table atacc2;
 356 drop table atacc1;
 357
 358 -- same things with one created with INHERIT
 359 create table atacc1 (test int);
 360 create table atacc2 (test2 int);
 361 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 362 alter table atacc3 no inherit atacc2;
 363 -- fail
 364 alter table atacc3 no inherit atacc2;
 365 -- make sure it really isn't a child
 366 insert into atacc3 (test2) values (3);
 367 select test2 from atacc2;
 368 -- fail due to missing constraint
 369 alter table atacc2 add constraint foo check (test2>0);
 370 alter table atacc3 inherit atacc2;
 371 -- fail due to missing column
 372 alter table atacc3 rename test2 to testx;
 373 alter table atacc3 inherit atacc2;
 374 -- fail due to mismatched data type
 375 alter table atacc3 add test2 bool;
 376 alter table atacc3 inherit atacc2;
 377 alter table atacc3 drop test2;
 378 -- succeed
 379 alter table atacc3 add test2 int;
 380 update atacc3 set test2 = 4 where test2 is null;
 381 alter table atacc3 add constraint foo check (test2>0);
 382 alter table atacc3 inherit atacc2;
 383 -- fail due to duplicates and circular inheritance
 384 alter table atacc3 inherit atacc2;
 385 alter table atacc2 inherit atacc3;
 386 alter table atacc2 inherit atacc2;
 387 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
 388 select test2 from atacc2;
 389 drop table atacc2 cascade;
 390 drop table atacc1;
 391
 392 -- adding only to a parent is disallowed as of 8.4
 393
 394 create table atacc1 (test int);
 395 create table atacc2 (test2 int) inherits (atacc1);
 396 -- fail:
 397 alter table only atacc1 add constraint foo check (test>0);
 398 -- ok:
 399 alter table only atacc2 add constraint foo check (test>0);
 400 -- check constraint not there on parent
 401 insert into atacc1 (test) values (-3);
 402 insert into atacc1 (test) values (3);
 403 -- check constraint is there on child
 404 insert into atacc2 (test) values (-3);
 405 insert into atacc2 (test) values (3);
 406 drop table atacc2;
 407 drop table atacc1;
 408
 409 -- test unique constraint adding
 410
 411 create table atacc1 ( test int ) with oids;
 412 -- add a unique constraint
 413 alter table atacc1 add constraint atacc_test1 unique (test);
 414 -- insert first value
 415 insert into atacc1 (test) values (2);
 416 -- should fail
 417 insert into atacc1 (test) values (2);
 418 -- should succeed
 419 insert into atacc1 (test) values (4);
 420 -- try adding a unique oid constraint
 421 alter table atacc1 add constraint atacc_oid1 unique(oid);
 422 drop table atacc1;
 423
 424 -- let's do one where the unique constraint fails when added
 425 create table atacc1 ( test int );
 426 -- insert soon to be failing rows
 427 insert into atacc1 (test) values (2);
 428 insert into atacc1 (test) values (2);
 429 -- add a unique constraint (fails)
 430 alter table atacc1 add constraint atacc_test1 unique (test);
 431 insert into atacc1 (test) values (3);
 432 drop table atacc1;
 433
 434 -- let's do one where the unique constraint fails
 435 -- because the column doesn't exist
 436 create table atacc1 ( test int );
 437 -- add a unique constraint (fails)
 438 alter table atacc1 add constraint atacc_test1 unique (test1);
 439 drop table atacc1;
 440
 441 -- something a little more complicated
 442 create table atacc1 ( test int, test2 int);
 443 -- add a unique constraint
 444 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 445 -- insert initial value
 446 insert into atacc1 (test,test2) values (4,4);
 447 -- should fail
 448 insert into atacc1 (test,test2) values (4,4);
 449 -- should all succeed
 450 insert into atacc1 (test,test2) values (4,5);
 451 insert into atacc1 (test,test2) values (5,4);
 452 insert into atacc1 (test,test2) values (5,5);
 453 drop table atacc1;
 454
 455 -- lets do some naming tests
 456 create table atacc1 (test int, test2 int, unique(test));
 457 alter table atacc1 add unique (test2);
 458 -- should fail for @@ second one @@
 459 insert into atacc1 (test2, test) values (3, 3);
 460 insert into atacc1 (test2, test) values (2, 3);
 461 drop table atacc1;
 462
 463 -- test primary key constraint adding
 464
 465 create table atacc1 ( test int ) with oids;
 466 -- add a primary key constraint
 467 alter table atacc1 add constraint atacc_test1 primary key (test);
 468 -- insert first value
 469 insert into atacc1 (test) values (2);
 470 -- should fail
 471 insert into atacc1 (test) values (2);
 472 -- should succeed
 473 insert into atacc1 (test) values (4);
 474 -- inserting NULL should fail
 475 insert into atacc1 (test) values(NULL);
 476 -- try adding a second primary key (should fail)
 477 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 478 -- drop first primary key constraint
 479 alter table atacc1 drop constraint atacc_test1 restrict;
 480 -- try adding a primary key on oid (should succeed)
 481 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 482 drop table atacc1;
 483
 484 -- let's do one where the primary key constraint fails when added
 485 create table atacc1 ( test int );
 486 -- insert soon to be failing rows
 487 insert into atacc1 (test) values (2);
 488 insert into atacc1 (test) values (2);
 489 -- add a primary key (fails)
 490 alter table atacc1 add constraint atacc_test1 primary key (test);
 491 insert into atacc1 (test) values (3);
 492 drop table atacc1;
 493
 494 -- let's do another one where the primary key constraint fails when added
 495 create table atacc1 ( test int );
 496 -- insert soon to be failing row
 497 insert into atacc1 (test) values (NULL);
 498 -- add a primary key (fails)
 499 alter table atacc1 add constraint atacc_test1 primary key (test);
 500 insert into atacc1 (test) values (3);
 501 drop table atacc1;
 502
 503 -- let's do one where the primary key constraint fails
 504 -- because the column doesn't exist
 505 create table atacc1 ( test int );
 506 -- add a primary key constraint (fails)
 507 alter table atacc1 add constraint atacc_test1 primary key (test1);
 508 drop table atacc1;
 509
 510 -- adding a new column as primary key to a non-empty table.
 511 -- should fail unless the column has a non-null default value.
 512 create table atacc1 ( test int );
 513 insert into atacc1 (test) values (0);
 514 -- add a primary key column without a default (fails).
 515 alter table atacc1 add column test2 int primary key;
 516 -- now add a primary key column with a default (succeeds).
 517 alter table atacc1 add column test2 int default 0 primary key;
 518 drop table atacc1;
 519
 520 -- something a little more complicated
 521 create table atacc1 ( test int, test2 int);
 522 -- add a primary key constraint
 523 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 524 -- try adding a second primary key - should fail
 525 alter table atacc1 add constraint atacc_test2 primary key (test);
 526 -- insert initial value
 527 insert into atacc1 (test,test2) values (4,4);
 528 -- should fail
 529 insert into atacc1 (test,test2) values (4,4);
 530 insert into atacc1 (test,test2) values (NULL,3);
 531 insert into atacc1 (test,test2) values (3, NULL);
 532 insert into atacc1 (test,test2) values (NULL,NULL);
 533 -- should all succeed
 534 insert into atacc1 (test,test2) values (4,5);
 535 insert into atacc1 (test,test2) values (5,4);
 536 insert into atacc1 (test,test2) values (5,5);
 537 drop table atacc1;
 538
 539 -- lets do some naming tests
 540 create table atacc1 (test int, test2 int, primary key(test));
 541 -- only first should succeed
 542 insert into atacc1 (test2, test) values (3, 3);
 543 insert into atacc1 (test2, test) values (2, 3);
 544 insert into atacc1 (test2, test) values (1, NULL);
 545 drop table atacc1;
 546
 547 -- alter table / alter column [set/drop] not null tests
 548 -- try altering system catalogs, should fail
 549 alter table pg_class alter column relname drop not null;
 550 alter table pg_class alter relname set not null;
 551
 552 -- try altering non-existent table, should fail
 553 alter table non_existent alter column bar set not null;
 554 alter table non_existent alter column bar drop not null;
 555
 556 -- test setting columns to null and not null and vice versa
 557 -- test checking for null values and primary key
 558 create table atacc1 (test int not null) with oids;
 559 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 560 alter table atacc1 alter column test drop not null;
 561 alter table atacc1 drop constraint "atacc1_pkey";
 562 alter table atacc1 alter column test drop not null;
 563 insert into atacc1 values (null);
 564 alter table atacc1 alter test set not null;
 565 delete from atacc1;
 566 alter table atacc1 alter test set not null;
 567
 568 -- try altering a non-existent column, should fail
 569 alter table atacc1 alter bar set not null;
 570 alter table atacc1 alter bar drop not null;
 571
 572 -- try altering the oid column, should fail
 573 alter table atacc1 alter oid set not null;
 574 alter table atacc1 alter oid drop not null;
 575
 576 -- try creating a view and altering that, should fail
 577 create view myview as select * from atacc1;
 578 alter table myview alter column test drop not null;
 579 alter table myview alter column test set not null;
 580 drop view myview;
 581
 582 drop table atacc1;
 583
 584 -- test inheritance
 585 create table parent (a int);
 586 create table child (b varchar(255)) inherits (parent);
 587
 588 alter table parent alter a set not null;
 589 insert into parent values (NULL);
 590 insert into child (a, b) values (NULL, 'foo');
 591 alter table parent alter a drop not null;
 592 insert into parent values (NULL);
 593 insert into child (a, b) values (NULL, 'foo');
 594 alter table only parent alter a set not null;
 595 alter table child alter a set not null;
 596 delete from parent;
 597 alter table only parent alter a set not null;
 598 insert into parent values (NULL);
 599 alter table child alter a set not null;
 600 insert into child (a, b) values (NULL, 'foo');
 601 delete from child;
 602 alter table child alter a set not null;
 603 insert into child (a, b) values (NULL, 'foo');
 604 drop table child;
 605 drop table parent;
 606
 607 -- test setting and removing default values
 608 create table def_test (
 609         c1      int4 default 5,
 610         c2      text default 'initial_default'
 611 );
 612 insert into def_test default values;
 613 alter table def_test alter column c1 drop default;
 614 insert into def_test default values;
 615 alter table def_test alter column c2 drop default;
 616 insert into def_test default values;
 617 alter table def_test alter column c1 set default 10;
 618 alter table def_test alter column c2 set default 'new_default';
 619 insert into def_test default values;
 620 select * from def_test;
 621
 622 -- set defaults to an incorrect type: this should fail
 623 alter table def_test alter column c1 set default 'wrong_datatype';
 624 alter table def_test alter column c2 set default 20;
 625
 626 -- set defaults on a non-existent column: this should fail
 627 alter table def_test alter column c3 set default 30;
 628
 629 -- set defaults on views: we need to create a view, add a rule
 630 -- to allow insertions into it, and then alter the view to add
 631 -- a default
 632 create view def_view_test as select * from def_test;
 633 create rule def_view_test_ins as
 634         on insert to def_view_test
 635         do instead insert into def_test select new.*;
 636 insert into def_view_test default values;
 637 alter table def_view_test alter column c1 set default 45;
 638 insert into def_view_test default values;
 639 alter table def_view_test alter column c2 set default 'view_default';
 640 insert into def_view_test default values;
 641 select * from def_view_test;
 642
 643 drop rule def_view_test_ins on def_view_test;
 644 drop view def_view_test;
 645 drop table def_test;
 646
 647 -- alter table / drop column tests
 648 -- try altering system catalogs, should fail
 649 alter table pg_class drop column relname;
 650
 651 -- try altering non-existent table, should fail
 652 alter table nosuchtable drop column bar;
 653
 654 -- test dropping columns
 655 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
 656 insert into atacc1 values (1, 2, 3, 4);
 657 alter table atacc1 drop a;
 658 alter table atacc1 drop a;
 659
 660 -- SELECTs
 661 select * from atacc1;
 662 select * from atacc1 order by a;
 663 select * from atacc1 order by "........pg.dropped.1........";
 664 select * from atacc1 group by a;
 665 select * from atacc1 group by "........pg.dropped.1........";
 666 select atacc1.* from atacc1;
 667 select a from atacc1;
 668 select atacc1.a from atacc1;
 669 select b,c,d from atacc1;
 670 select a,b,c,d from atacc1;
 671 select * from atacc1 where a = 1;
 672 select "........pg.dropped.1........" from atacc1;
 673 select atacc1."........pg.dropped.1........" from atacc1;
 674 select "........pg.dropped.1........",b,c,d from atacc1;
 675 select * from atacc1 where "........pg.dropped.1........" = 1;
 676
 677 -- UPDATEs
 678 update atacc1 set a = 3;
 679 update atacc1 set b = 2 where a = 3;
 680 update atacc1 set "........pg.dropped.1........" = 3;
 681 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
 682
 683 -- INSERTs
 684 insert into atacc1 values (10, 11, 12, 13);
 685 insert into atacc1 values (default, 11, 12, 13);
 686 insert into atacc1 values (11, 12, 13);
 687 insert into atacc1 (a) values (10);
 688 insert into atacc1 (a) values (default);
 689 insert into atacc1 (a,b,c,d) values (10,11,12,13);
 690 insert into atacc1 (a,b,c,d) values (default,11,12,13);
 691 insert into atacc1 (b,c,d) values (11,12,13);
 692 insert into atacc1 ("........pg.dropped.1........") values (10);
 693 insert into atacc1 ("........pg.dropped.1........") values (default);
 694 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
 695 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
 696
 697 -- DELETEs
 698 delete from atacc1 where a = 3;
 699 delete from atacc1 where "........pg.dropped.1........" = 3;
 700 delete from atacc1;
 701
 702 -- try dropping a non-existent column, should fail
 703 alter table atacc1 drop bar;
 704
 705 -- try dropping the oid column, should succeed
 706 alter table atacc1 drop oid;
 707
 708 -- try dropping the xmin column, should fail
 709 alter table atacc1 drop xmin;
 710
 711 -- try creating a view and altering that, should fail
 712 create view myview as select * from atacc1;
 713 select * from myview;
 714 alter table myview drop d;
 715 drop view myview;
 716
 717 -- test some commands to make sure they fail on the dropped column
 718 analyze atacc1(a);
 719 analyze atacc1("........pg.dropped.1........");
 720 vacuum analyze atacc1(a);
 721 vacuum analyze atacc1("........pg.dropped.1........");
 722 comment on column atacc1.a is 'testing';
 723 comment on column atacc1."........pg.dropped.1........" is 'testing';
 724 alter table atacc1 alter a set storage plain;
 725 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
 726 alter table atacc1 alter a set statistics 0;
 727 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
 728 alter table atacc1 alter a set default 3;
 729 alter table atacc1 alter "........pg.dropped.1........" set default 3;
 730 alter table atacc1 alter a drop default;
 731 alter table atacc1 alter "........pg.dropped.1........" drop default;
 732 alter table atacc1 alter a set not null;
 733 alter table atacc1 alter "........pg.dropped.1........" set not null;
 734 alter table atacc1 alter a drop not null;
 735 alter table atacc1 alter "........pg.dropped.1........" drop not null;
 736 alter table atacc1 rename a to x;
 737 alter table atacc1 rename "........pg.dropped.1........" to x;
 738 alter table atacc1 add primary key(a);
 739 alter table atacc1 add primary key("........pg.dropped.1........");
 740 alter table atacc1 add unique(a);
 741 alter table atacc1 add unique("........pg.dropped.1........");
 742 alter table atacc1 add check (a > 3);
 743 alter table atacc1 add check ("........pg.dropped.1........" > 3);
 744 create table atacc2 (id int4 unique);
 745 alter table atacc1 add foreign key (a) references atacc2(id);
 746 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
 747 alter table atacc2 add foreign key (id) references atacc1(a);
 748 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
 749 drop table atacc2;
 750 create index "testing_idx" on atacc1(a);
 751 create index "testing_idx" on atacc1("........pg.dropped.1........");
 752
 753 -- test create as and select into
 754 insert into atacc1 values (21, 22, 23);
 755 create table test1 as select * from atacc1;
 756 select * from test1;
 757 drop table test1;
 758 select * into test2 from atacc1;
 759 select * from test2;
 760 drop table test2;
 761
 762 -- try dropping all columns
 763 alter table atacc1 drop c;
 764 alter table atacc1 drop d;
 765 alter table atacc1 drop b;
 766 select * from atacc1;
 767
 768 drop table atacc1;
 769
 770 -- test inheritance
 771 create table parent (a int, b int, c int);
 772 insert into parent values (1, 2, 3);
 773 alter table parent drop a;
 774 create table child (d varchar(255)) inherits (parent);
 775 insert into child values (12, 13, 'testing');
 776
 777 select * from parent;
 778 select * from child;
 779 alter table parent drop c;
 780 select * from parent;
 781 select * from child;
 782
 783 drop table child;
 784 drop table parent;
 785
 786 -- test copy in/out
 787 create table test (a int4, b int4, c int4);
 788 insert into test values (1,2,3);
 789 alter table test drop a;
 790 copy test to stdout;
 791 copy test(a) to stdout;
 792 copy test("........pg.dropped.1........") to stdout;
 793 copy test from stdin;
 794 10      11      12
 795 \.
 796 select * from test;
 797 copy test from stdin;
 798 21      22
 799 \.
 800 select * from test;
 801 copy test(a) from stdin;
 802 copy test("........pg.dropped.1........") from stdin;
 803 copy test(b,c) from stdin;
 804 31      32
 805 \.
 806 select * from test;
 807 drop table test;
 808
 809 -- test inheritance
 810
 811 create table dropColumn (a int, b int, e int);
 812 create table dropColumnChild (c int) inherits (dropColumn);
 813 create table dropColumnAnother (d int) inherits (dropColumnChild);
 814
 815 -- these two should fail
 816 alter table dropColumnchild drop column a;
 817 alter table only dropColumnChild drop column b;
 818
 819 -- these three should work
 820 alter table only dropColumn drop column e;
 821 alter table dropColumnChild drop column c;
 822 alter table dropColumn drop column a;
 823
 824 create table renameColumn (a int);
 825 create table renameColumnChild (b int) inherits (renameColumn);
 826 create table renameColumnAnother (c int) inherits (renameColumnChild);
 827
 828 -- these three should fail
 829 alter table renameColumnChild rename column a to d;
 830 alter table only renameColumnChild rename column a to d;
 831 alter table only renameColumn rename column a to d;
 832
 833 -- these should work
 834 alter table renameColumn rename column a to d;
 835 alter table renameColumnChild rename column b to a;
 836
 837 -- this should work
 838 alter table renameColumn add column w int;
 839
 840 -- this should fail
 841 alter table only renameColumn add column x int;
 842
 843
 844 -- Test corner cases in dropping of inherited columns
 845
 846 create table p1 (f1 int, f2 int);
 847 create table c1 (f1 int not null) inherits(p1);
 848
 849 -- should be rejected since c1.f1 is inherited
 850 alter table c1 drop column f1;
 851 -- should work
 852 alter table p1 drop column f1;
 853 -- c1.f1 is still there, but no longer inherited
 854 select f1 from c1;
 855 alter table c1 drop column f1;
 856 select f1 from c1;
 857
 858 drop table p1 cascade;
 859
 860 create table p1 (f1 int, f2 int);
 861 create table c1 () inherits(p1);
 862
 863 -- should be rejected since c1.f1 is inherited
 864 alter table c1 drop column f1;
 865 alter table p1 drop column f1;
 866 -- c1.f1 is dropped now, since there is no local definition for it
 867 select f1 from c1;
 868
 869 drop table p1 cascade;
 870
 871 create table p1 (f1 int, f2 int);
 872 create table c1 () inherits(p1);
 873
 874 -- should be rejected since c1.f1 is inherited
 875 alter table c1 drop column f1;
 876 alter table only p1 drop column f1;
 877 -- c1.f1 is NOT dropped, but must now be considered non-inherited
 878 alter table c1 drop column f1;
 879
 880 drop table p1 cascade;
 881
 882 create table p1 (f1 int, f2 int);
 883 create table c1 (f1 int not null) inherits(p1);
 884
 885 -- should be rejected since c1.f1 is inherited
 886 alter table c1 drop column f1;
 887 alter table only p1 drop column f1;
 888 -- c1.f1 is still there, but no longer inherited
 889 alter table c1 drop column f1;
 890
 891 drop table p1 cascade;
 892
 893 create table p1(id int, name text);
 894 create table p2(id2 int, name text, height int);
 895 create table c1(age int) inherits(p1,p2);
 896 create table gc1() inherits (c1);
 897
 898 select relname, attname, attinhcount, attislocal
 899 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 900 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 901 order by relname, attnum;
 902
 903 -- should work
 904 alter table only p1 drop column name;
 905 -- should work. Now c1.name is local and inhcount is 0.
 906 alter table p2 drop column name;
 907 -- should be rejected since its inherited
 908 alter table gc1 drop column name;
 909 -- should work, and drop gc1.name along
 910 alter table c1 drop column name;
 911 -- should fail: column does not exist
 912 alter table gc1 drop column name;
 913 -- should work and drop the attribute in all tables
 914 alter table p2 drop column height;
 915
 916 select relname, attname, attinhcount, attislocal
 917 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 918 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 919 order by relname, attnum;
 920
 921 drop table p1, p2 cascade;
 922
 923 --
 924 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
 925 --
 926 create table altstartwith (col integer) with oids;
 927
 928 insert into altstartwith values (1);
 929
 930 select oid > 0, * from altstartwith;
 931
 932 alter table altstartwith set without oids;
 933
 934 select oid > 0, * from altstartwith; -- fails
 935 select * from altstartwith;
 936
 937 alter table altstartwith set with oids;
 938
 939 select oid > 0, * from altstartwith;
 940
 941 drop table altstartwith;
 942
 943 -- Check inheritance cases
 944 create table altwithoid (col integer) with oids;
 945
 946 -- Inherits parents oid column anyway
 947 create table altinhoid () inherits (altwithoid) without oids;
 948
 949 insert into altinhoid values (1);
 950
 951 select oid > 0, * from altwithoid;
 952 select oid > 0, * from altinhoid;
 953
 954 alter table altwithoid set without oids;
 955
 956 select oid > 0, * from altwithoid; -- fails
 957 select oid > 0, * from altinhoid; -- fails
 958 select * from altwithoid;
 959 select * from altinhoid;
 960
 961 alter table altwithoid set with oids;
 962
 963 select oid > 0, * from altwithoid;
 964 select oid > 0, * from altinhoid;
 965
 966 drop table altwithoid cascade;
 967
 968 create table altwithoid (col integer) without oids;
 969
 970 -- child can have local oid column
 971 create table altinhoid () inherits (altwithoid) with oids;
 972
 973 insert into altinhoid values (1);
 974
 975 select oid > 0, * from altwithoid; -- fails
 976 select oid > 0, * from altinhoid;
 977
 978 alter table altwithoid set with oids;
 979
 980 select oid > 0, * from altwithoid;
 981 select oid > 0, * from altinhoid;
 982
 983 -- the child's local definition should remain
 984 alter table altwithoid set without oids;
 985
 986 select oid > 0, * from altwithoid; -- fails
 987 select oid > 0, * from altinhoid;
 988
 989 drop table altwithoid cascade;
 990
 991 -- test renumbering of child-table columns in inherited operations
 992
 993 create table p1 (f1 int);
 994 create table c1 (f2 text, f3 int) inherits (p1);
 995
 996 alter table p1 add column a1 int check (a1 > 0);
 997 alter table p1 add column f2 text;
 998
 999 insert into p1 values (1,2,'abc');
1000 insert into c1 values(11,'xyz',33,0); -- should fail
1001 insert into c1 values(11,'xyz',33,22);
1002
1003 select * from p1;
1004 update p1 set a1 = a1 + 1, f2 = upper(f2);
1005 select * from p1;
1006
1007 drop table p1 cascade;
1008
1009 -- test that operations with a dropped column do not try to reference
1010 -- its datatype
1011
1012 create domain mytype as text;
1013 create temp table foo (f1 text, f2 mytype, f3 text);
1014
1015 insert into foo values('bb','cc','dd');
1016 select * from foo;
1017
1018 drop domain mytype cascade;
1019
1020 select * from foo;
1021 insert into foo values('qq','rr');
1022 select * from foo;
1023 update foo set f3 = 'zz';
1024 select * from foo;
1025 select f3,max(f1) from foo group by f3;
1026
1027 -- Simple tests for alter table column type
1028 alter table foo alter f1 TYPE integer; -- fails
1029 alter table foo alter f1 TYPE varchar(10);
1030
1031 create table anothertab (atcol1 serial8, atcol2 boolean,
1032         constraint anothertab_chk check (atcol1 <= 3));
1033
1034 insert into anothertab (atcol1, atcol2) values (default, true);
1035 insert into anothertab (atcol1, atcol2) values (default, false);
1036 select * from anothertab;
1037
1038 alter table anothertab alter column atcol1 type boolean; -- fails
1039 alter table anothertab alter column atcol1 type integer;
1040
1041 select * from anothertab;
1042
1043 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1044 insert into anothertab (atcol1, atcol2) values (default, null);
1045
1046 select * from anothertab;
1047
1048 alter table anothertab alter column atcol2 type text
1049       using case when atcol2 is true then 'IT WAS TRUE'
1050                  when atcol2 is false then 'IT WAS FALSE'
1051                  else 'IT WAS NULL!' end;
1052
1053 select * from anothertab;
1054 alter table anothertab alter column atcol1 type boolean
1055         using case when atcol1 % 2 = 0 then true else false end; -- fails
1056 alter table anothertab alter column atcol1 drop default;
1057 alter table anothertab alter column atcol1 type boolean
1058         using case when atcol1 % 2 = 0 then true else false end; -- fails
1059 alter table anothertab drop constraint anothertab_chk;
1060
1061 alter table anothertab alter column atcol1 type boolean
1062         using case when atcol1 % 2 = 0 then true else false end;
1063
1064 select * from anothertab;
1065
1066 drop table anothertab;
1067
1068 create table another (f1 int, f2 text);
1069
1070 insert into another values(1, 'one');
1071 insert into another values(2, 'two');
1072 insert into another values(3, 'three');
1073
1074 select * from another;
1075
1076 alter table another
1077   alter f1 type text using f2 || ' more',
1078   alter f2 type bigint using f1 * 10;
1079
1080 select * from another;
1081
1082 drop table another;
1083
1084 --
1085 -- alter function
1086 --
1087 create function test_strict(text) returns text as
1088     'select coalesce($1, ''got passed a null'');'
1089     language sql returns null on null input;
1090 select test_strict(NULL);
1091 alter function test_strict(text) called on null input;
1092 select test_strict(NULL);
1093
1094 create function non_strict(text) returns text as
1095     'select coalesce($1, ''got passed a null'');'
1096     language sql called on null input;
1097 select non_strict(NULL);
1098 alter function non_strict(text) returns null on null input;
1099 select non_strict(NULL);
1100
1101 --
1102 -- alter object set schema
1103 --
1104
1105 create schema alter1;
1106 create schema alter2;
1107
1108 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1109
1110 create view alter1.v1 as select * from alter1.t1;
1111
1112 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1113
1114 create domain alter1.posint integer check (value > 0);
1115
1116 create type alter1.ctype as (f1 int, f2 text);
1117
1118 insert into alter1.t1(f2) values(11);
1119 insert into alter1.t1(f2) values(12);
1120
1121 alter table alter1.t1 set schema alter2;
1122 alter table alter1.v1 set schema alter2;
1123 alter function alter1.plus1(int) set schema alter2;
1124 alter domain alter1.posint set schema alter2;
1125 alter type alter1.ctype set schema alter2;
1126
1127 -- this should succeed because nothing is left in alter1
1128 drop schema alter1;
1129
1130 insert into alter2.t1(f2) values(13);
1131 insert into alter2.t1(f2) values(14);
1132
1133 select * from alter2.t1;
1134
1135 select * from alter2.v1;
1136
1137 select alter2.plus1(41);
1138
1139 -- clean up
1140 drop schema alter2 cascade;