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 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 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 245 -- This next should fail, because inet=int does not exist
 246 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 247 -- This should also fail for the same reason, but here we
 248 -- give the column name
 249 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 250 -- This should succeed, even though they are different types
 251 -- because varchar=int does exist
 252 DROP TABLE FKTABLE;
 253 CREATE TEMP TABLE FKTABLE (ftest1 varchar);
 254 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 255 -- As should this
 256 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 257 DROP TABLE pktable cascade;
 258 DROP TABLE fktable;
 259
 260 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 261                            PRIMARY KEY(ptest1, ptest2));
 262 -- This should fail, because we just chose really odd types
 263 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 264 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 265 DROP TABLE FKTABLE;
 266 -- Again, so should this...
 267 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 268 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 269      references pktable(ptest1, ptest2);
 270 DROP TABLE FKTABLE;
 271 -- This fails because we mixed up the column ordering
 272 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 273 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 274      references pktable(ptest2, ptest1);
 275 -- As does this...
 276 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 277      references pktable(ptest1, ptest2);
 278
 279 -- temp tables should go away by themselves, need not drop them.
 280
 281 -- test check constraint adding
 282
 283 create table atacc1 ( test int );
 284 -- add a check constraint
 285 alter table atacc1 add constraint atacc_test1 check (test>3);
 286 -- should fail
 287 insert into atacc1 (test) values (2);
 288 -- should succeed
 289 insert into atacc1 (test) values (4);
 290 drop table atacc1;
 291
 292 -- let's do one where the check fails when added
 293 create table atacc1 ( test int );
 294 -- insert a soon to be failing row
 295 insert into atacc1 (test) values (2);
 296 -- add a check constraint (fails)
 297 alter table atacc1 add constraint atacc_test1 check (test>3);
 298 insert into atacc1 (test) values (4);
 299 drop table atacc1;
 300
 301 -- let's do one where the check fails because the column doesn't exist
 302 create table atacc1 ( test int );
 303 -- add a check constraint (fails)
 304 alter table atacc1 add constraint atacc_test1 check (test1>3);
 305 drop table atacc1;
 306
 307 -- something a little more complicated
 308 create table atacc1 ( test int, test2 int, test3 int);
 309 -- add a check constraint (fails)
 310 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 311 -- should fail
 312 insert into atacc1 (test,test2,test3) values (4,4,2);
 313 -- should succeed
 314 insert into atacc1 (test,test2,test3) values (4,4,5);
 315 drop table atacc1;
 316
 317 -- lets do some naming tests
 318 create table atacc1 (test int check (test>3), test2 int);
 319 alter table atacc1 add check (test2>test);
 320 -- should fail for $2
 321 insert into atacc1 (test2, test) values (3, 4);
 322 drop table atacc1;
 323
 324 -- inheritance related tests
 325 create table atacc1 (test int);
 326 create table atacc2 (test2 int);
 327 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 328 alter table atacc2 add constraint foo check (test2>0);
 329 -- fail and then succeed on atacc2
 330 insert into atacc2 (test2) values (-3);
 331 insert into atacc2 (test2) values (3);
 332 -- fail and then succeed on atacc3
 333 insert into atacc3 (test2) values (-3);
 334 insert into atacc3 (test2) values (3);
 335 drop table atacc3;
 336 drop table atacc2;
 337 drop table atacc1;
 338
 339 -- let's try only to add only to the parent
 340
 341 create table atacc1 (test int);
 342 create table atacc2 (test2 int);
 343 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 344 alter table only atacc2 add constraint foo check (test2>0);
 345 -- fail and then succeed on atacc2
 346 insert into atacc2 (test2) values (-3);
 347 insert into atacc2 (test2) values (3);
 348 -- both succeed on atacc3
 349 insert into atacc3 (test2) values (-3);
 350 insert into atacc3 (test2) values (3);
 351 drop table atacc3;
 352 drop table atacc2;
 353 drop table atacc1;
 354
 355 -- test unique constraint adding
 356
 357 create table atacc1 ( test int ) with oids;
 358 -- add a unique constraint
 359 alter table atacc1 add constraint atacc_test1 unique (test);
 360 -- insert first value
 361 insert into atacc1 (test) values (2);
 362 -- should fail
 363 insert into atacc1 (test) values (2);
 364 -- should succeed
 365 insert into atacc1 (test) values (4);
 366 -- try adding a unique oid constraint
 367 alter table atacc1 add constraint atacc_oid1 unique(oid);
 368 drop table atacc1;
 369
 370 -- let's do one where the unique constraint fails when added
 371 create table atacc1 ( test int );
 372 -- insert soon to be failing rows
 373 insert into atacc1 (test) values (2);
 374 insert into atacc1 (test) values (2);
 375 -- add a unique constraint (fails)
 376 alter table atacc1 add constraint atacc_test1 unique (test);
 377 insert into atacc1 (test) values (3);
 378 drop table atacc1;
 379
 380 -- let's do one where the unique constraint fails
 381 -- because the column doesn't exist
 382 create table atacc1 ( test int );
 383 -- add a unique constraint (fails)
 384 alter table atacc1 add constraint atacc_test1 unique (test1);
 385 drop table atacc1;
 386
 387 -- something a little more complicated
 388 create table atacc1 ( test int, test2 int);
 389 -- add a unique constraint
 390 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 391 -- insert initial value
 392 insert into atacc1 (test,test2) values (4,4);
 393 -- should fail
 394 insert into atacc1 (test,test2) values (4,4);
 395 -- should all succeed
 396 insert into atacc1 (test,test2) values (4,5);
 397 insert into atacc1 (test,test2) values (5,4);
 398 insert into atacc1 (test,test2) values (5,5);
 399 drop table atacc1;
 400
 401 -- lets do some naming tests
 402 create table atacc1 (test int, test2 int, unique(test));
 403 alter table atacc1 add unique (test2);
 404 -- should fail for @@ second one @@
 405 insert into atacc1 (test2, test) values (3, 3);
 406 insert into atacc1 (test2, test) values (2, 3);
 407 drop table atacc1;
 408
 409 -- test primary key constraint adding
 410
 411 create table atacc1 ( test int ) with oids;
 412 -- add a primary key constraint
 413 alter table atacc1 add constraint atacc_test1 primary key (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 -- inserting NULL should fail
 421 insert into atacc1 (test) values(NULL);
 422 -- try adding a second primary key (should fail)
 423 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 424 -- drop first primary key constraint
 425 alter table atacc1 drop constraint atacc_test1 restrict;
 426 -- try adding a primary key on oid (should succeed)
 427 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 428 drop table atacc1;
 429
 430 -- let's do one where the primary key constraint fails when added
 431 create table atacc1 ( test int );
 432 -- insert soon to be failing rows
 433 insert into atacc1 (test) values (2);
 434 insert into atacc1 (test) values (2);
 435 -- add a primary key (fails)
 436 alter table atacc1 add constraint atacc_test1 primary key (test);
 437 insert into atacc1 (test) values (3);
 438 drop table atacc1;
 439
 440 -- let's do another one where the primary key constraint fails when added
 441 create table atacc1 ( test int );
 442 -- insert soon to be failing row
 443 insert into atacc1 (test) values (NULL);
 444 -- add a primary key (fails)
 445 alter table atacc1 add constraint atacc_test1 primary key (test);
 446 insert into atacc1 (test) values (3);
 447 drop table atacc1;
 448
 449 -- let's do one where the primary key constraint fails
 450 -- because the column doesn't exist
 451 create table atacc1 ( test int );
 452 -- add a primary key constraint (fails)
 453 alter table atacc1 add constraint atacc_test1 primary key (test1);
 454 drop table atacc1;
 455
 456 -- something a little more complicated
 457 create table atacc1 ( test int, test2 int);
 458 -- add a primary key constraint
 459 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 460 -- try adding a second primary key - should fail
 461 alter table atacc1 add constraint atacc_test2 primary key (test);
 462 -- insert initial value
 463 insert into atacc1 (test,test2) values (4,4);
 464 -- should fail
 465 insert into atacc1 (test,test2) values (4,4);
 466 insert into atacc1 (test,test2) values (NULL,3);
 467 insert into atacc1 (test,test2) values (3, NULL);
 468 insert into atacc1 (test,test2) values (NULL,NULL);
 469 -- should all succeed
 470 insert into atacc1 (test,test2) values (4,5);
 471 insert into atacc1 (test,test2) values (5,4);
 472 insert into atacc1 (test,test2) values (5,5);
 473 drop table atacc1;
 474
 475 -- lets do some naming tests
 476 create table atacc1 (test int, test2 int, primary key(test));
 477 -- only first should succeed
 478 insert into atacc1 (test2, test) values (3, 3);
 479 insert into atacc1 (test2, test) values (2, 3);
 480 insert into atacc1 (test2, test) values (1, NULL);
 481 drop table atacc1;
 482
 483 -- alter table / alter column [set/drop] not null tests
 484 -- try altering system catalogs, should fail
 485 alter table pg_class alter column relname drop not null;
 486 alter table pg_class alter relname set not null;
 487
 488 -- try altering non-existent table, should fail
 489 alter table non_existent alter column bar set not null;
 490 alter table non_existent alter column bar drop not null;
 491
 492 -- test setting columns to null and not null and vice versa
 493 -- test checking for null values and primary key
 494 create table atacc1 (test int not null) with oids;
 495 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 496 alter table atacc1 alter column test drop not null;
 497 alter table atacc1 drop constraint "atacc1_pkey";
 498 alter table atacc1 alter column test drop not null;
 499 insert into atacc1 values (null);
 500 alter table atacc1 alter test set not null;
 501 delete from atacc1;
 502 alter table atacc1 alter test set not null;
 503
 504 -- try altering a non-existent column, should fail
 505 alter table atacc1 alter bar set not null;
 506 alter table atacc1 alter bar drop not null;
 507
 508 -- try altering the oid column, should fail
 509 alter table atacc1 alter oid set not null;
 510 alter table atacc1 alter oid drop not null;
 511
 512 -- try creating a view and altering that, should fail
 513 create view myview as select * from atacc1;
 514 alter table myview alter column test drop not null;
 515 alter table myview alter column test set not null;
 516 drop view myview;
 517
 518 drop table atacc1;
 519
 520 -- test inheritance
 521 create table parent (a int);
 522 create table child (b varchar(255)) inherits (parent);
 523
 524 alter table parent alter a set not null;
 525 insert into parent values (NULL);
 526 insert into child (a, b) values (NULL, 'foo');
 527 alter table parent alter a drop not null;
 528 insert into parent values (NULL);
 529 insert into child (a, b) values (NULL, 'foo');
 530 alter table only parent alter a set not null;
 531 alter table child alter a set not null;
 532 delete from parent;
 533 alter table only parent alter a set not null;
 534 insert into parent values (NULL);
 535 alter table child alter a set not null;
 536 insert into child (a, b) values (NULL, 'foo');
 537 delete from child;
 538 alter table child alter a set not null;
 539 insert into child (a, b) values (NULL, 'foo');
 540 drop table child;
 541 drop table parent;
 542
 543 -- test setting and removing default values
 544 create table def_test (
 545         c1      int4 default 5,
 546         c2      text default 'initial_default'
 547 );
 548 insert into def_test default values;
 549 alter table def_test alter column c1 drop default;
 550 insert into def_test default values;
 551 alter table def_test alter column c2 drop default;
 552 insert into def_test default values;
 553 alter table def_test alter column c1 set default 10;
 554 alter table def_test alter column c2 set default 'new_default';
 555 insert into def_test default values;
 556 select * from def_test;
 557
 558 -- set defaults to an incorrect type: this should fail
 559 alter table def_test alter column c1 set default 'wrong_datatype';
 560 alter table def_test alter column c2 set default 20;
 561
 562 -- set defaults on a non-existent column: this should fail
 563 alter table def_test alter column c3 set default 30;
 564
 565 -- set defaults on views: we need to create a view, add a rule
 566 -- to allow insertions into it, and then alter the view to add
 567 -- a default
 568 create view def_view_test as select * from def_test;
 569 create rule def_view_test_ins as
 570         on insert to def_view_test
 571         do instead insert into def_test select new.*;
 572 insert into def_view_test default values;
 573 alter table def_view_test alter column c1 set default 45;
 574 insert into def_view_test default values;
 575 alter table def_view_test alter column c2 set default 'view_default';
 576 insert into def_view_test default values;
 577 select * from def_view_test;
 578
 579 drop rule def_view_test_ins on def_view_test;
 580 drop view def_view_test;
 581 drop table def_test;
 582
 583 -- alter table / drop column tests
 584 -- try altering system catalogs, should fail
 585 alter table pg_class drop column relname;
 586
 587 -- try altering non-existent table, should fail
 588 alter table nosuchtable drop column bar;
 589
 590 -- test dropping columns
 591 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
 592 insert into atacc1 values (1, 2, 3, 4);
 593 alter table atacc1 drop a;
 594 alter table atacc1 drop a;
 595
 596 -- SELECTs
 597 select * from atacc1;
 598 select * from atacc1 order by a;
 599 select * from atacc1 order by "........pg.dropped.1........";
 600 select * from atacc1 group by a;
 601 select * from atacc1 group by "........pg.dropped.1........";
 602 select atacc1.* from atacc1;
 603 select a from atacc1;
 604 select atacc1.a from atacc1;
 605 select b,c,d from atacc1;
 606 select a,b,c,d from atacc1;
 607 select * from atacc1 where a = 1;
 608 select "........pg.dropped.1........" from atacc1;
 609 select atacc1."........pg.dropped.1........" from atacc1;
 610 select "........pg.dropped.1........",b,c,d from atacc1;
 611 select * from atacc1 where "........pg.dropped.1........" = 1;
 612
 613 -- UPDATEs
 614 update atacc1 set a = 3;
 615 update atacc1 set b = 2 where a = 3;
 616 update atacc1 set "........pg.dropped.1........" = 3;
 617 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
 618
 619 -- INSERTs
 620 insert into atacc1 values (10, 11, 12, 13);
 621 insert into atacc1 values (default, 11, 12, 13);
 622 insert into atacc1 values (11, 12, 13);
 623 insert into atacc1 (a) values (10);
 624 insert into atacc1 (a) values (default);
 625 insert into atacc1 (a,b,c,d) values (10,11,12,13);
 626 insert into atacc1 (a,b,c,d) values (default,11,12,13);
 627 insert into atacc1 (b,c,d) values (11,12,13);
 628 insert into atacc1 ("........pg.dropped.1........") values (10);
 629 insert into atacc1 ("........pg.dropped.1........") values (default);
 630 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
 631 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
 632
 633 -- DELETEs
 634 delete from atacc1 where a = 3;
 635 delete from atacc1 where "........pg.dropped.1........" = 3;
 636 delete from atacc1;
 637
 638 -- try dropping a non-existent column, should fail
 639 alter table atacc1 drop bar;
 640
 641 -- try dropping the oid column, should succeed
 642 alter table atacc1 drop oid;
 643
 644 -- try dropping the xmin column, should fail
 645 alter table atacc1 drop xmin;
 646
 647 -- try creating a view and altering that, should fail
 648 create view myview as select * from atacc1;
 649 select * from myview;
 650 alter table myview drop d;
 651 drop view myview;
 652
 653 -- test some commands to make sure they fail on the dropped column
 654 analyze atacc1(a);
 655 analyze atacc1("........pg.dropped.1........");
 656 vacuum analyze atacc1(a);
 657 vacuum analyze atacc1("........pg.dropped.1........");
 658 comment on column atacc1.a is 'testing';
 659 comment on column atacc1."........pg.dropped.1........" is 'testing';
 660 alter table atacc1 alter a set storage plain;
 661 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
 662 alter table atacc1 alter a set statistics 0;
 663 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
 664 alter table atacc1 alter a set default 3;
 665 alter table atacc1 alter "........pg.dropped.1........" set default 3;
 666 alter table atacc1 alter a drop default;
 667 alter table atacc1 alter "........pg.dropped.1........" drop default;
 668 alter table atacc1 alter a set not null;
 669 alter table atacc1 alter "........pg.dropped.1........" set not null;
 670 alter table atacc1 alter a drop not null;
 671 alter table atacc1 alter "........pg.dropped.1........" drop not null;
 672 alter table atacc1 rename a to x;
 673 alter table atacc1 rename "........pg.dropped.1........" to x;
 674 alter table atacc1 add primary key(a);
 675 alter table atacc1 add primary key("........pg.dropped.1........");
 676 alter table atacc1 add unique(a);
 677 alter table atacc1 add unique("........pg.dropped.1........");
 678 alter table atacc1 add check (a > 3);
 679 alter table atacc1 add check ("........pg.dropped.1........" > 3);
 680 create table atacc2 (id int4 unique);
 681 alter table atacc1 add foreign key (a) references atacc2(id);
 682 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
 683 alter table atacc2 add foreign key (id) references atacc1(a);
 684 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
 685 drop table atacc2;
 686 create index "testing_idx" on atacc1(a);
 687 create index "testing_idx" on atacc1("........pg.dropped.1........");
 688
 689 -- test create as and select into
 690 insert into atacc1 values (21, 22, 23);
 691 create table test1 as select * from atacc1;
 692 select * from test1;
 693 drop table test1;
 694 select * into test2 from atacc1;
 695 select * from test2;
 696 drop table test2;
 697
 698 -- try dropping all columns
 699 alter table atacc1 drop c;
 700 alter table atacc1 drop d;
 701 alter table atacc1 drop b;
 702 select * from atacc1;
 703
 704 drop table atacc1;
 705
 706 -- test inheritance
 707 create table parent (a int, b int, c int);
 708 insert into parent values (1, 2, 3);
 709 alter table parent drop a;
 710 create table child (d varchar(255)) inherits (parent);
 711 insert into child values (12, 13, 'testing');
 712
 713 select * from parent;
 714 select * from child;
 715 alter table parent drop c;
 716 select * from parent;
 717 select * from child;
 718
 719 drop table child;
 720 drop table parent;
 721
 722 -- test copy in/out
 723 create table test (a int4, b int4, c int4);
 724 insert into test values (1,2,3);
 725 alter table test drop a;
 726 copy test to stdout;
 727 copy test(a) to stdout;
 728 copy test("........pg.dropped.1........") to stdout;
 729 copy test from stdin;
 730 10      11      12
 731 \.
 732 select * from test;
 733 copy test from stdin;
 734 21      22
 735 \.
 736 select * from test;
 737 copy test(a) from stdin;
 738 copy test("........pg.dropped.1........") from stdin;
 739 copy test(b,c) from stdin;
 740 31      32
 741 \.
 742 select * from test;
 743 drop table test;
 744
 745 -- test inheritance
 746
 747 create table dropColumn (a int, b int, e int);
 748 create table dropColumnChild (c int) inherits (dropColumn);
 749 create table dropColumnAnother (d int) inherits (dropColumnChild);
 750
 751 -- these two should fail
 752 alter table dropColumnchild drop column a;
 753 alter table only dropColumnChild drop column b;
 754
 755 -- these three should work
 756 alter table only dropColumn drop column e;
 757 alter table dropColumnChild drop column c;
 758 alter table dropColumn drop column a;
 759
 760 create table renameColumn (a int);
 761 create table renameColumnChild (b int) inherits (renameColumn);
 762 create table renameColumnAnother (c int) inherits (renameColumnChild);
 763
 764 -- these three should fail
 765 alter table renameColumnChild rename column a to d;
 766 alter table only renameColumnChild rename column a to d;
 767 alter table only renameColumn rename column a to d;
 768
 769 -- these should work
 770 alter table renameColumn rename column a to d;
 771 alter table renameColumnChild rename column b to a;
 772
 773 -- this should work
 774 alter table renameColumn add column w int;
 775
 776 -- this should fail
 777 alter table only renameColumn add column x int;
 778
 779
 780 -- Test corner cases in dropping of inherited columns
 781
 782 create table p1 (f1 int, f2 int);
 783 create table c1 (f1 int not null) inherits(p1);
 784
 785 -- should be rejected since c1.f1 is inherited
 786 alter table c1 drop column f1;
 787 -- should work
 788 alter table p1 drop column f1;
 789 -- c1.f1 is still there, but no longer inherited
 790 select f1 from c1;
 791 alter table c1 drop column f1;
 792 select f1 from c1;
 793
 794 drop table p1 cascade;
 795
 796 create table p1 (f1 int, f2 int);
 797 create table c1 () inherits(p1);
 798
 799 -- should be rejected since c1.f1 is inherited
 800 alter table c1 drop column f1;
 801 alter table p1 drop column f1;
 802 -- c1.f1 is dropped now, since there is no local definition for it
 803 select f1 from c1;
 804
 805 drop table p1 cascade;
 806
 807 create table p1 (f1 int, f2 int);
 808 create table c1 () inherits(p1);
 809
 810 -- should be rejected since c1.f1 is inherited
 811 alter table c1 drop column f1;
 812 alter table only p1 drop column f1;
 813 -- c1.f1 is NOT dropped, but must now be considered non-inherited
 814 alter table c1 drop column f1;
 815
 816 drop table p1 cascade;
 817
 818 create table p1 (f1 int, f2 int);
 819 create table c1 (f1 int not null) inherits(p1);
 820
 821 -- should be rejected since c1.f1 is inherited
 822 alter table c1 drop column f1;
 823 alter table only p1 drop column f1;
 824 -- c1.f1 is still there, but no longer inherited
 825 alter table c1 drop column f1;
 826
 827 drop table p1 cascade;
 828
 829 create table p1(id int, name text);
 830 create table p2(id2 int, name text, height int);
 831 create table c1(age int) inherits(p1,p2);
 832 create table gc1() inherits (c1);
 833
 834 select relname, attname, attinhcount, attislocal
 835 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 836 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 837 order by relname, attnum;
 838
 839 -- should work
 840 alter table only p1 drop column name;
 841 -- should work. Now c1.name is local and inhcount is 0.
 842 alter table p2 drop column name;
 843 -- should be rejected since its inherited
 844 alter table gc1 drop column name;
 845 -- should work, and drop gc1.name along
 846 alter table c1 drop column name;
 847 -- should fail: column does not exist
 848 alter table gc1 drop column name;
 849 -- should work and drop the attribute in all tables
 850 alter table p2 drop column height;
 851
 852 select relname, attname, attinhcount, attislocal
 853 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 854 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 855 order by relname, attnum;
 856
 857 drop table p1, p2 cascade;
 858
 859 --
 860 -- Test the ALTER TABLE WITHOUT OIDS command
 861 --
 862 create table altstartwith (col integer) with oids;
 863
 864 insert into altstartwith values (1);
 865
 866 select oid > 0, * from altstartwith;
 867
 868 alter table altstartwith set without oids;
 869
 870 select oid > 0, * from altstartwith; -- fails
 871 select * from altstartwith;
 872
 873 -- Run inheritance tests
 874 create table altwithoid (col integer) with oids;
 875
 876 -- Inherits parents oid column
 877 create table altinhoid () inherits (altwithoid) without oids;
 878
 879 insert into altinhoid values (1);
 880
 881 select oid > 0, * from altwithoid;
 882 select oid > 0, * from altinhoid;
 883
 884 alter table altwithoid set without oids;
 885 alter table altinhoid set without oids;
 886
 887 select oid > 0, * from altwithoid; -- fails
 888 select oid > 0, * from altinhoid; -- fails
 889 select * from altwithoid;
 890 select * from altinhoid;
 891
 892 -- test renumbering of child-table columns in inherited operations
 893
 894 create table p1 (f1 int);
 895 create table c1 (f2 text, f3 int) inherits (p1);
 896
 897 alter table p1 add column a1 int check (a1 > 0);
 898 alter table p1 add column f2 text;
 899
 900 insert into p1 values (1,2,'abc');
 901 insert into c1 values(11,'xyz',33,0); -- should fail
 902 insert into c1 values(11,'xyz',33,22);
 903
 904 select * from p1;
 905 update p1 set a1 = a1 + 1, f2 = upper(f2);
 906 select * from p1;
 907
 908 drop table p1 cascade;
 909
 910 -- test that operations with a dropped column do not try to reference
 911 -- its datatype
 912
 913 create domain mytype as text;
 914 create temp table foo (f1 text, f2 mytype, f3 text);
 915
 916 insert into foo values('aa','bb','cc');
 917 select * from foo;
 918
 919 drop domain mytype cascade;
 920
 921 select * from foo;
 922 insert into foo values('qq','rr');
 923 select * from foo;
 924 update foo set f3 = 'zz';
 925 select * from foo;
 926 select f3,max(f1) from foo group by f3;
 927
 928 -- Simple tests for alter table column type
 929 alter table foo alter f1 TYPE integer; -- fails
 930 alter table foo alter f1 TYPE varchar(10);
 931
 932 create table anothertab (atcol1 serial8, atcol2 boolean,
 933         constraint anothertab_chk check (atcol1 <= 3));
 934
 935 insert into anothertab (atcol1, atcol2) values (default, true);
 936 insert into anothertab (atcol1, atcol2) values (default, false);
 937 select * from anothertab;
 938
 939 alter table anothertab alter column atcol1 type boolean; -- fails
 940 alter table anothertab alter column atcol1 type integer;
 941
 942 select * from anothertab;
 943
 944 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
 945 insert into anothertab (atcol1, atcol2) values (default, null);
 946
 947 select * from anothertab;
 948
 949 alter table anothertab alter column atcol2 type text
 950       using case when atcol2 is true then 'IT WAS TRUE'
 951                  when atcol2 is false then 'IT WAS FALSE'
 952                  else 'IT WAS NULL!' end;
 953
 954 select * from anothertab;
 955 alter table anothertab alter column atcol1 type boolean
 956         using case when atcol1 % 2 = 0 then true else false end; -- fails
 957 alter table anothertab alter column atcol1 drop default;
 958 alter table anothertab alter column atcol1 type boolean
 959         using case when atcol1 % 2 = 0 then true else false end; -- fails
 960 alter table anothertab drop constraint anothertab_chk;
 961
 962 alter table anothertab alter column atcol1 type boolean
 963         using case when atcol1 % 2 = 0 then true else false end;
 964
 965 select * from anothertab;
 966
 967 drop table anothertab;
 968
 969 create table another (f1 int, f2 text);
 970
 971 insert into another values(1, 'one');
 972 insert into another values(2, 'two');
 973 insert into another values(3, 'three');
 974
 975 select * from another;
 976
 977 alter table another
 978   alter f1 type text using f2 || ' more',
 979   alter f2 type bigint using f1 * 10;
 980
 981 select * from another;
 982
 983 drop table another;
 984
 985 --
 986 -- alter function
 987 --
 988 create function test_strict(text) returns text as
 989     'select coalesce($1, ''got passed a null'');'
 990     language sql returns null on null input;
 991 select test_strict(NULL);
 992 alter function test_strict(text) called on null input;
 993 select test_strict(NULL);
 994
 995 create function non_strict(text) returns text as
 996     'select coalesce($1, ''got passed a null'');'
 997     language sql called on null input;
 998 select non_strict(NULL);
 999 alter function non_strict(text) returns null on null input;
1000 select non_strict(NULL);
1001
1002 --
1003 -- alter object set schema
1004 --
1005
1006 create schema alter1;
1007 create schema alter2;
1008
1009 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1010
1011 create view alter1.v1 as select * from alter1.t1;
1012
1013 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1014
1015 create domain alter1.posint integer check (value > 0);
1016
1017 create type alter1.ctype as (f1 int, f2 text);
1018
1019 insert into alter1.t1(f2) values(11);
1020 insert into alter1.t1(f2) values(12);
1021
1022 alter table alter1.t1 set schema alter2;
1023 alter table alter1.v1 set schema alter2;
1024 alter function alter1.plus1(int) set schema alter2;
1025 alter domain alter1.posint set schema alter2;
1026 alter type alter1.ctype set schema alter2;
1027
1028 -- this should succeed because nothing is left in alter1
1029 drop schema alter1;
1030
1031 insert into alter2.t1(f2) values(13);
1032 insert into alter2.t1(f2) values(14);
1033
1034 select * from alter2.t1;
1035
1036 select * from alter2.v1;
1037
1038 select alter2.plus1(41);
1039
1040 -- clean up
1041 drop schema alter2 cascade;