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 ALTER TABLE tmp ADD COLUMN a int4;
   9
  10 ALTER TABLE tmp ADD COLUMN b name;
  11
  12 ALTER TABLE tmp ADD COLUMN c text;
  13
  14 ALTER TABLE tmp ADD COLUMN d float8;
  15
  16 ALTER TABLE tmp ADD COLUMN e float4;
  17
  18 ALTER TABLE tmp ADD COLUMN f int2;
  19
  20 ALTER TABLE tmp ADD COLUMN g polygon;
  21
  22 ALTER TABLE tmp ADD COLUMN h abstime;
  23
  24 ALTER TABLE tmp ADD COLUMN i char;
  25
  26 ALTER TABLE tmp ADD COLUMN j abstime[];
  27
  28 ALTER TABLE tmp ADD COLUMN k int4;
  29
  30 ALTER TABLE tmp ADD COLUMN l tid;
  31
  32 ALTER TABLE tmp ADD COLUMN m xid;
  33
  34 ALTER TABLE tmp ADD COLUMN n oidvector;
  35
  36 --ALTER TABLE tmp ADD COLUMN o lock;
  37 ALTER TABLE tmp ADD COLUMN p smgr;
  38
  39 ALTER TABLE tmp ADD COLUMN q point;
  40
  41 ALTER TABLE tmp ADD COLUMN r lseg;
  42
  43 ALTER TABLE tmp ADD COLUMN s path;
  44
  45 ALTER TABLE tmp ADD COLUMN t box;
  46
  47 ALTER TABLE tmp ADD COLUMN u tinterval;
  48
  49 ALTER TABLE tmp ADD COLUMN v timestamp;
  50
  51 ALTER TABLE tmp ADD COLUMN w interval;
  52
  53 ALTER TABLE tmp ADD COLUMN x float8[];
  54
  55 ALTER TABLE tmp ADD COLUMN y float4[];
  56
  57 ALTER TABLE tmp ADD COLUMN z int2[];
  58
  59 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  60         v, w, x, y, z)
  61    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
  62         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
  63         314159, '(1,1)', '512',
  64         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
  65         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
  66         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
  67
  68 SELECT * FROM tmp;
  69
  70 DROP TABLE tmp;
  71
  72 -- the wolf bug - schema mods caused inconsistent row descriptors
  73 CREATE TABLE tmp (
  74         initial         int4
  75 );
  76
  77 ALTER TABLE tmp ADD COLUMN a int4;
  78
  79 ALTER TABLE tmp ADD COLUMN b name;
  80
  81 ALTER TABLE tmp ADD COLUMN c text;
  82
  83 ALTER TABLE tmp ADD COLUMN d float8;
  84
  85 ALTER TABLE tmp ADD COLUMN e float4;
  86
  87 ALTER TABLE tmp ADD COLUMN f int2;
  88
  89 ALTER TABLE tmp ADD COLUMN g polygon;
  90
  91 ALTER TABLE tmp ADD COLUMN h abstime;
  92
  93 ALTER TABLE tmp ADD COLUMN i char;
  94
  95 ALTER TABLE tmp ADD COLUMN j abstime[];
  96
  97 ALTER TABLE tmp ADD COLUMN k int4;
  98
  99 ALTER TABLE tmp ADD COLUMN l tid;
 100
 101 ALTER TABLE tmp ADD COLUMN m xid;
 102
 103 ALTER TABLE tmp ADD COLUMN n oidvector;
 104
 105 --ALTER TABLE tmp ADD COLUMN o lock;
 106 ALTER TABLE tmp ADD COLUMN p smgr;
 107
 108 ALTER TABLE tmp ADD COLUMN q point;
 109
 110 ALTER TABLE tmp ADD COLUMN r lseg;
 111
 112 ALTER TABLE tmp ADD COLUMN s path;
 113
 114 ALTER TABLE tmp ADD COLUMN t box;
 115
 116 ALTER TABLE tmp ADD COLUMN u tinterval;
 117
 118 ALTER TABLE tmp ADD COLUMN v timestamp;
 119
 120 ALTER TABLE tmp ADD COLUMN w interval;
 121
 122 ALTER TABLE tmp ADD COLUMN x float8[];
 123
 124 ALTER TABLE tmp ADD COLUMN y float4[];
 125
 126 ALTER TABLE tmp ADD COLUMN z int2[];
 127
 128 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
 129         v, w, x, y, z)
 130    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
 131         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
 132         314159, '(1,1)', '512',
 133         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
 134         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
 135         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
 136
 137 SELECT * FROM tmp;
 138
 139 DROP TABLE tmp;
 140
 141
 142 --
 143 -- rename -
 144 --   should preserve indices, which we can check by seeing if a SELECT
 145 --   chooses an indexscan; however, in the absence of vacuum statistics
 146 --   it might not.  Therefore, vacuum first.
 147 --
 148 VACUUM ANALYZE tenk1;
 149
 150 ALTER TABLE tenk1 RENAME TO ten_k;
 151
 152 -- 20 values, sorted
 153 SELECT unique1 FROM ten_k WHERE unique1 < 20;
 154
 155 -- 20 values, sorted
 156 SELECT unique2 FROM ten_k WHERE unique2 < 20;
 157
 158 -- 100 values, sorted
 159 SELECT hundred FROM ten_k WHERE hundred = 50;
 160
 161 ALTER TABLE ten_k RENAME TO tenk1;
 162
 163 -- 5 values, sorted
 164 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 165
 166 -- ALTER TABLE ... RENAME on non-table relations
 167 -- renaming indexes (FIXME: this should probably test the index's functionality)
 168 ALTER TABLE onek_unique1 RENAME TO tmp_onek_unique1;
 169 ALTER TABLE tmp_onek_unique1 RENAME TO onek_unique1;
 170 -- renaming views
 171 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
 172 ALTER TABLE tmp_view RENAME TO tmp_view_new;
 173 -- 5 values, sorted
 174 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 175 DROP VIEW tmp_view_new;
 176 -- renaming sequences
 177 CREATE SEQUENCE foo_seq;
 178 ALTER TABLE foo_seq RENAME TO foo_seq_new;
 179 SELECT * FROM foo_seq_new;
 180 DROP SEQUENCE foo_seq_new;
 181 -- toast-like relation name
 182 alter table stud_emp rename to pg_toast_stud_emp;
 183 alter table pg_toast_stud_emp rename to stud_emp;
 184
 185 -- FOREIGN KEY CONSTRAINT adding TEST
 186
 187 CREATE TABLE tmp2 (a int primary key);
 188
 189 CREATE TABLE tmp3 (a int, b int);
 190
 191 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 192
 193 CREATE TABLE tmp5 (a int, b int);
 194
 195 -- Insert rows into tmp2 (pktable)
 196 INSERT INTO tmp2 values (1);
 197 INSERT INTO tmp2 values (2);
 198 INSERT INTO tmp2 values (3);
 199 INSERT INTO tmp2 values (4);
 200
 201 -- Insert rows into tmp3
 202 INSERT INTO tmp3 values (1,10);
 203 INSERT INTO tmp3 values (1,20);
 204 INSERT INTO tmp3 values (5,50);
 205
 206 -- Try (and fail) to add constraint due to invalid source columns
 207 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
 208
 209 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
 210 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
 211
 212 -- Try (and fail) to add constraint due to invalid data
 213 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 214
 215 -- Delete failing row
 216 DELETE FROM tmp3 where a=5;
 217
 218 -- Try (and succeed)
 219 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 220
 221 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 222 -- tmp4 is a,b
 223
 224 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 225
 226 DROP TABLE tmp5;
 227
 228 DROP TABLE tmp4;
 229
 230 DROP TABLE tmp3;
 231
 232 DROP TABLE tmp2;
 233
 234 -- Foreign key adding test with mixed types
 235
 236 -- Note: these tables are TEMP to avoid name conflicts when this test
 237 -- is run in parallel with foreign_key.sql.
 238
 239 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 240 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 241 -- This next should fail, because inet=int does not exist
 242 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 243 -- This should also fail for the same reason, but here we
 244 -- give the column name
 245 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 246 -- This should succeed, even though they are different types
 247 -- because varchar=int does exist
 248 DROP TABLE FKTABLE;
 249 CREATE TEMP TABLE FKTABLE (ftest1 varchar);
 250 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 251 -- As should this
 252 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 253 DROP TABLE pktable cascade;
 254 DROP TABLE fktable;
 255
 256 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 257                            PRIMARY KEY(ptest1, ptest2));
 258 -- This should fail, because we just chose really odd types
 259 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 260 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 261 DROP TABLE FKTABLE;
 262 -- Again, so should this...
 263 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 264 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 265      references pktable(ptest1, ptest2);
 266 DROP TABLE FKTABLE;
 267 -- This fails because we mixed up the column ordering
 268 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 269 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 270      references pktable(ptest2, ptest1);
 271 -- As does this...
 272 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 273      references pktable(ptest1, ptest2);
 274
 275 -- temp tables should go away by themselves, need not drop them.
 276
 277 -- test check constraint adding
 278
 279 create table atacc1 ( test int );
 280 -- add a check constraint
 281 alter table atacc1 add constraint atacc_test1 check (test>3);
 282 -- should fail
 283 insert into atacc1 (test) values (2);
 284 -- should succeed
 285 insert into atacc1 (test) values (4);
 286 drop table atacc1;
 287
 288 -- let's do one where the check fails when added
 289 create table atacc1 ( test int );
 290 -- insert a soon to be failing row
 291 insert into atacc1 (test) values (2);
 292 -- add a check constraint (fails)
 293 alter table atacc1 add constraint atacc_test1 check (test>3);
 294 insert into atacc1 (test) values (4);
 295 drop table atacc1;
 296
 297 -- let's do one where the check fails because the column doesn't exist
 298 create table atacc1 ( test int );
 299 -- add a check constraint (fails)
 300 alter table atacc1 add constraint atacc_test1 check (test1>3);
 301 drop table atacc1;
 302
 303 -- something a little more complicated
 304 create table atacc1 ( test int, test2 int, test3 int);
 305 -- add a check constraint (fails)
 306 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 307 -- should fail
 308 insert into atacc1 (test,test2,test3) values (4,4,2);
 309 -- should succeed
 310 insert into atacc1 (test,test2,test3) values (4,4,5);
 311 drop table atacc1;
 312
 313 -- lets do some naming tests
 314 create table atacc1 (test int check (test>3), test2 int);
 315 alter table atacc1 add check (test2>test);
 316 -- should fail for $2
 317 insert into atacc1 (test2, test) values (3, 4);
 318 drop table atacc1;
 319
 320 -- inheritance related tests
 321 create table atacc1 (test int);
 322 create table atacc2 (test2 int);
 323 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 324 alter table atacc2 add constraint foo check (test2>0);
 325 -- fail and then succeed on atacc2
 326 insert into atacc2 (test2) values (-3);
 327 insert into atacc2 (test2) values (3);
 328 -- fail and then succeed on atacc3
 329 insert into atacc3 (test2) values (-3);
 330 insert into atacc3 (test2) values (3);
 331 drop table atacc3;
 332 drop table atacc2;
 333 drop table atacc1;
 334
 335 -- let's try only to add only to the parent
 336
 337 create table atacc1 (test int);
 338 create table atacc2 (test2 int);
 339 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 340 alter table only atacc2 add constraint foo check (test2>0);
 341 -- fail and then succeed on atacc2
 342 insert into atacc2 (test2) values (-3);
 343 insert into atacc2 (test2) values (3);
 344 -- both succeed on atacc3
 345 insert into atacc3 (test2) values (-3);
 346 insert into atacc3 (test2) values (3);
 347 drop table atacc3;
 348 drop table atacc2;
 349 drop table atacc1;
 350
 351 -- test unique constraint adding
 352
 353 create table atacc1 ( test int );
 354 -- add a unique constraint
 355 alter table atacc1 add constraint atacc_test1 unique (test);
 356 -- insert first value
 357 insert into atacc1 (test) values (2);
 358 -- should fail
 359 insert into atacc1 (test) values (2);
 360 -- should succeed
 361 insert into atacc1 (test) values (4);
 362 -- try adding a unique oid constraint
 363 alter table atacc1 add constraint atacc_oid1 unique(oid);
 364 drop table atacc1;
 365
 366 -- let's do one where the unique constraint fails when added
 367 create table atacc1 ( test int );
 368 -- insert soon to be failing rows
 369 insert into atacc1 (test) values (2);
 370 insert into atacc1 (test) values (2);
 371 -- add a unique constraint (fails)
 372 alter table atacc1 add constraint atacc_test1 unique (test);
 373 insert into atacc1 (test) values (3);
 374 drop table atacc1;
 375
 376 -- let's do one where the unique constraint fails
 377 -- because the column doesn't exist
 378 create table atacc1 ( test int );
 379 -- add a unique constraint (fails)
 380 alter table atacc1 add constraint atacc_test1 unique (test1);
 381 drop table atacc1;
 382
 383 -- something a little more complicated
 384 create table atacc1 ( test int, test2 int);
 385 -- add a unique constraint
 386 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 387 -- insert initial value
 388 insert into atacc1 (test,test2) values (4,4);
 389 -- should fail
 390 insert into atacc1 (test,test2) values (4,4);
 391 -- should all succeed
 392 insert into atacc1 (test,test2) values (4,5);
 393 insert into atacc1 (test,test2) values (5,4);
 394 insert into atacc1 (test,test2) values (5,5);
 395 drop table atacc1;
 396
 397 -- lets do some naming tests
 398 create table atacc1 (test int, test2 int, unique(test));
 399 alter table atacc1 add unique (test2);
 400 -- should fail for @@ second one @@
 401 insert into atacc1 (test2, test) values (3, 3);
 402 insert into atacc1 (test2, test) values (2, 3);
 403 drop table atacc1;
 404
 405 -- test primary key constraint adding
 406
 407 create table atacc1 ( test int );
 408 -- add a primary key constraint
 409 alter table atacc1 add constraint atacc_test1 primary key (test);
 410 -- insert first value
 411 insert into atacc1 (test) values (2);
 412 -- should fail
 413 insert into atacc1 (test) values (2);
 414 -- should succeed
 415 insert into atacc1 (test) values (4);
 416 -- inserting NULL should fail
 417 insert into atacc1 (test) values(NULL);
 418 -- try adding a second primary key (should fail)
 419 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 420 -- drop first primary key constraint
 421 alter table atacc1 drop constraint atacc_test1 restrict;
 422 -- try adding a primary key on oid (should succeed)
 423 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 424 drop table atacc1;
 425
 426 -- let's do one where the primary key constraint fails when added
 427 create table atacc1 ( test int );
 428 -- insert soon to be failing rows
 429 insert into atacc1 (test) values (2);
 430 insert into atacc1 (test) values (2);
 431 -- add a primary key (fails)
 432 alter table atacc1 add constraint atacc_test1 primary key (test);
 433 insert into atacc1 (test) values (3);
 434 drop table atacc1;
 435
 436 -- let's do another one where the primary key constraint fails when added
 437 create table atacc1 ( test int );
 438 -- insert soon to be failing row
 439 insert into atacc1 (test) values (NULL);
 440 -- add a primary key (fails)
 441 alter table atacc1 add constraint atacc_test1 primary key (test);
 442 insert into atacc1 (test) values (3);
 443 drop table atacc1;
 444
 445 -- let's do one where the primary key constraint fails
 446 -- because the column doesn't exist
 447 create table atacc1 ( test int );
 448 -- add a primary key constraint (fails)
 449 alter table atacc1 add constraint atacc_test1 primary key (test1);
 450 drop table atacc1;
 451
 452 -- something a little more complicated
 453 create table atacc1 ( test int, test2 int);
 454 -- add a primary key constraint
 455 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 456 -- try adding a second primary key - should fail
 457 alter table atacc1 add constraint atacc_test2 primary key (test);
 458 -- insert initial value
 459 insert into atacc1 (test,test2) values (4,4);
 460 -- should fail
 461 insert into atacc1 (test,test2) values (4,4);
 462 insert into atacc1 (test,test2) values (NULL,3);
 463 insert into atacc1 (test,test2) values (3, NULL);
 464 insert into atacc1 (test,test2) values (NULL,NULL);
 465 -- should all succeed
 466 insert into atacc1 (test,test2) values (4,5);
 467 insert into atacc1 (test,test2) values (5,4);
 468 insert into atacc1 (test,test2) values (5,5);
 469 drop table atacc1;
 470
 471 -- lets do some naming tests
 472 create table atacc1 (test int, test2 int, primary key(test));
 473 -- only first should succeed
 474 insert into atacc1 (test2, test) values (3, 3);
 475 insert into atacc1 (test2, test) values (2, 3);
 476 insert into atacc1 (test2, test) values (1, NULL);
 477 drop table atacc1;
 478
 479 -- alter table / alter column [set/drop] not null tests
 480 -- try altering system catalogs, should fail
 481 alter table pg_class alter column relname drop not null;
 482 alter table pg_class alter relname set not null;
 483
 484 -- try altering non-existent table, should fail
 485 alter table non_existent alter column bar set not null;
 486 alter table non_existent alter column bar drop not null;
 487
 488 -- test setting columns to null and not null and vice versa
 489 -- test checking for null values and primary key
 490 create table atacc1 (test int not null);
 491 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 492 alter table atacc1 alter column test drop not null;
 493 alter table atacc1 drop constraint "atacc1_pkey";
 494 alter table atacc1 alter column test drop not null;
 495 insert into atacc1 values (null);
 496 alter table atacc1 alter test set not null;
 497 delete from atacc1;
 498 alter table atacc1 alter test set not null;
 499
 500 -- try altering a non-existent column, should fail
 501 alter table atacc1 alter bar set not null;
 502 alter table atacc1 alter bar drop not null;
 503
 504 -- try altering the oid column, should fail
 505 alter table atacc1 alter oid set not null;
 506 alter table atacc1 alter oid drop not null;
 507
 508 -- try creating a view and altering that, should fail
 509 create view myview as select * from atacc1;
 510 alter table myview alter column test drop not null;
 511 alter table myview alter column test set not null;
 512 drop view myview;
 513
 514 drop table atacc1;
 515
 516 -- test inheritance
 517 create table parent (a int);
 518 create table child (b varchar(255)) inherits (parent);
 519
 520 alter table parent alter a set not null;
 521 insert into parent values (NULL);
 522 insert into child (a, b) values (NULL, 'foo');
 523 alter table parent alter a drop not null;
 524 insert into parent values (NULL);
 525 insert into child (a, b) values (NULL, 'foo');
 526 alter table only parent alter a set not null;
 527 alter table child alter a set not null;
 528 delete from parent;
 529 alter table only parent alter a set not null;
 530 insert into parent values (NULL);
 531 alter table child alter a set not null;
 532 insert into child (a, b) values (NULL, 'foo');
 533 delete from child;
 534 alter table child alter a set not null;
 535 insert into child (a, b) values (NULL, 'foo');
 536 drop table child;
 537 drop table parent;
 538
 539 -- test setting and removing default values
 540 create table def_test (
 541         c1      int4 default 5,
 542         c2      text default 'initial_default'
 543 );
 544 insert into def_test default values;
 545 alter table def_test alter column c1 drop default;
 546 insert into def_test default values;
 547 alter table def_test alter column c2 drop default;
 548 insert into def_test default values;
 549 alter table def_test alter column c1 set default 10;
 550 alter table def_test alter column c2 set default 'new_default';
 551 insert into def_test default values;
 552 select * from def_test;
 553
 554 -- set defaults to an incorrect type: this should fail
 555 alter table def_test alter column c1 set default 'wrong_datatype';
 556 alter table def_test alter column c2 set default 20;
 557
 558 -- set defaults on a non-existent column: this should fail
 559 alter table def_test alter column c3 set default 30;
 560
 561 -- set defaults on views: we need to create a view, add a rule
 562 -- to allow insertions into it, and then alter the view to add
 563 -- a default
 564 create view def_view_test as select * from def_test;
 565 create rule def_view_test_ins as
 566         on insert to def_view_test
 567         do instead insert into def_test select new.*;
 568 insert into def_view_test default values;
 569 alter table def_view_test alter column c1 set default 45;
 570 insert into def_view_test default values;
 571 alter table def_view_test alter column c2 set default 'view_default';
 572 insert into def_view_test default values;
 573 select * from def_view_test;
 574
 575 drop rule def_view_test_ins on def_view_test;
 576 drop view def_view_test;
 577 drop table def_test;
 578
 579 -- alter table / drop column tests
 580 -- try altering system catalogs, should fail
 581 alter table pg_class drop column relname;
 582
 583 -- try altering non-existent table, should fail
 584 alter table foo drop column bar;
 585
 586 -- test dropping columns
 587 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
 588 insert into atacc1 values (1, 2, 3, 4);
 589 alter table atacc1 drop a;
 590 alter table atacc1 drop a;
 591
 592 -- SELECTs
 593 select * from atacc1;
 594 select * from atacc1 order by a;
 595 select * from atacc1 order by "........pg.dropped.1........";
 596 select * from atacc1 group by a;
 597 select * from atacc1 group by "........pg.dropped.1........";
 598 select atacc1.* from atacc1;
 599 select a from atacc1;
 600 select atacc1.a from atacc1;
 601 select b,c,d from atacc1;
 602 select a,b,c,d from atacc1;
 603 select * from atacc1 where a = 1;
 604 select "........pg.dropped.1........" from atacc1;
 605 select atacc1."........pg.dropped.1........" from atacc1;
 606 select "........pg.dropped.1........",b,c,d from atacc1;
 607 select * from atacc1 where "........pg.dropped.1........" = 1;
 608
 609 -- UPDATEs
 610 update atacc1 set a = 3;
 611 update atacc1 set b = 2 where a = 3;
 612 update atacc1 set "........pg.dropped.1........" = 3;
 613 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
 614
 615 -- INSERTs
 616 insert into atacc1 values (10, 11, 12, 13);
 617 insert into atacc1 values (default, 11, 12, 13);
 618 insert into atacc1 values (11, 12, 13);
 619 insert into atacc1 (a) values (10);
 620 insert into atacc1 (a) values (default);
 621 insert into atacc1 (a,b,c,d) values (10,11,12,13);
 622 insert into atacc1 (a,b,c,d) values (default,11,12,13);
 623 insert into atacc1 (b,c,d) values (11,12,13);
 624 insert into atacc1 ("........pg.dropped.1........") values (10);
 625 insert into atacc1 ("........pg.dropped.1........") values (default);
 626 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
 627 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
 628
 629 -- DELETEs
 630 delete from atacc1 where a = 3;
 631 delete from atacc1 where "........pg.dropped.1........" = 3;
 632 delete from atacc1;
 633
 634 -- try dropping a non-existent column, should fail
 635 alter table atacc1 drop bar;
 636
 637 -- try dropping the oid column, should fail
 638 alter table atacc1 drop oid;
 639
 640 -- try creating a view and altering that, should fail
 641 create view myview as select * from atacc1;
 642 select * from myview;
 643 alter table myview drop d;
 644 drop view myview;
 645
 646 -- test some commands to make sure they fail on the dropped column
 647 analyze atacc1(a);
 648 analyze atacc1("........pg.dropped.1........");
 649 vacuum analyze atacc1(a);
 650 vacuum analyze atacc1("........pg.dropped.1........");
 651 comment on column atacc1.a is 'testing';
 652 comment on column atacc1."........pg.dropped.1........" is 'testing';
 653 alter table atacc1 alter a set storage plain;
 654 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
 655 alter table atacc1 alter a set statistics 0;
 656 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
 657 alter table atacc1 alter a set default 3;
 658 alter table atacc1 alter "........pg.dropped.1........" set default 3;
 659 alter table atacc1 alter a drop default;
 660 alter table atacc1 alter "........pg.dropped.1........" drop default;
 661 alter table atacc1 alter a set not null;
 662 alter table atacc1 alter "........pg.dropped.1........" set not null;
 663 alter table atacc1 alter a drop not null;
 664 alter table atacc1 alter "........pg.dropped.1........" drop not null;
 665 alter table atacc1 rename a to x;
 666 alter table atacc1 rename "........pg.dropped.1........" to x;
 667 alter table atacc1 add primary key(a);
 668 alter table atacc1 add primary key("........pg.dropped.1........");
 669 alter table atacc1 add unique(a);
 670 alter table atacc1 add unique("........pg.dropped.1........");
 671 alter table atacc1 add check (a > 3);
 672 alter table atacc1 add check ("........pg.dropped.1........" > 3);
 673 create table atacc2 (id int4 unique);
 674 alter table atacc1 add foreign key (a) references atacc2(id);
 675 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
 676 alter table atacc2 add foreign key (id) references atacc1(a);
 677 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
 678 drop table atacc2;
 679 create index "testing_idx" on atacc1(a);
 680 create index "testing_idx" on atacc1("........pg.dropped.1........");
 681
 682 -- test create as and select into
 683 insert into atacc1 values (21, 22, 23);
 684 create table test1 as select * from atacc1;
 685 select * from test1;
 686 drop table test1;
 687 select * into test2 from atacc1;
 688 select * from test2;
 689 drop table test2;
 690
 691 -- try dropping all columns
 692 alter table atacc1 drop c;
 693 alter table atacc1 drop d;
 694 alter table atacc1 drop b;
 695 select * from atacc1;
 696
 697 drop table atacc1;
 698
 699 -- test inheritance
 700 create table parent (a int, b int, c int);
 701 insert into parent values (1, 2, 3);
 702 alter table parent drop a;
 703 create table child (d varchar(255)) inherits (parent);
 704 insert into child values (12, 13, 'testing');
 705
 706 select * from parent;
 707 select * from child;
 708 alter table parent drop c;
 709 select * from parent;
 710 select * from child;
 711
 712 drop table child;
 713 drop table parent;
 714
 715 -- test copy in/out
 716 create table test (a int4, b int4, c int4);
 717 insert into test values (1,2,3);
 718 alter table test drop a;
 719 copy test to stdout;
 720 copy test(a) to stdout;
 721 copy test("........pg.dropped.1........") to stdout;
 722 copy test from stdin;
 723 10      11      12
 724 \.
 725 SET autocommit TO 'on';
 726 select * from test;
 727 copy test from stdin;
 728 21      22
 729 \.
 730 select * from test;
 731 copy test(a) from stdin;
 732 copy test("........pg.dropped.1........") from stdin;
 733 copy test(b,c) from stdin;
 734 31      32
 735 \.
 736 select * from test;
 737 drop table test;
 738
 739 -- test inheritance
 740
 741 create table dropColumn (a int, b int, e int);
 742 create table dropColumnChild (c int) inherits (dropColumn);
 743 create table dropColumnAnother (d int) inherits (dropColumnChild);
 744
 745 -- these two should fail
 746 alter table dropColumnchild drop column a;
 747 alter table only dropColumnChild drop column b;
 748
 749 -- these three should work
 750 alter table only dropColumn drop column e;
 751 alter table dropColumnChild drop column c;
 752 alter table dropColumn drop column a;
 753
 754 create table renameColumn (a int);
 755 create table renameColumnChild (b int) inherits (renameColumn);
 756 create table renameColumnAnother (c int) inherits (renameColumnChild);
 757
 758 -- these three should fail
 759 alter table renameColumnChild rename column a to d;
 760 alter table only renameColumnChild rename column a to d;
 761 alter table only renameColumn rename column a to d;
 762
 763 -- these should work
 764 alter table renameColumn rename column a to d;
 765 alter table renameColumnChild rename column b to a;
 766
 767 -- this should work
 768 alter table renameColumn add column w int;
 769
 770 -- this should fail
 771 alter table only renameColumn add column x int;
 772
 773
 774 -- Test corner cases in dropping of inherited columns
 775
 776 create table p1 (f1 int, f2 int);
 777 create table c1 (f1 int not null) inherits(p1);
 778
 779 -- should be rejected since c1.f1 is inherited
 780 alter table c1 drop column f1;
 781 -- should work
 782 alter table p1 drop column f1;
 783 -- c1.f1 is still there, but no longer inherited
 784 select f1 from c1;
 785 alter table c1 drop column f1;
 786 select f1 from c1;
 787
 788 drop table p1 cascade;
 789
 790 create table p1 (f1 int, f2 int);
 791 create table c1 () inherits(p1);
 792
 793 -- should be rejected since c1.f1 is inherited
 794 alter table c1 drop column f1;
 795 alter table p1 drop column f1;
 796 -- c1.f1 is dropped now, since there is no local definition for it
 797 select f1 from c1;
 798
 799 drop table p1 cascade;
 800
 801 create table p1 (f1 int, f2 int);
 802 create table c1 () inherits(p1);
 803
 804 -- should be rejected since c1.f1 is inherited
 805 alter table c1 drop column f1;
 806 alter table only p1 drop column f1;
 807 -- c1.f1 is NOT dropped, but must now be considered non-inherited
 808 alter table c1 drop column f1;
 809
 810 drop table p1 cascade;
 811
 812 create table p1 (f1 int, f2 int);
 813 create table c1 (f1 int not null) inherits(p1);
 814
 815 -- should be rejected since c1.f1 is inherited
 816 alter table c1 drop column f1;
 817 alter table only p1 drop column f1;
 818 -- c1.f1 is still there, but no longer inherited
 819 alter table c1 drop column f1;
 820
 821 drop table p1 cascade;
 822
 823 create table p1(id int, name text);
 824 create table p2(id2 int, name text, height int);
 825 create table c1(age int) inherits(p1,p2);
 826 create table gc1() inherits (c1);
 827
 828 select relname, attname, attinhcount, attislocal
 829 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 830 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 831 order by relname, attnum;
 832
 833 -- should work
 834 alter table only p1 drop column name;
 835 -- should work. Now c1.name is local and inhcount is 0.
 836 alter table p2 drop column name;
 837 -- should be rejected since its inherited
 838 alter table gc1 drop column name;
 839 -- should work, and drop gc1.name along
 840 alter table c1 drop column name;
 841 -- should fail: column does not exist
 842 alter table gc1 drop column name;
 843 -- should work and drop the attribute in all tables
 844 alter table p2 drop column height;
 845
 846 select relname, attname, attinhcount, attislocal
 847 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 848 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 849 order by relname, attnum;
 850
 851 drop table p1, p2 cascade;
 852
 853 -- test renumbering of child-table columns in inherited operations
 854
 855 create table p1 (f1 int);
 856 create table c1 (f2 text, f3 int) inherits (p1);
 857
 858 alter table p1 add column a1 int check (a1 > 0);
 859 alter table p1 add column f2 text;
 860
 861 insert into p1 values (1,2,'abc');
 862 insert into c1 values(11,'xyz',33,0); -- should fail
 863 insert into c1 values(11,'xyz',33,22);
 864
 865 select * from p1;
 866 update p1 set a1 = a1 + 1, f2 = upper(f2);
 867 select * from p1;
 868
 869 drop table p1 cascade;