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 ALTER TABLE tmp3 drop constraint tmpconstr;
 225
 226 INSERT INTO tmp3 values (5,50);
 227
 228 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
 229 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
 230 ALTER TABLE tmp3 validate constraint tmpconstr;
 231
 232 -- Delete failing row
 233 DELETE FROM tmp3 where a=5;
 234
 235 -- Try (and succeed) and repeat to show it works on already valid constraint
 236 ALTER TABLE tmp3 validate constraint tmpconstr;
 237 ALTER TABLE tmp3 validate constraint tmpconstr;
 238
 239
 240 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 241 -- tmp4 is a,b
 242
 243 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 244
 245 DROP TABLE tmp5;
 246
 247 DROP TABLE tmp4;
 248
 249 DROP TABLE tmp3;
 250
 251 DROP TABLE tmp2;
 252
 253 -- Foreign key adding test with mixed types
 254
 255 -- Note: these tables are TEMP to avoid name conflicts when this test
 256 -- is run in parallel with foreign_key.sql.
 257
 258 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 259 INSERT INTO PKTABLE VALUES(42);
 260 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 261 -- This next should fail, because int=inet does not exist
 262 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 263 -- This should also fail for the same reason, but here we
 264 -- give the column name
 265 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 266 DROP TABLE FKTABLE;
 267 -- This should succeed, even though they are different types,
 268 -- because int=int8 exists and is a member of the integer opfamily
 269 CREATE TEMP TABLE FKTABLE (ftest1 int8);
 270 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 271 -- Check it actually works
 272 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 273 INSERT INTO FKTABLE VALUES(43);         -- should fail
 274 DROP TABLE FKTABLE;
 275 -- This should fail, because we'd have to cast numeric to int which is
 276 -- not an implicit coercion (or use numeric=numeric, but that's not part
 277 -- of the integer opfamily)
 278 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
 279 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 280 DROP TABLE FKTABLE;
 281 DROP TABLE PKTABLE;
 282 -- On the other hand, this should work because int implicitly promotes to
 283 -- numeric, and we allow promotion on the FK side
 284 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
 285 INSERT INTO PKTABLE VALUES(42);
 286 CREATE TEMP TABLE FKTABLE (ftest1 int);
 287 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 288 -- Check it actually works
 289 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 290 INSERT INTO FKTABLE VALUES(43);         -- should fail
 291 DROP TABLE FKTABLE;
 292 DROP TABLE PKTABLE;
 293
 294 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 295                            PRIMARY KEY(ptest1, ptest2));
 296 -- This should fail, because we just chose really odd types
 297 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 298 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 299 DROP TABLE FKTABLE;
 300 -- Again, so should this...
 301 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 302 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 303      references pktable(ptest1, ptest2);
 304 DROP TABLE FKTABLE;
 305 -- This fails because we mixed up the column ordering
 306 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 307 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 308      references pktable(ptest2, ptest1);
 309 -- As does this...
 310 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 311      references pktable(ptest1, ptest2);
 312
 313 -- temp tables should go away by themselves, need not drop them.
 314
 315 -- test check constraint adding
 316
 317 create table atacc1 ( test int );
 318 -- add a check constraint
 319 alter table atacc1 add constraint atacc_test1 check (test>3);
 320 -- should fail
 321 insert into atacc1 (test) values (2);
 322 -- should succeed
 323 insert into atacc1 (test) values (4);
 324 drop table atacc1;
 325
 326 -- let's do one where the check fails when added
 327 create table atacc1 ( test int );
 328 -- insert a soon to be failing row
 329 insert into atacc1 (test) values (2);
 330 -- add a check constraint (fails)
 331 alter table atacc1 add constraint atacc_test1 check (test>3);
 332 insert into atacc1 (test) values (4);
 333 drop table atacc1;
 334
 335 -- let's do one where the check fails because the column doesn't exist
 336 create table atacc1 ( test int );
 337 -- add a check constraint (fails)
 338 alter table atacc1 add constraint atacc_test1 check (test1>3);
 339 drop table atacc1;
 340
 341 -- something a little more complicated
 342 create table atacc1 ( test int, test2 int, test3 int);
 343 -- add a check constraint (fails)
 344 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 345 -- should fail
 346 insert into atacc1 (test,test2,test3) values (4,4,2);
 347 -- should succeed
 348 insert into atacc1 (test,test2,test3) values (4,4,5);
 349 drop table atacc1;
 350
 351 -- lets do some naming tests
 352 create table atacc1 (test int check (test>3), test2 int);
 353 alter table atacc1 add check (test2>test);
 354 -- should fail for $2
 355 insert into atacc1 (test2, test) values (3, 4);
 356 drop table atacc1;
 357
 358 -- inheritance related tests
 359 create table atacc1 (test int);
 360 create table atacc2 (test2 int);
 361 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 362 alter table atacc2 add constraint foo check (test2>0);
 363 -- fail and then succeed on atacc2
 364 insert into atacc2 (test2) values (-3);
 365 insert into atacc2 (test2) values (3);
 366 -- fail and then succeed on atacc3
 367 insert into atacc3 (test2) values (-3);
 368 insert into atacc3 (test2) values (3);
 369 drop table atacc3;
 370 drop table atacc2;
 371 drop table atacc1;
 372
 373 -- same things with one created with INHERIT
 374 create table atacc1 (test int);
 375 create table atacc2 (test2 int);
 376 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 377 alter table atacc3 no inherit atacc2;
 378 -- fail
 379 alter table atacc3 no inherit atacc2;
 380 -- make sure it really isn't a child
 381 insert into atacc3 (test2) values (3);
 382 select test2 from atacc2;
 383 -- fail due to missing constraint
 384 alter table atacc2 add constraint foo check (test2>0);
 385 alter table atacc3 inherit atacc2;
 386 -- fail due to missing column
 387 alter table atacc3 rename test2 to testx;
 388 alter table atacc3 inherit atacc2;
 389 -- fail due to mismatched data type
 390 alter table atacc3 add test2 bool;
 391 alter table atacc3 inherit atacc2;
 392 alter table atacc3 drop test2;
 393 -- succeed
 394 alter table atacc3 add test2 int;
 395 update atacc3 set test2 = 4 where test2 is null;
 396 alter table atacc3 add constraint foo check (test2>0);
 397 alter table atacc3 inherit atacc2;
 398 -- fail due to duplicates and circular inheritance
 399 alter table atacc3 inherit atacc2;
 400 alter table atacc2 inherit atacc3;
 401 alter table atacc2 inherit atacc2;
 402 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
 403 select test2 from atacc2;
 404 drop table atacc2 cascade;
 405 drop table atacc1;
 406
 407 -- adding only to a parent is disallowed as of 8.4
 408
 409 create table atacc1 (test int);
 410 create table atacc2 (test2 int) inherits (atacc1);
 411 -- fail:
 412 alter table only atacc1 add constraint foo check (test>0);
 413 -- ok:
 414 alter table only atacc2 add constraint foo check (test>0);
 415 -- check constraint not there on parent
 416 insert into atacc1 (test) values (-3);
 417 insert into atacc1 (test) values (3);
 418 -- check constraint is there on child
 419 insert into atacc2 (test) values (-3);
 420 insert into atacc2 (test) values (3);
 421 drop table atacc2;
 422 drop table atacc1;
 423
 424 -- test unique constraint adding
 425
 426 create table atacc1 ( test int ) with oids;
 427 -- add a unique constraint
 428 alter table atacc1 add constraint atacc_test1 unique (test);
 429 -- insert first value
 430 insert into atacc1 (test) values (2);
 431 -- should fail
 432 insert into atacc1 (test) values (2);
 433 -- should succeed
 434 insert into atacc1 (test) values (4);
 435 -- try adding a unique oid constraint
 436 alter table atacc1 add constraint atacc_oid1 unique(oid);
 437 -- try to create duplicates via alter table using - should fail
 438 alter table atacc1 alter column test type integer using 0;
 439 drop table atacc1;
 440
 441 -- let's do one where the unique constraint fails when added
 442 create table atacc1 ( test int );
 443 -- insert soon to be failing rows
 444 insert into atacc1 (test) values (2);
 445 insert into atacc1 (test) values (2);
 446 -- add a unique constraint (fails)
 447 alter table atacc1 add constraint atacc_test1 unique (test);
 448 insert into atacc1 (test) values (3);
 449 drop table atacc1;
 450
 451 -- let's do one where the unique constraint fails
 452 -- because the column doesn't exist
 453 create table atacc1 ( test int );
 454 -- add a unique constraint (fails)
 455 alter table atacc1 add constraint atacc_test1 unique (test1);
 456 drop table atacc1;
 457
 458 -- something a little more complicated
 459 create table atacc1 ( test int, test2 int);
 460 -- add a unique constraint
 461 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 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 -- should all succeed
 467 insert into atacc1 (test,test2) values (4,5);
 468 insert into atacc1 (test,test2) values (5,4);
 469 insert into atacc1 (test,test2) values (5,5);
 470 drop table atacc1;
 471
 472 -- lets do some naming tests
 473 create table atacc1 (test int, test2 int, unique(test));
 474 alter table atacc1 add unique (test2);
 475 -- should fail for @@ second one @@
 476 insert into atacc1 (test2, test) values (3, 3);
 477 insert into atacc1 (test2, test) values (2, 3);
 478 drop table atacc1;
 479
 480 -- test primary key constraint adding
 481
 482 create table atacc1 ( test int ) with oids;
 483 -- add a primary key constraint
 484 alter table atacc1 add constraint atacc_test1 primary key (test);
 485 -- insert first value
 486 insert into atacc1 (test) values (2);
 487 -- should fail
 488 insert into atacc1 (test) values (2);
 489 -- should succeed
 490 insert into atacc1 (test) values (4);
 491 -- inserting NULL should fail
 492 insert into atacc1 (test) values(NULL);
 493 -- try adding a second primary key (should fail)
 494 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 495 -- drop first primary key constraint
 496 alter table atacc1 drop constraint atacc_test1 restrict;
 497 -- try adding a primary key on oid (should succeed)
 498 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 499 drop table atacc1;
 500
 501 -- let's do one where the primary key constraint fails when added
 502 create table atacc1 ( test int );
 503 -- insert soon to be failing rows
 504 insert into atacc1 (test) values (2);
 505 insert into atacc1 (test) values (2);
 506 -- add a primary key (fails)
 507 alter table atacc1 add constraint atacc_test1 primary key (test);
 508 insert into atacc1 (test) values (3);
 509 drop table atacc1;
 510
 511 -- let's do another one where the primary key constraint fails when added
 512 create table atacc1 ( test int );
 513 -- insert soon to be failing row
 514 insert into atacc1 (test) values (NULL);
 515 -- add a primary key (fails)
 516 alter table atacc1 add constraint atacc_test1 primary key (test);
 517 insert into atacc1 (test) values (3);
 518 drop table atacc1;
 519
 520 -- let's do one where the primary key constraint fails
 521 -- because the column doesn't exist
 522 create table atacc1 ( test int );
 523 -- add a primary key constraint (fails)
 524 alter table atacc1 add constraint atacc_test1 primary key (test1);
 525 drop table atacc1;
 526
 527 -- adding a new column as primary key to a non-empty table.
 528 -- should fail unless the column has a non-null default value.
 529 create table atacc1 ( test int );
 530 insert into atacc1 (test) values (0);
 531 -- add a primary key column without a default (fails).
 532 alter table atacc1 add column test2 int primary key;
 533 -- now add a primary key column with a default (succeeds).
 534 alter table atacc1 add column test2 int default 0 primary key;
 535 drop table atacc1;
 536
 537 -- something a little more complicated
 538 create table atacc1 ( test int, test2 int);
 539 -- add a primary key constraint
 540 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 541 -- try adding a second primary key - should fail
 542 alter table atacc1 add constraint atacc_test2 primary key (test);
 543 -- insert initial value
 544 insert into atacc1 (test,test2) values (4,4);
 545 -- should fail
 546 insert into atacc1 (test,test2) values (4,4);
 547 insert into atacc1 (test,test2) values (NULL,3);
 548 insert into atacc1 (test,test2) values (3, NULL);
 549 insert into atacc1 (test,test2) values (NULL,NULL);
 550 -- should all succeed
 551 insert into atacc1 (test,test2) values (4,5);
 552 insert into atacc1 (test,test2) values (5,4);
 553 insert into atacc1 (test,test2) values (5,5);
 554 drop table atacc1;
 555
 556 -- lets do some naming tests
 557 create table atacc1 (test int, test2 int, primary key(test));
 558 -- only first should succeed
 559 insert into atacc1 (test2, test) values (3, 3);
 560 insert into atacc1 (test2, test) values (2, 3);
 561 insert into atacc1 (test2, test) values (1, NULL);
 562 drop table atacc1;
 563
 564 -- alter table / alter column [set/drop] not null tests
 565 -- try altering system catalogs, should fail
 566 alter table pg_class alter column relname drop not null;
 567 alter table pg_class alter relname set not null;
 568
 569 -- try altering non-existent table, should fail
 570 alter table non_existent alter column bar set not null;
 571 alter table non_existent alter column bar drop not null;
 572
 573 -- test setting columns to null and not null and vice versa
 574 -- test checking for null values and primary key
 575 create table atacc1 (test int not null) with oids;
 576 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 577 alter table atacc1 alter column test drop not null;
 578 alter table atacc1 drop constraint "atacc1_pkey";
 579 alter table atacc1 alter column test drop not null;
 580 insert into atacc1 values (null);
 581 alter table atacc1 alter test set not null;
 582 delete from atacc1;
 583 alter table atacc1 alter test set not null;
 584
 585 -- try altering a non-existent column, should fail
 586 alter table atacc1 alter bar set not null;
 587 alter table atacc1 alter bar drop not null;
 588
 589 -- try altering the oid column, should fail
 590 alter table atacc1 alter oid set not null;
 591 alter table atacc1 alter oid drop not null;
 592
 593 -- try creating a view and altering that, should fail
 594 create view myview as select * from atacc1;
 595 alter table myview alter column test drop not null;
 596 alter table myview alter column test set not null;
 597 drop view myview;
 598
 599 drop table atacc1;
 600
 601 -- test inheritance
 602 create table parent (a int);
 603 create table child (b varchar(255)) inherits (parent);
 604
 605 alter table parent alter a set not null;
 606 insert into parent values (NULL);
 607 insert into child (a, b) values (NULL, 'foo');
 608 alter table parent alter a drop not null;
 609 insert into parent values (NULL);
 610 insert into child (a, b) values (NULL, 'foo');
 611 alter table only parent alter a set not null;
 612 alter table child alter a set not null;
 613 delete from parent;
 614 alter table only parent alter a set not null;
 615 insert into parent values (NULL);
 616 alter table child alter a set not null;
 617 insert into child (a, b) values (NULL, 'foo');
 618 delete from child;
 619 alter table child alter a set not null;
 620 insert into child (a, b) values (NULL, 'foo');
 621 drop table child;
 622 drop table parent;
 623
 624 -- test setting and removing default values
 625 create table def_test (
 626         c1      int4 default 5,
 627         c2      text default 'initial_default'
 628 );
 629 insert into def_test default values;
 630 alter table def_test alter column c1 drop default;
 631 insert into def_test default values;
 632 alter table def_test alter column c2 drop default;
 633 insert into def_test default values;
 634 alter table def_test alter column c1 set default 10;
 635 alter table def_test alter column c2 set default 'new_default';
 636 insert into def_test default values;
 637 select * from def_test;
 638
 639 -- set defaults to an incorrect type: this should fail
 640 alter table def_test alter column c1 set default 'wrong_datatype';
 641 alter table def_test alter column c2 set default 20;
 642
 643 -- set defaults on a non-existent column: this should fail
 644 alter table def_test alter column c3 set default 30;
 645
 646 -- set defaults on views: we need to create a view, add a rule
 647 -- to allow insertions into it, and then alter the view to add
 648 -- a default
 649 create view def_view_test as select * from def_test;
 650 create rule def_view_test_ins as
 651         on insert to def_view_test
 652         do instead insert into def_test select new.*;
 653 insert into def_view_test default values;
 654 alter table def_view_test alter column c1 set default 45;
 655 insert into def_view_test default values;
 656 alter table def_view_test alter column c2 set default 'view_default';
 657 insert into def_view_test default values;
 658 select * from def_view_test;
 659
 660 drop rule def_view_test_ins on def_view_test;
 661 drop view def_view_test;
 662 drop table def_test;
 663
 664 -- alter table / drop column tests
 665 -- try altering system catalogs, should fail
 666 alter table pg_class drop column relname;
 667
 668 -- try altering non-existent table, should fail
 669 alter table nosuchtable drop column bar;
 670
 671 -- test dropping columns
 672 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
 673 insert into atacc1 values (1, 2, 3, 4);
 674 alter table atacc1 drop a;
 675 alter table atacc1 drop a;
 676
 677 -- SELECTs
 678 select * from atacc1;
 679 select * from atacc1 order by a;
 680 select * from atacc1 order by "........pg.dropped.1........";
 681 select * from atacc1 group by a;
 682 select * from atacc1 group by "........pg.dropped.1........";
 683 select atacc1.* from atacc1;
 684 select a from atacc1;
 685 select atacc1.a from atacc1;
 686 select b,c,d from atacc1;
 687 select a,b,c,d from atacc1;
 688 select * from atacc1 where a = 1;
 689 select "........pg.dropped.1........" from atacc1;
 690 select atacc1."........pg.dropped.1........" from atacc1;
 691 select "........pg.dropped.1........",b,c,d from atacc1;
 692 select * from atacc1 where "........pg.dropped.1........" = 1;
 693
 694 -- UPDATEs
 695 update atacc1 set a = 3;
 696 update atacc1 set b = 2 where a = 3;
 697 update atacc1 set "........pg.dropped.1........" = 3;
 698 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
 699
 700 -- INSERTs
 701 insert into atacc1 values (10, 11, 12, 13);
 702 insert into atacc1 values (default, 11, 12, 13);
 703 insert into atacc1 values (11, 12, 13);
 704 insert into atacc1 (a) values (10);
 705 insert into atacc1 (a) values (default);
 706 insert into atacc1 (a,b,c,d) values (10,11,12,13);
 707 insert into atacc1 (a,b,c,d) values (default,11,12,13);
 708 insert into atacc1 (b,c,d) values (11,12,13);
 709 insert into atacc1 ("........pg.dropped.1........") values (10);
 710 insert into atacc1 ("........pg.dropped.1........") values (default);
 711 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
 712 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
 713
 714 -- DELETEs
 715 delete from atacc1 where a = 3;
 716 delete from atacc1 where "........pg.dropped.1........" = 3;
 717 delete from atacc1;
 718
 719 -- try dropping a non-existent column, should fail
 720 alter table atacc1 drop bar;
 721
 722 -- try dropping the oid column, should succeed
 723 alter table atacc1 drop oid;
 724
 725 -- try dropping the xmin column, should fail
 726 alter table atacc1 drop xmin;
 727
 728 -- try creating a view and altering that, should fail
 729 create view myview as select * from atacc1;
 730 select * from myview;
 731 alter table myview drop d;
 732 drop view myview;
 733
 734 -- test some commands to make sure they fail on the dropped column
 735 analyze atacc1(a);
 736 analyze atacc1("........pg.dropped.1........");
 737 vacuum analyze atacc1(a);
 738 vacuum analyze atacc1("........pg.dropped.1........");
 739 comment on column atacc1.a is 'testing';
 740 comment on column atacc1."........pg.dropped.1........" is 'testing';
 741 alter table atacc1 alter a set storage plain;
 742 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
 743 alter table atacc1 alter a set statistics 0;
 744 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
 745 alter table atacc1 alter a set default 3;
 746 alter table atacc1 alter "........pg.dropped.1........" set default 3;
 747 alter table atacc1 alter a drop default;
 748 alter table atacc1 alter "........pg.dropped.1........" drop default;
 749 alter table atacc1 alter a set not null;
 750 alter table atacc1 alter "........pg.dropped.1........" set not null;
 751 alter table atacc1 alter a drop not null;
 752 alter table atacc1 alter "........pg.dropped.1........" drop not null;
 753 alter table atacc1 rename a to x;
 754 alter table atacc1 rename "........pg.dropped.1........" to x;
 755 alter table atacc1 add primary key(a);
 756 alter table atacc1 add primary key("........pg.dropped.1........");
 757 alter table atacc1 add unique(a);
 758 alter table atacc1 add unique("........pg.dropped.1........");
 759 alter table atacc1 add check (a > 3);
 760 alter table atacc1 add check ("........pg.dropped.1........" > 3);
 761 create table atacc2 (id int4 unique);
 762 alter table atacc1 add foreign key (a) references atacc2(id);
 763 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
 764 alter table atacc2 add foreign key (id) references atacc1(a);
 765 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
 766 drop table atacc2;
 767 create index "testing_idx" on atacc1(a);
 768 create index "testing_idx" on atacc1("........pg.dropped.1........");
 769
 770 -- test create as and select into
 771 insert into atacc1 values (21, 22, 23);
 772 create table test1 as select * from atacc1;
 773 select * from test1;
 774 drop table test1;
 775 select * into test2 from atacc1;
 776 select * from test2;
 777 drop table test2;
 778
 779 -- try dropping all columns
 780 alter table atacc1 drop c;
 781 alter table atacc1 drop d;
 782 alter table atacc1 drop b;
 783 select * from atacc1;
 784
 785 drop table atacc1;
 786
 787 -- test inheritance
 788 create table parent (a int, b int, c int);
 789 insert into parent values (1, 2, 3);
 790 alter table parent drop a;
 791 create table child (d varchar(255)) inherits (parent);
 792 insert into child values (12, 13, 'testing');
 793
 794 select * from parent;
 795 select * from child;
 796 alter table parent drop c;
 797 select * from parent;
 798 select * from child;
 799
 800 drop table child;
 801 drop table parent;
 802
 803 -- test copy in/out
 804 create table test (a int4, b int4, c int4);
 805 insert into test values (1,2,3);
 806 alter table test drop a;
 807 copy test to stdout;
 808 copy test(a) to stdout;
 809 copy test("........pg.dropped.1........") to stdout;
 810 copy test from stdin;
 811 10      11      12
 812 \.
 813 select * from test;
 814 copy test from stdin;
 815 21      22
 816 \.
 817 select * from test;
 818 copy test(a) from stdin;
 819 copy test("........pg.dropped.1........") from stdin;
 820 copy test(b,c) from stdin;
 821 31      32
 822 \.
 823 select * from test;
 824 drop table test;
 825
 826 -- test inheritance
 827
 828 create table dropColumn (a int, b int, e int);
 829 create table dropColumnChild (c int) inherits (dropColumn);
 830 create table dropColumnAnother (d int) inherits (dropColumnChild);
 831
 832 -- these two should fail
 833 alter table dropColumnchild drop column a;
 834 alter table only dropColumnChild drop column b;
 835
 836
 837
 838 -- these three should work
 839 alter table only dropColumn drop column e;
 840 alter table dropColumnChild drop column c;
 841 alter table dropColumn drop column a;
 842
 843 create table renameColumn (a int);
 844 create table renameColumnChild (b int) inherits (renameColumn);
 845 create table renameColumnAnother (c int) inherits (renameColumnChild);
 846
 847 -- these three should fail
 848 alter table renameColumnChild rename column a to d;
 849 alter table only renameColumnChild rename column a to d;
 850 alter table only renameColumn rename column a to d;
 851
 852 -- these should work
 853 alter table renameColumn rename column a to d;
 854 alter table renameColumnChild rename column b to a;
 855
 856 -- this should work
 857 alter table renameColumn add column w int;
 858
 859 -- this should fail
 860 alter table only renameColumn add column x int;
 861
 862
 863 -- Test corner cases in dropping of inherited columns
 864
 865 create table p1 (f1 int, f2 int);
 866 create table c1 (f1 int not null) inherits(p1);
 867
 868 -- should be rejected since c1.f1 is inherited
 869 alter table c1 drop column f1;
 870 -- should work
 871 alter table p1 drop column f1;
 872 -- c1.f1 is still there, but no longer inherited
 873 select f1 from c1;
 874 alter table c1 drop column f1;
 875 select f1 from c1;
 876
 877 drop table p1 cascade;
 878
 879 create table p1 (f1 int, f2 int);
 880 create table c1 () inherits(p1);
 881
 882 -- should be rejected since c1.f1 is inherited
 883 alter table c1 drop column f1;
 884 alter table p1 drop column f1;
 885 -- c1.f1 is dropped now, since there is no local definition for it
 886 select f1 from c1;
 887
 888 drop table p1 cascade;
 889
 890 create table p1 (f1 int, f2 int);
 891 create table c1 () inherits(p1);
 892
 893 -- should be rejected since c1.f1 is inherited
 894 alter table c1 drop column f1;
 895 alter table only p1 drop column f1;
 896 -- c1.f1 is NOT dropped, but must now be considered non-inherited
 897 alter table c1 drop column f1;
 898
 899 drop table p1 cascade;
 900
 901 create table p1 (f1 int, f2 int);
 902 create table c1 (f1 int not null) inherits(p1);
 903
 904 -- should be rejected since c1.f1 is inherited
 905 alter table c1 drop column f1;
 906 alter table only p1 drop column f1;
 907 -- c1.f1 is still there, but no longer inherited
 908 alter table c1 drop column f1;
 909
 910 drop table p1 cascade;
 911
 912 create table p1(id int, name text);
 913 create table p2(id2 int, name text, height int);
 914 create table c1(age int) inherits(p1,p2);
 915 create table gc1() inherits (c1);
 916
 917 select relname, attname, attinhcount, attislocal
 918 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 919 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 920 order by relname, attnum;
 921
 922 -- should work
 923 alter table only p1 drop column name;
 924 -- should work. Now c1.name is local and inhcount is 0.
 925 alter table p2 drop column name;
 926 -- should be rejected since its inherited
 927 alter table gc1 drop column name;
 928 -- should work, and drop gc1.name along
 929 alter table c1 drop column name;
 930 -- should fail: column does not exist
 931 alter table gc1 drop column name;
 932 -- should work and drop the attribute in all tables
 933 alter table p2 drop column height;
 934
 935 -- IF EXISTS test
 936 create table dropColumnExists ();
 937 alter table dropColumnExists drop column non_existing; --fail
 938 alter table dropColumnExists drop column if exists non_existing; --succeed
 939
 940 select relname, attname, attinhcount, attislocal
 941 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
 942 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 943 order by relname, attnum;
 944
 945 drop table p1, p2 cascade;
 946
 947 -- test attinhcount tracking with merged columns
 948
 949 create table depth0();
 950 create table depth1(c text) inherits (depth0);
 951 create table depth2() inherits (depth1);
 952 alter table depth0 add c text;
 953
 954 select attrelid::regclass, attname, attinhcount, attislocal
 955 from pg_attribute
 956 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
 957 order by attrelid::regclass::text, attnum;
 958
 959 --
 960 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
 961 --
 962 create table altstartwith (col integer) with oids;
 963
 964 insert into altstartwith values (1);
 965
 966 select oid > 0, * from altstartwith;
 967
 968 alter table altstartwith set without oids;
 969
 970 select oid > 0, * from altstartwith; -- fails
 971 select * from altstartwith;
 972
 973 alter table altstartwith set with oids;
 974
 975 select oid > 0, * from altstartwith;
 976
 977 drop table altstartwith;
 978
 979 -- Check inheritance cases
 980 create table altwithoid (col integer) with oids;
 981
 982 -- Inherits parents oid column anyway
 983 create table altinhoid () inherits (altwithoid) without oids;
 984
 985 insert into altinhoid values (1);
 986
 987 select oid > 0, * from altwithoid;
 988 select oid > 0, * from altinhoid;
 989
 990 alter table altwithoid set without oids;
 991
 992 select oid > 0, * from altwithoid; -- fails
 993 select oid > 0, * from altinhoid; -- fails
 994 select * from altwithoid;
 995 select * from altinhoid;
 996
 997 alter table altwithoid set with oids;
 998
 999 select oid > 0, * from altwithoid;
