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 datetime;
  50
  51 ALTER TABLE tmp ADD COLUMN w timespan;
  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 datetime;
 119
 120 ALTER TABLE tmp ADD COLUMN w timespan;
 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
 182 -- FOREIGN KEY CONSTRAINT adding TEST
 183
 184 CREATE TABLE tmp2 (a int primary key);
 185
 186 CREATE TABLE tmp3 (a int, b int);
 187
 188 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 189
 190 CREATE TABLE tmp5 (a int, b int);
 191
 192 -- Insert rows into tmp2 (pktable)
 193 INSERT INTO tmp2 values (1);
 194 INSERT INTO tmp2 values (2);
 195 INSERT INTO tmp2 values (3);
 196 INSERT INTO tmp2 values (4);
 197
 198 -- Insert rows into tmp3
 199 INSERT INTO tmp3 values (1,10);
 200 INSERT INTO tmp3 values (1,20);
 201 INSERT INTO tmp3 values (5,50);
 202
 203 -- Try (and fail) to add constraint due to invalid source columns
 204 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
 205
 206 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
 207 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
 208
 209 -- Try (and fail) to add constraint due to invalid data
 210 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 211
 212 -- Delete failing row
 213 DELETE FROM tmp3 where a=5;
 214
 215 -- Try (and succeed)
 216 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 217
 218 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 219 -- tmp4 is a,b
 220
 221 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 222
 223 DROP TABLE tmp5;
 224
 225 DROP TABLE tmp4;
 226
 227 DROP TABLE tmp3;
 228
 229 DROP TABLE tmp2;
 230
 231 -- Foreign key adding test with mixed types
 232
 233 -- Note: these tables are TEMP to avoid name conflicts when this test
 234 -- is run in parallel with foreign_key.sql.
 235
 236 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 237 CREATE TEMP TABLE FKTABLE (ftest1 text);
 238 -- This next should fail, because text=int does not exist
 239 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 240 -- This should also fail for the same reason, but here we
 241 -- give the column name
 242 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 243 -- This should succeed, even though they are different types
 244 -- because varchar=int does exist
 245 DROP TABLE FKTABLE;
 246 CREATE TEMP TABLE FKTABLE (ftest1 varchar);
 247 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 248 -- As should this
 249 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 250 DROP TABLE pktable;
 251 DROP TABLE fktable;
 252
 253 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 text,
 254                            PRIMARY KEY(ptest1, ptest2));
 255 -- This should fail, because we just chose really odd types
 256 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime);
 257 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 258 -- Again, so should this...
 259 DROP TABLE FKTABLE;
 260 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime);
 261 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 262      references pktable(ptest1, ptest2);
 263 -- This fails because we mixed up the column ordering
 264 DROP TABLE FKTABLE;
 265 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 text);
 266 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 267      references pktable(ptest2, ptest1);
 268 -- As does this...
 269 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 270      references pktable(ptest1, ptest2);
 271
 272 -- temp tables should go away by themselves, need not drop them.
 273
 274 -- test check constraint adding
 275
 276 create table atacc1 ( test int );
 277 -- add a check constraint
 278 alter table atacc1 add constraint atacc_test1 check (test>3);
 279 -- should fail
 280 insert into atacc1 (test) values (2);
 281 -- should succeed
 282 insert into atacc1 (test) values (4);
 283 drop table atacc1;
 284
 285 -- let's do one where the check fails when added
 286 create table atacc1 ( test int );
 287 -- insert a soon to be failing row
 288 insert into atacc1 (test) values (2);
 289 -- add a check constraint (fails)
 290 alter table atacc1 add constraint atacc_test1 check (test>3);
 291 insert into atacc1 (test) values (4);
 292 drop table atacc1;
 293
 294 -- let's do one where the check fails because the column doesn't exist
 295 create table atacc1 ( test int );
 296 -- add a check constraint (fails)
 297 alter table atacc1 add constraint atacc_test1 check (test1>3);
 298 drop table atacc1;
 299
 300 -- something a little more complicated
 301 create table atacc1 ( test int, test2 int, test3 int);
 302 -- add a check constraint (fails)
 303 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 304 -- should fail
 305 insert into atacc1 (test,test2,test3) values (4,4,2);
 306 -- should succeed
 307 insert into atacc1 (test,test2,test3) values (4,4,5);
 308 drop table atacc1;
 309
 310 -- lets do some naming tests
 311 create table atacc1 (test int check (test>3), test2 int);
 312 alter table atacc1 add check (test2>test);
 313 -- should fail for $2
 314 insert into atacc1 (test2, test) values (3, 4);
 315 drop table atacc1;
 316
 317 -- inheritance related tests
 318 create table atacc1 (test int);
 319 create table atacc2 (test2 int);
 320 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 321 alter table atacc2 add constraint foo check (test2>0);
 322 -- fail and then succeed on atacc2
 323 insert into atacc2 (test2) values (-3);
 324 insert into atacc2 (test2) values (3);
 325 -- fail and then succeed on atacc3
 326 insert into atacc3 (test2) values (-3);
 327 insert into atacc3 (test2) values (3);
 328 drop table atacc3;
 329 drop table atacc2;
 330 drop table atacc1;
 331
 332 -- let's try only to add only to the parent
 333
 334 create table atacc1 (test int);
 335 create table atacc2 (test2 int);
 336 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 337 alter table only atacc2 add constraint foo check (test2>0);
 338 -- fail and then succeed on atacc2
 339 insert into atacc2 (test2) values (-3);
 340 insert into atacc2 (test2) values (3);
 341 -- both succeed on atacc3
 342 insert into atacc3 (test2) values (-3);
 343 insert into atacc3 (test2) values (3);
 344 drop table atacc3;
 345 drop table atacc2;
 346 drop table atacc1;
 347
 348 -- test unique constraint adding
 349
 350 create table atacc1 ( test int );
 351 -- add a unique constraint
 352 alter table atacc1 add constraint atacc_test1 unique (test);
 353 -- insert first value
 354 insert into atacc1 (test) values (2);
 355 -- should fail
 356 insert into atacc1 (test) values (2);
 357 -- should succeed
 358 insert into atacc1 (test) values (4);
 359 -- try adding a unique oid constraint
 360 alter table atacc1 add constraint atacc_oid1 unique(oid);
 361 drop table atacc1;
 362
 363 -- let's do one where the unique constraint fails when added
 364 create table atacc1 ( test int );
 365 -- insert soon to be failing rows
 366 insert into atacc1 (test) values (2);
 367 insert into atacc1 (test) values (2);
 368 -- add a unique constraint (fails)
 369 alter table atacc1 add constraint atacc_test1 unique (test);
 370 insert into atacc1 (test) values (3);
 371 drop table atacc1;
 372
 373 -- let's do one where the unique constraint fails
 374 -- because the column doesn't exist
 375 create table atacc1 ( test int );
 376 -- add a unique constraint (fails)
 377 alter table atacc1 add constraint atacc_test1 unique (test1);
 378 drop table atacc1;
 379
 380 -- something a little more complicated
 381 create table atacc1 ( test int, test2 int);
 382 -- add a unique constraint
 383 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 384 -- insert initial value
 385 insert into atacc1 (test,test2) values (4,4);
 386 -- should fail
 387 insert into atacc1 (test,test2) values (4,4);
 388 -- should all succeed
 389 insert into atacc1 (test,test2) values (4,5);
 390 insert into atacc1 (test,test2) values (5,4);
 391 insert into atacc1 (test,test2) values (5,5);
 392 drop table atacc1;
 393
 394 -- lets do some naming tests
 395 create table atacc1 (test int, test2 int, unique(test));
 396 alter table atacc1 add unique (test2);
 397 -- should fail for @@ second one @@
 398 insert into atacc1 (test2, test) values (3, 3);
 399 insert into atacc1 (test2, test) values (2, 3);
 400 drop table atacc1;
 401
 402 -- test primary key constraint adding
 403
 404 create table atacc1 ( test int );
 405 -- add a primary key constraint
 406 alter table atacc1 add constraint atacc_test1 primary key (test);
 407 -- insert first value
 408 insert into atacc1 (test) values (2);
 409 -- should fail
 410 insert into atacc1 (test) values (2);
 411 -- should succeed
 412 insert into atacc1 (test) values (4);
 413 -- inserting NULL should fail
 414 insert into atacc1 (test) values(NULL);
 415 -- try adding a primary key oid constraint
 416 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 417 drop table atacc1;
 418
 419 -- let's do one where the primary key constraint fails when added
 420 create table atacc1 ( test int );
 421 -- insert soon to be failing rows
 422 insert into atacc1 (test) values (2);
 423 insert into atacc1 (test) values (2);
 424 -- add a primary key (fails)
 425 alter table atacc1 add constraint atacc_test1 primary key (test);
 426 insert into atacc1 (test) values (3);
 427 drop table atacc1;
 428
 429 -- let's do another one where the primary key constraint fails when added
 430 create table atacc1 ( test int );
 431 -- insert soon to be failing row
 432 insert into atacc1 (test) values (NULL);
 433 -- add a primary key (fails)
 434 alter table atacc1 add constraint atacc_test1 primary key (test);
 435 insert into atacc1 (test) values (3);
 436 drop table atacc1;
 437
 438 -- let's do one where the primary key constraint fails
 439 -- because the column doesn't exist
 440 create table atacc1 ( test int );
 441 -- add a primary key constraint (fails)
 442 alter table atacc1 add constraint atacc_test1 primary key (test1);
 443 drop table atacc1;
 444
 445 -- something a little more complicated
 446 create table atacc1 ( test int, test2 int);
 447 -- add a primary key constraint
 448 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 449 -- try adding a second primary key - should fail
 450 alter table atacc1 add constraint atacc_test2 primary key (test);
 451 -- insert initial value
 452 insert into atacc1 (test,test2) values (4,4);
 453 -- should fail
 454 insert into atacc1 (test,test2) values (4,4);
 455 insert into atacc1 (test,test2) values (NULL,3);
 456 insert into atacc1 (test,test2) values (3, NULL);
 457 insert into atacc1 (test,test2) values (NULL,NULL);
 458 -- should all succeed
 459 insert into atacc1 (test,test2) values (4,5);
 460 insert into atacc1 (test,test2) values (5,4);
 461 insert into atacc1 (test,test2) values (5,5);
 462 drop table atacc1;
 463
 464 -- lets do some naming tests
 465 create table atacc1 (test int, test2 int, primary key(test));
 466 -- only first should succeed
 467 insert into atacc1 (test2, test) values (3, 3);
 468 insert into atacc1 (test2, test) values (2, 3);
 469 insert into atacc1 (test2, test) values (1, NULL);
 470 drop table atacc1;
 471
 472 -- alter table / alter column [set/drop] not null tests
 473 -- try altering system catalogs, should fail
 474 alter table pg_class alter column relname drop not null;
 475 alter table pg_class alter relname set not null;
 476
 477 -- try altering non-existent table, should fail
 478 alter table foo alter column bar set not null;
 479 alter table foo alter column bar drop not null;
 480
 481 -- test setting columns to null and not null and vice versa
 482 -- test checking for null values and primary key
 483 create table atacc1 (test int not null);
 484 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 485 alter table atacc1 alter column test drop not null;
 486 drop index atacc1_pkey;
 487 alter table atacc1 alter column test drop not null;
 488 insert into atacc1 values (null);
 489 alter table atacc1 alter test set not null;
 490 delete from atacc1;
 491 alter table atacc1 alter test set not null;
 492
 493 -- try altering a non-existent column, should fail
 494 alter table atacc1 alter bar set not null;
 495 alter table atacc1 alter bar drop not null;
 496
 497 -- try altering the oid column, should fail
 498 alter table atacc1 alter oid set not null;
 499 alter table atacc1 alter oid drop not null;
 500
 501 -- try creating a view and altering that, should fail
 502 create view myview as select * from atacc1;
 503 alter table myview alter column test drop not null;
 504 alter table myview alter column test set not null;
 505 drop view myview;
 506
 507 drop table atacc1;
 508
 509 -- test inheritance
 510 create table parent (a int);
 511 create table child (b varchar(255)) inherits (parent);
 512
 513 alter table parent alter a set not null;
 514 insert into parent values (NULL);
 515 insert into child (a, b) values (NULL, 'foo');
 516 alter table parent alter a drop not null;
 517 insert into parent values (NULL);
 518 insert into child (a, b) values (NULL, 'foo');
 519 alter table only parent alter a set not null;
 520 alter table child alter a set not null;
 521 delete from parent;
 522 alter table only parent alter a set not null;
 523 insert into parent values (NULL);
 524 alter table child alter a set not null;
 525 insert into child (a, b) values (NULL, 'foo');
 526 delete from child;
 527 alter table child alter a set not null;
 528 insert into child (a, b) values (NULL, 'foo');
 529 drop table child;
 530 drop table parent;
 531