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 -- analyze to ensure we get an indexscan here
 175 ANALYZE tenk1;
 176 -- 5 values, sorted
 177 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 178 DROP VIEW tmp_view_new;
 179 -- toast-like relation name
 180 alter table stud_emp rename to pg_toast_stud_emp;
 181 alter table pg_toast_stud_emp rename to stud_emp;
 182
 183 -- FOREIGN KEY CONSTRAINT adding TEST
 184
 185 CREATE TABLE tmp2 (a int primary key);
 186
 187 CREATE TABLE tmp3 (a int, b int);
 188
 189 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 190
 191 CREATE TABLE tmp5 (a int, b int);
 192
 193 -- Insert rows into tmp2 (pktable)
 194 INSERT INTO tmp2 values (1);
 195 INSERT INTO tmp2 values (2);
 196 INSERT INTO tmp2 values (3);
 197 INSERT INTO tmp2 values (4);
 198
 199 -- Insert rows into tmp3
 200 INSERT INTO tmp3 values (1,10);
 201 INSERT INTO tmp3 values (1,20);
 202 INSERT INTO tmp3 values (5,50);
 203
 204 -- Try (and fail) to add constraint due to invalid source columns
 205 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
 206
 207 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
 208 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
 209
 210 -- Try (and fail) to add constraint due to invalid data
 211 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 212
 213 -- Delete failing row
 214 DELETE FROM tmp3 where a=5;
 215
 216 -- Try (and succeed)
 217 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 218
 219 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 220 -- tmp4 is a,b
 221
 222 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 223
 224 DROP TABLE tmp5;
 225
 226 DROP TABLE tmp4;
 227
 228 DROP TABLE tmp3;
 229
 230 DROP TABLE tmp2;
 231
 232 -- Foreign key adding test with mixed types
 233
 234 -- Note: these tables are TEMP to avoid name conflicts when this test
 235 -- is run in parallel with foreign_key.sql.
 236
 237 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 238 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 239 -- This next should fail, because inet=int does not exist
 240 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 241 -- This should also fail for the same reason, but here we
 242 -- give the column name
 243 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 244 -- This should succeed, even though they are different types
 245 -- because varchar=int does exist
 246 DROP TABLE FKTABLE;
 247 CREATE TEMP TABLE FKTABLE (ftest1 varchar);
 248 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 249 -- As should this
 250 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 251 DROP TABLE pktable cascade;
 252 DROP TABLE fktable;
 253
 254 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 255                            PRIMARY KEY(ptest1, ptest2));
 256 -- This should fail, because we just chose really odd types
 257 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 258 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 259 DROP TABLE FKTABLE;
 260 -- Again, so should this...
 261 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 262 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 263      references pktable(ptest1, ptest2);
 264 DROP TABLE FKTABLE;
 265 -- This fails because we mixed up the column ordering
 266 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 267 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 268      references pktable(ptest2, ptest1);
 269 -- As does this...
 270 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 271      references pktable(ptest1, ptest2);
 272
 273 -- temp tables should go away by themselves, need not drop them.
 274
 275 -- test check constraint adding
 276
 277 create table atacc1 ( test int );
 278 -- add a check constraint
 279 alter table atacc1 add constraint atacc_test1 check (test>3);
 280 -- should fail
 281 insert into atacc1 (test) values (2);
 282 -- should succeed
 283 insert into atacc1 (test) values (4);
 284 drop table atacc1;
 285
 286 -- let's do one where the check fails when added
 287 create table atacc1 ( test int );
 288 -- insert a soon to be failing row
 289 insert into atacc1 (test) values (2);
 290 -- add a check constraint (fails)
 291 alter table atacc1 add constraint atacc_test1 check (test>3);
 292 insert into atacc1 (test) values (4);
 293 drop table atacc1;
 294
 295 -- let's do one where the check fails because the column doesn't exist
 296 create table atacc1 ( test int );
 297 -- add a check constraint (fails)
 298 alter table atacc1 add constraint atacc_test1 check (test1>3);
 299 drop table atacc1;
 300
 301 -- something a little more complicated
 302 create table atacc1 ( test int, test2 int, test3 int);
 303 -- add a check constraint (fails)
 304 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 305 -- should fail
 306 insert into atacc1 (test,test2,test3) values (4,4,2);
 307 -- should succeed
 308 insert into atacc1 (test,test2,test3) values (4,4,5);
 309 drop table atacc1;
 310
 311 -- lets do some naming tests
 312 create table atacc1 (test int check (test>3), test2 int);
 313 alter table atacc1 add check (test2>test);
 314 -- should fail for $2
 315 insert into atacc1 (test2, test) values (3, 4);
 316 drop table atacc1;
 317
 318 -- inheritance related tests
 319 create table atacc1 (test int);
 320 create table atacc2 (test2 int);
 321 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 322 alter table atacc2 add constraint foo check (test2>0);
 323 -- fail and then succeed on atacc2
 324 insert into atacc2 (test2) values (-3);
 325 insert into atacc2 (test2) values (3);
 326 -- fail and then succeed on atacc3
 327 insert into atacc3 (test2) values (-3);
 328 insert into atacc3 (test2) values (3);
 329 drop table atacc3;
 330 drop table atacc2;
 331 drop table atacc1;
 332
 333 -- let's try only to add only to the parent
 334
 335 create table atacc1 (test int);
 336 create table atacc2 (test2 int);
 337 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 338 alter table only atacc2 add constraint foo check (test2>0);
 339 -- fail and then succeed on atacc2
 340 insert into atacc2 (test2) values (-3);
 341 insert into atacc2 (test2) values (3);
 342 -- both succeed on atacc3
 343 insert into atacc3 (test2) values (-3);
 344 insert into atacc3 (test2) values (3);
 345 drop table atacc3;
 346 drop table atacc2;
 347 drop table atacc1;
 348
 349 -- test unique constraint adding
 350
 351 create table atacc1 ( test int );
 352 -- add a unique constraint
 353 alter table atacc1 add constraint atacc_test1 unique (test);
 354 -- insert first value
 355 insert into atacc1 (test) values (2);
 356 -- should fail
 357 insert into atacc1 (test) values (2);
 358 -- should succeed
 359 insert into atacc1 (test) values (4);
 360 -- try adding a unique oid constraint
 361 alter table atacc1 add constraint atacc_oid1 unique(oid);
 362 drop table atacc1;
 363
 364 -- let's do one where the unique constraint fails when added
 365 create table atacc1 ( test int );
 366 -- insert soon to be failing rows
 367 insert into atacc1 (test) values (2);
 368 insert into atacc1 (test) values (2);
 369 -- add a unique constraint (fails)
 370 alter table atacc1 add constraint atacc_test1 unique (test);
 371 insert into atacc1 (test) values (3);
 372 drop table atacc1;
 373
 374 -- let's do one where the unique constraint fails
 375 -- because the column doesn't exist
 376 create table atacc1 ( test int );
 377 -- add a unique constraint (fails)
 378 alter table atacc1 add constraint atacc_test1 unique (test1);
 379 drop table atacc1;
 380
 381 -- something a little more complicated
 382 create table atacc1 ( test int, test2 int);
 383 -- add a unique constraint
 384 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 385 -- insert initial value
 386 insert into atacc1 (test,test2) values (4,4);
 387 -- should fail
 388 insert into atacc1 (test,test2) values (4,4);
 389 -- should all succeed
 390 insert into atacc1 (test,test2) values (4,5);
 391 insert into atacc1 (test,test2) values (5,4);
 392 insert into atacc1 (test,test2) values (5,5);
 393 drop table atacc1;
 394
 395 -- lets do some naming tests
 396 create table atacc1 (test int, test2 int, unique(test));
 397 alter table atacc1 add unique (test2);
 398 -- should fail for @@ second one @@
 399 insert into atacc1 (test2, test) values (3, 3);
 400 insert into atacc1 (test2, test) values (2, 3);
 401 drop table atacc1;
 402
 403 -- test primary key constraint adding
 404
 405 create table atacc1 ( test int );
 406 -- add a primary key constraint
 407 alter table atacc1 add constraint atacc_test1 primary key (test);
 408 -- insert first value
 409 insert into atacc1 (test) values (2);
 410 -- should fail
 411 insert into atacc1 (test) values (2);
 412 -- should succeed
 413 insert into atacc1 (test) values (4);
 414 -- inserting NULL should fail
 415 insert into atacc1 (test) values(NULL);
 416 -- try adding a second primary key (should fail)
 417 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 418 -- drop first primary key constraint
 419 alter table atacc1 drop constraint atacc_test1 restrict;
 420 -- try adding a primary key on oid (should succeed)
 421 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 422 drop table atacc1;
 423
 424 -- let's do one where the primary key 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 primary key (fails)
 430 alter table atacc1 add constraint atacc_test1 primary key (test);
 431 insert into atacc1 (test) values (3);
 432 drop table atacc1;
 433
 434 -- let's do another one where the primary key constraint fails when added
 435 create table atacc1 ( test int );
 436 -- insert soon to be failing row
 437 insert into atacc1 (test) values (NULL);
 438 -- add a primary key (fails)
 439 alter table atacc1 add constraint atacc_test1 primary key (test);
 440 insert into atacc1 (test) values (3);
 441 drop table atacc1;
 442
 443 -- let's do one where the primary key constraint fails
 444 -- because the column doesn't exist
 445 create table atacc1 ( test int );
 446 -- add a primary key constraint (fails)
 447 alter table atacc1 add constraint atacc_test1 primary key (test1);
 448 drop table atacc1;
 449
 450 -- something a little more complicated
 451 create table atacc1 ( test int, test2 int);
 452 -- add a primary key constraint
 453 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 454 -- try adding a second primary key - should fail
 455 alter table atacc1 add constraint atacc_test2 primary key (test);
 456 -- insert initial value
 457 insert into atacc1 (test,test2) values (4,4);
 458 -- should fail
 459 insert into atacc1 (test,test2) values (4,4);
 460 insert into atacc1 (test,test2) values (NULL,3);
 461 insert into atacc1 (test,test2) values (3, NULL);
 462 insert into atacc1 (test,test2) values (NULL,NULL);
 463 -- should all succeed
 464 insert into atacc1 (test,test2) values (4,5);
 465 insert into atacc1 (test,test2) values (5,4);
 466 insert into atacc1 (test,test2) values (5,5);
 467 drop table atacc1;
 468
 469 -- lets do some naming tests
 470 create table atacc1 (test int, test2 int, primary key(test));
 471 -- only first should succeed
 472 insert into atacc1 (test2, test) values (3, 3);
 473 insert into atacc1 (test2, test) values (2, 3);
 474 insert into atacc1 (test2, test) values (1, NULL);
 475 drop table atacc1;
 476
 477 -- alter table / alter column [set/drop] not null tests
 478 -- try altering system catalogs, should fail
 479 alter table pg_class alter column relname drop not null;
 480 alter table pg_class alter relname set not null;
 481
 482 -- try altering non-existent table, should fail
 483 alter table non_existent alter column bar set not null;
 484 alter table non_existent alter column bar drop not null;
 485
 486 -- test setting columns to null and not null and vice versa
 487 -- test checking for null values and primary key
 488 create table atacc1 (test int not null);
 489 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 490 alter table atacc1 alter column test drop not null;
 491 alter table atacc1 drop constraint "atacc1_pkey";
 492 alter table atacc1 alter column test drop not null;
 493 insert into atacc1 values (null);
 494 alter table atacc1 alter test set not null;
 495 delete from atacc1;
 496 alter table atacc1 alter test set not null;
 497
 498 -- try altering a non-existent column, should fail
 499 alter table atacc1 alter bar set not null;
 500 alter table atacc1 alter bar drop not null;
 501
 502 -- try altering the oid column, should fail
 503 alter table atacc1 alter oid set not null;
 504 alter table atacc1 alter oid drop not null;
 505
 506 -- try creating a view and altering that, should fail
 507 create view myview as select * from atacc1;
 508 alter table myview alter column test drop not null;
 509 alter table myview alter column test set not null;
 510 drop view myview;
 511
 512 drop table atacc1;
 513
 514 -- test inheritance
 515 create table parent (a int);
 516 create table child (b varchar(255)) inherits (parent);
 517
 518 alter table parent alter a set not null;
 519 insert into parent values (NULL);
 520 insert into child (a, b) values (NULL, 'foo');
 521 alter table parent alter a drop not null;
 522 insert into parent values (NULL);
 523 insert into child (a, b) values (NULL, 'foo');
 524 alter table only parent alter a set not null;
 525 alter table child alter a set not null;
 526 delete from parent;
 527 alter table only parent alter a set not null;
 528 insert into parent values (NULL);
 529 alter table child alter a set not null;
 530 insert into child (a, b) values (NULL, 'foo');
 531 delete from child;
 532 alter table child alter a set not null;
 533 insert into child (a, b) values (NULL, 'foo');
 534 drop table child;
 535 drop table parent;
 536
 537 -- test setting and removing default values
 538 create table def_test (
 539         c1      int4 default 5,
 540         c2      text default 'initial_default'
 541 );
 542 insert into def_test default values;
 543 alter table def_test alter column c1 drop default;
 544 insert into def_test default values;
 545 alter table def_test alter column c2 drop default;
 546 insert into def_test default values;
 547 alter table def_test alter column c1 set default 10;
 548 alter table def_test alter column c2 set default 'new_default';
 549 insert into def_test default values;
 550 select * from def_test;
 551
 552 -- set defaults to an incorrect type: this should fail
 553 alter table def_test alter column c1 set default 'wrong_datatype';
 554 alter table def_test alter column c2 set default 20;
 555
 556 -- set defaults on a non-existent column: this should fail
 557 alter table def_test alter column c3 set default 30;
 558
 559 -- set defaults on views: we need to create a view, add a rule
 560 -- to allow insertions into it, and then alter the view to add
 561 -- a default
 562 create view def_view_test as select * from def_test;
 563 create rule def_view_test_ins as
 564         on insert to def_view_test
 565         do instead insert into def_test select new.*;
 566 insert into def_view_test default values;
 567 alter table def_view_test alter column c1 set default 45;
 568 insert into def_view_test default values;
 569 alter table def_view_test alter column c2 set default 'view_default';
 570 insert into def_view_test default values;
 571 select * from def_view_test;
 572
 573 drop rule def_view_test_ins on def_view_test;
 574 drop view def_view_test;
 575 drop table def_test;
 576
 577 -- alter table / drop column tests
 578 -- try altering system catalogs, should fail
 579 alter table pg_class drop column relname;
 580
 581 -- try altering non-existent table, should fail
 582 alter table nosuchtable drop column bar;
 583
 584 -- test dropping columns
 585 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
 586 insert into atacc1 values (1, 2, 3, 4);
 587 alter table atacc1 drop a;
 588 alter table atacc1 drop a;
 589
 590 -- SELECTs
 591 select * from atacc1;
 592 select * from atacc1 order by a;
 593 select * from atacc1 order by "........pg.dropped.1........";
 594 select * from atacc1 group by a;
 595 select * from atacc1 group by "........pg.dropped.1........";
 596 select atacc1.* from atacc1;
 597 select a from atacc1;
 598 select atacc1.a from atacc1;
 599 select b,c,d from atacc1;
 600 select a,b,c,d from atacc1;
 601 select * from atacc1 where a = 1;
 602 select "........pg.dropped.1........" from atacc1;
 603 select atacc1."........pg.dropped.1........" from atacc1;
 604 select "........pg.dropped.1........",b,c,d from atacc1;
 605 select * from atacc1 where "........pg.dropped.1........" = 1;
 606
 607 -- UPDATEs
 608 update atacc1 set a = 3;
 609 update atacc1 set b = 2 where a = 3;
 610 update atacc1 set "........pg.dropped.1........" = 3;
 611 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
 612
 613 -- INSERTs
 614 insert into atacc1 values (10, 11, 12, 13);
 615 insert into atacc1 values (default, 11, 12, 13);
 616 insert into atacc1 values (11, 12, 13);
 617 insert into atacc1 (a) values (10);
 618 insert into atacc1 (a) values (default);
 619 insert into atacc1 (a,b,c,d) values (10,11,12,13);
 620 insert into atacc1 (a,b,c,d) values (default,11,12,13);
 621 insert into atacc1 (b,c,d) values (11,12,13);
 622 insert into atacc1 ("........pg.dropped.1........") values (10);
 623 insert into atacc1 ("........pg.dropped.1........") values (default);
 624 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
 625 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
 626
 627 -- DELETEs
 628 delete from atacc1 where a = 3;
 629 delete from atacc1 where "........pg.dropped.1........" = 3;
 630 delete from atacc1;
 631
 632 -- try dropping a non-existent column, should fail
 633 alter table atacc1 drop bar;
 634
 635 -- try dropping the oid column, should succeed
 636 alter table atacc1 drop oid;
 637
 638 -- try dropping the xmin column, should fail
 639 alter table atacc1 drop xmin;
 640
 641 -- try creating a view and altering that, should fail
 642 create view myview as select * from atacc1;
 643 select * from myview;
 644 alter table myview drop d;
 645 drop view myview;
 646
 647 -- test some commands to make sure they fail on the dropped column
 648 analyze atacc1(a);
 649 analyze atacc1("........pg.dropped.1........");
 650 vacuum analyze atacc1(a);
 651 vacuum analyze atacc1("........pg.dropped.1........");
 652 comment on column atacc1.a is 'testing';
 653 comment on column atacc1."........pg.dropped.1........" is 'testing';
 654 alter table atacc1 alter a set storage plain;
 655 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
 656 alter table atacc1 alter a set statistics 0;
 657 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
 658 alter table atacc1 alter a set default 3;
 659 alter table atacc1 alter "........pg.dropped.1........" set default 3;
 660 alter table atacc1 alter a drop default;
 661 alter table atacc1 alter "........pg.dropped.1........" drop default;
 662 alter table atacc1 alter a set not null;
 663 alter table atacc1 alter "........pg.dropped.1........" set not null;
 664 alter table atacc1 alter a drop not null;
 665 alter table atacc1 alter "........pg.dropped.1........" drop not null;
 666 alter table atacc1 rename a to x;
 667 alter table atacc1 rename "........pg.dropped.1........" to x;
 668 alter table atacc1 add primary key(a);
 669 alter table atacc1 add primary key("........pg.dropped.1........");
 670 alter table atacc1 add unique(a);
 671 alter table atacc1 add unique("........pg.dropped.1........");
 672 alter table atacc1 add check (a > 3);
 673 alter table atacc1 add check ("........pg.dropped.1........" > 3);
 674 create table atacc2 (id int4 unique);
 675 alter table atacc1 add foreign key (a) references atacc2(id);
 676 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
 677 alter table atacc2 add foreign key (id) references atacc1(a);
 678 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
 679 drop table atacc2;
 680 create index "testing_idx" on atacc1(a);
 681 create index "testing_idx" on atacc1("........pg.dropped.1........");
 682
 683 -- test create as and select into
 684 insert into atacc1 values (21, 22, 23);
 685 create table test1 as select * from atacc1;
 686 select * from test1;
 687 drop table test1;
 688 select * into test2 from atacc1;
 689 select * from test2;
 690 drop table test2;
 691
 692 -- try dropping all columns
 693 alter table atacc1 drop c;
 694 alter table atacc1 drop d;
 695 alter table atacc1 drop b;
 696 select * from atacc1;
 697
 698 drop table atacc1;
 699
 700 -- test inheritance
 701 create table parent (a int, b int, c int);
 702 insert into parent values (1, 2, 3);
 703 alter table parent drop a;
 704 create table child (d varchar(255)) inherits (parent);
 705 insert into child values (12, 13, 'testing');
 706
 707 select * from parent;
 708 select * from child;
 709 alter table parent drop c;
 710 select * from parent;
 711 select * from child;
 712
 713 drop table child;
 714 drop table parent;
 715
 716 -- test copy in/out
 717 create table test (a int4, b int4, c int4);
 718 insert into test values (1,2,3);
 719 alter table test drop a;
 720 copy test to stdout;
 721 copy test(a) to stdout;
 722 copy test("........pg.dropped.1........") to stdout;
 723 copy test from stdin;
 724 10      11      12
 725 \.
 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 --
 854 -- Test the ALTER TABLE WITHOUT OIDS command
 855 --
 856 create table altstartwith (col integer) with oids;
 857
 858 insert into altstartwith values (1);
 859
 860 select oid > 0, * from altstartwith;
 861
 862 alter table altstartwith set without oids;
 863
 864 select oid > 0, * from altstartwith; -- fails
 865 select * from altstartwith;
 866
 867 -- Run inheritance tests
 868 create table altwithoid (col integer) with oids;
 869
 870 -- Inherits parents oid column
 871 create table altinhoid () inherits (altwithoid) without oids;
 872
 873 insert into altinhoid values (1);
 874
 875 select oid > 0, * from altwithoid;
 876 select oid > 0, * from altinhoid;
 877
 878 alter table altwithoid set without oids;
 879 alter table altinhoid set without oids;
 880
 881 select oid > 0, * from altwithoid; -- fails
 882 select oid > 0, * from altinhoid; -- fails
 883 select * from altwithoid;
 884 select * from altinhoid;
 885
 886 -- test renumbering of child-table columns in inherited operations
 887
 888 create table p1 (f1 int);
 889 create table c1 (f2 text, f3 int) inherits (p1);
 890
 891 alter table p1 add column a1 int check (a1 > 0);
 892 alter table p1 add column f2 text;
 893
 894 insert into p1 values (1,2,'abc');
 895 insert into c1 values(11,'xyz',33,0); -- should fail
 896 insert into c1 values(11,'xyz',33,22);
 897
 898 select * from p1;
 899 update p1 set a1 = a1 + 1, f2 = upper(f2);
 900 select * from p1;
 901
 902 drop table p1 cascade;
 903
 904 -- test that operations with a dropped column do not try to reference
 905 -- its datatype
 906
 907 create domain mytype as text;
 908 create temp table foo (f1 text, f2 mytype, f3 text);
 909
 910 insert into foo values('aa','bb','cc');
 911 select * from foo;
 912
 913 drop domain mytype cascade;
 914
 915 select * from foo;
 916 insert into foo values('qq','rr');
 917 select * from foo;
 918 update foo set f3 = 'zz';
 919 select * from foo;
 920 select f3,max(f1) from foo group by f3;
 921
 922 -- Simple tests for alter table column type
 923 alter table foo alter f1 TYPE integer; -- fails
 924 alter table foo alter f1 TYPE varchar(10);
 925
 926 create table anothertab (atcol1 serial8, atcol2 boolean,
 927         constraint anothertab_chk check (atcol1 <= 3));
 928
 929 insert into anothertab (atcol1, atcol2) values (default, true);
 930 insert into anothertab (atcol1, atcol2) values (default, false);
 931 select * from anothertab;
 932
 933 alter table anothertab alter column atcol1 type boolean; -- fails
 934 alter table anothertab alter column atcol1 type integer;
 935
 936 select * from anothertab;
 937
 938 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
 939 insert into anothertab (atcol1, atcol2) values (default, null);
 940
 941 select * from anothertab;
 942
 943 alter table anothertab alter column atcol2 type text
 944       using case when atcol2 is true then 'IT WAS TRUE'
 945                  when atcol2 is false then 'IT WAS FALSE'
 946                  else 'IT WAS NULL!' end;
 947
 948 select * from anothertab;
 949 alter table anothertab alter column atcol1 type boolean
 950         using case when atcol1 % 2 = 0 then true else false end; -- fails
 951 alter table anothertab alter column atcol1 drop default;
 952 alter table anothertab alter column atcol1 type boolean
 953         using case when atcol1 % 2 = 0 then true else false end; -- fails
 954 alter table anothertab drop constraint anothertab_chk;
 955
 956 alter table anothertab alter column atcol1 type boolean
 957         using case when atcol1 % 2 = 0 then true else false end;
 958
 959 select * from anothertab;
 960
 961 drop table anothertab;
 962
 963 create table another (f1 int, f2 text);
 964
 965 insert into another values(1, 'one');
 966 insert into another values(2, 'two');
 967 insert into another values(3, 'three');
 968
 969 select * from another;
 970
 971 alter table another
 972   alter f1 type text using f2 || ' more',
 973   alter f2 type bigint using f1 * 10;
 974
 975 select * from another;
 976
 977 drop table another;