1000 select oid > 0, * from altinhoid;
1001
1002 drop table altwithoid cascade;
1003
1004 create table altwithoid (col integer) without oids;
1005
1006 -- child can have local oid column
1007 create table altinhoid () inherits (altwithoid) with oids;
1008
1009 insert into altinhoid values (1);
1010
1011 select oid > 0, * from altwithoid; -- fails
1012 select oid > 0, * from altinhoid;
1013
1014 alter table altwithoid set with oids;
1015
1016 select oid > 0, * from altwithoid;
1017 select oid > 0, * from altinhoid;
1018
1019 -- the child's local definition should remain
1020 alter table altwithoid set without oids;
1021
1022 select oid > 0, * from altwithoid; -- fails
1023 select oid > 0, * from altinhoid;
1024
1025 drop table altwithoid cascade;
1026
1027 -- test renumbering of child-table columns in inherited operations
1028
1029 create table p1 (f1 int);
1030 create table c1 (f2 text, f3 int) inherits (p1);
1031
1032 alter table p1 add column a1 int check (a1 > 0);
1033 alter table p1 add column f2 text;
1034
1035 insert into p1 values (1,2,'abc');
1036 insert into c1 values(11,'xyz',33,0); -- should fail
1037 insert into c1 values(11,'xyz',33,22);
1038
1039 select * from p1;
1040 update p1 set a1 = a1 + 1, f2 = upper(f2);
1041 select * from p1;
1042
1043 drop table p1 cascade;
1044
1045 -- test that operations with a dropped column do not try to reference
1046 -- its datatype
1047
1048 create domain mytype as text;
1049 create temp table foo (f1 text, f2 mytype, f3 text);
1050
1051 insert into foo values('bb','cc','dd');
1052 select * from foo;
1053
1054 drop domain mytype cascade;
1055
1056 select * from foo;
1057 insert into foo values('qq','rr');
1058 select * from foo;
1059 update foo set f3 = 'zz';
1060 select * from foo;
1061 select f3,max(f1) from foo group by f3;
1062
1063 -- Simple tests for alter table column type
1064 alter table foo alter f1 TYPE integer; -- fails
1065 alter table foo alter f1 TYPE varchar(10);
1066
1067 create table anothertab (atcol1 serial8, atcol2 boolean,
1068         constraint anothertab_chk check (atcol1 <= 3));
1069
1070 insert into anothertab (atcol1, atcol2) values (default, true);
1071 insert into anothertab (atcol1, atcol2) values (default, false);
1072 select * from anothertab;
1073
1074 alter table anothertab alter column atcol1 type boolean; -- fails
1075 alter table anothertab alter column atcol1 type integer;
1076
1077 select * from anothertab;
1078
1079 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1080 insert into anothertab (atcol1, atcol2) values (default, null);
1081
1082 select * from anothertab;
1083
1084 alter table anothertab alter column atcol2 type text
1085       using case when atcol2 is true then 'IT WAS TRUE'
1086                  when atcol2 is false then 'IT WAS FALSE'
1087                  else 'IT WAS NULL!' end;
1088
1089 select * from anothertab;
1090 alter table anothertab alter column atcol1 type boolean
1091         using case when atcol1 % 2 = 0 then true else false end; -- fails
1092 alter table anothertab alter column atcol1 drop default;
1093 alter table anothertab alter column atcol1 type boolean
1094         using case when atcol1 % 2 = 0 then true else false end; -- fails
1095 alter table anothertab drop constraint anothertab_chk;
1096 alter table anothertab drop constraint anothertab_chk; -- fails
1097 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1098
1099 alter table anothertab alter column atcol1 type boolean
1100         using case when atcol1 % 2 = 0 then true else false end;
1101
1102 select * from anothertab;
1103
1104 drop table anothertab;
1105
1106 create table another (f1 int, f2 text);
1107
1108 insert into another values(1, 'one');
1109 insert into another values(2, 'two');
1110 insert into another values(3, 'three');
1111
1112 select * from another;
1113
1114 alter table another
1115   alter f1 type text using f2 || ' more',
1116   alter f2 type bigint using f1 * 10;
1117
1118 select * from another;
1119
1120 drop table another;
1121
1122 -- table's row type
1123 create table tab1 (a int, b text);
1124 create table tab2 (x int, y tab1);
1125 alter table tab1 alter column b type varchar; -- fails
1126
1127 -- disallow recursive containment of row types
1128 create temp table recur1 (f1 int);
1129 alter table recur1 add column f2 recur1; -- fails
1130 alter table recur1 add column f2 recur1[]; -- fails
1131 create temp table recur2 (f1 int, f2 recur1);
1132 alter table recur1 add column f2 recur2; -- fails
1133 alter table recur1 add column f2 int;
1134 alter table recur1 alter column f2 type recur2; -- fails
1135
1136 -- SET STORAGE may need to add a TOAST table
1137 create table test_storage (a text);
1138 alter table test_storage alter a set storage plain;
1139 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1140 alter table test_storage alter a set storage extended; -- re-add TOAST table
1141
1142 select reltoastrelid <> 0 as has_toast_table
1143 from pg_class
1144 where oid = 'test_storage'::regclass;
1145
1146 --
1147 -- lock levels
1148 --
1149 drop type lockmodes;
1150 create type lockmodes as enum (
1151  'AccessShareLock'
1152 ,'RowShareLock'
1153 ,'RowExclusiveLock'
1154 ,'ShareUpdateExclusiveLock'
1155 ,'ShareLock'
1156 ,'ShareRowExclusiveLock'
1157 ,'ExclusiveLock'
1158 ,'AccessExclusiveLock'
1159 );
1160
1161 drop view my_locks;
1162 create or replace view my_locks as
1163 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1164 from pg_locks l join pg_class c on l.relation = c.oid
1165 where virtualtransaction = (
1166         select virtualtransaction
1167         from pg_locks
1168         where transactionid = txid_current()::integer)
1169 and locktype = 'relation'
1170 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1171 and c.relname != 'my_locks'
1172 group by c.relname;
1173
1174 create table alterlock (f1 int primary key, f2 text);
1175
1176 -- share update exclusive
1177 begin; alter table alterlock alter column f2 set statistics 150;
1178 select * from my_locks order by 1;
1179 rollback;
1180
1181 begin; alter table alterlock cluster on alterlock_pkey;
1182 select * from my_locks order by 1;
1183 commit;
1184
1185 begin; alter table alterlock set without cluster;
1186 select * from my_locks order by 1;
1187 commit;
1188
1189 begin; alter table alterlock set (fillfactor = 100);
1190 select * from my_locks order by 1;
1191 commit;
1192
1193 begin; alter table alterlock reset (fillfactor);
1194 select * from my_locks order by 1;
1195 commit;
1196
1197 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1198 select * from my_locks order by 1;
1199 commit;
1200
1201 begin; alter table alterlock set (autovacuum_enabled = off);
1202 select * from my_locks order by 1;
1203 commit;
1204
1205 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1206 select * from my_locks order by 1;
1207 rollback;
1208
1209 begin; alter table alterlock alter column f2 set storage extended;
1210 select * from my_locks order by 1;
1211 rollback;
1212
1213 -- share row exclusive
1214 begin; alter table alterlock alter column f2 set default 'x';
1215 select * from my_locks order by 1;
1216 rollback;
1217
1218 -- cleanup
1219 drop table alterlock;
1220 drop view my_locks;
1221 drop type lockmodes;
1222
1223 --
1224 -- alter function
1225 --
1226 create function test_strict(text) returns text as
1227     'select coalesce($1, ''got passed a null'');'
1228     language sql returns null on null input;
1229 select test_strict(NULL);
1230 alter function test_strict(text) called on null input;
1231 select test_strict(NULL);
1232
1233 create function non_strict(text) returns text as
1234     'select coalesce($1, ''got passed a null'');'
1235     language sql called on null input;
1236 select non_strict(NULL);
1237 alter function non_strict(text) returns null on null input;
1238 select non_strict(NULL);
1239
1240 --
1241 -- alter object set schema
1242 --
1243
1244 create schema alter1;
1245 create schema alter2;
1246
1247 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1248
1249 create view alter1.v1 as select * from alter1.t1;
1250
1251 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1252
1253 create domain alter1.posint integer check (value > 0);
1254
1255 create type alter1.ctype as (f1 int, f2 text);
1256
1257 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1258 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1259
1260 create operator alter1.=(procedure = alter1.same, leftarg  = alter1.ctype, rightarg = alter1.ctype);
1261
1262 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1263   operator 1 alter1.=(alter1.ctype, alter1.ctype);
1264
1265 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1266
1267 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1268 create text search configuration alter1.cfg(parser = alter1.prs);
1269 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1270 create text search dictionary alter1.dict(template = alter1.tmpl);
1271
1272 insert into alter1.t1(f2) values(11);
1273 insert into alter1.t1(f2) values(12);
1274
1275 alter table alter1.t1 set schema alter2;
1276 alter table alter1.v1 set schema alter2;
1277 alter function alter1.plus1(int) set schema alter2;
1278 alter domain alter1.posint set schema alter2;
1279 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1280 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1281 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1282 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1283 alter type alter1.ctype set schema alter2;
1284 alter conversion alter1.ascii_to_utf8 set schema alter2;
1285 alter text search parser alter1.prs set schema alter2;
1286 alter text search configuration alter1.cfg set schema alter2;
1287 alter text search template alter1.tmpl set schema alter2;
1288 alter text search dictionary alter1.dict set schema alter2;
1289
1290 -- this should succeed because nothing is left in alter1
1291 drop schema alter1;
1292
1293 insert into alter2.t1(f2) values(13);
1294 insert into alter2.t1(f2) values(14);
1295
1296 select * from alter2.t1;
1297
1298 select * from alter2.v1;
1299
1300 select alter2.plus1(41);
1301
1302 -- clean up
1303 drop schema alter2 cascade;
1304
1305 --
1306 -- composite types
1307 --
1308
1309 CREATE TYPE test_type AS (a int);
1310 \d test_type
1311
1312 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1313
1314 ALTER TYPE test_type ADD ATTRIBUTE b text;
1315 \d test_type
1316
1317 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1318
1319 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1320 \d test_type
1321
1322 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1323 \d test_type
1324
1325 ALTER TYPE test_type DROP ATTRIBUTE b;
1326 \d test_type
1327
1328 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1329
1330 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1331
1332 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1333 \d test_type
1334
1335 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1336 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1337 \d test_type
1338
1339 DROP TYPE test_type;
1340
1341 CREATE TYPE test_type1 AS (a int, b text);
1342 CREATE TABLE test_tbl1 (x int, y test_type1);
1343 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1344
1345 CREATE TYPE test_type2 AS (a int, b text);
1346 CREATE TABLE test_tbl2 OF test_type2;
1347 \d test_type2
1348 \d test_tbl2
1349
1350 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1351 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1352 \d test_type2
1353 \d test_tbl2
1354
1355 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1356 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1357 \d test_type2
1358 \d test_tbl2
1359
1360 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1361 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
1362 \d test_type2
1363 \d test_tbl2
1364
1365 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
1366 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
1367 \d test_type2
1368 \d test_tbl2
1369
1370 CREATE TYPE test_type_empty AS ();
1371 DROP TYPE test_type_empty;
1372
1373 --
1374 -- typed tables: OF / NOT OF
1375 --
1376
1377 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
1378 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
1379 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2));      -- OK
1380 CREATE TABLE tt1 (x int, y bigint);                                     -- wrong base type
1381 CREATE TABLE tt2 (x int, y numeric(9,2));                       -- wrong typmod
1382 CREATE TABLE tt3 (y numeric(8,2), x int);                       -- wrong column order
1383 CREATE TABLE tt4 (x int);                                                       -- too few columns
1384 CREATE TABLE tt5 (x int, y numeric(8,2), z int);        -- too few columns
1385 CREATE TABLE tt6 () INHERITS (tt0);                                     -- can't have a parent
1386 CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
1387 ALTER TABLE tt7 DROP q;                                                         -- OK
1388
1389 ALTER TABLE tt0 OF tt_t0;
1390 ALTER TABLE tt1 OF tt_t0;
1391 ALTER TABLE tt2 OF tt_t0;
1392 ALTER TABLE tt3 OF tt_t0;
1393 ALTER TABLE tt4 OF tt_t0;
1394 ALTER TABLE tt5 OF tt_t0;
1395 ALTER TABLE tt6 OF tt_t0;
1396 ALTER TABLE tt7 OF tt_t0;
1397
1398 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
1399 ALTER TABLE tt7 OF tt_t1;                       -- reassign an already-typed table
1400 ALTER TABLE tt7 NOT OF;
1401 \d tt7