5 CREATE TABLE tmp (initial int4);
6 ALTER TABLE tmp ADD COLUMN a int4;
7 ALTER TABLE tmp ADD COLUMN b name;
8 ALTER TABLE tmp ADD COLUMN c text;
9 ALTER TABLE tmp ADD COLUMN d float8;
10 ALTER TABLE tmp ADD COLUMN e float4;
11 ALTER TABLE tmp ADD COLUMN f int2;
12 ALTER TABLE tmp ADD COLUMN g polygon;
13 ALTER TABLE tmp ADD COLUMN h abstime;
14 ALTER TABLE tmp ADD COLUMN i char;
15 ALTER TABLE tmp ADD COLUMN j abstime[];
16 ALTER TABLE tmp ADD COLUMN k int4;
17 ALTER TABLE tmp ADD COLUMN l tid;
18 ALTER TABLE tmp ADD COLUMN m xid;
19 ALTER TABLE tmp ADD COLUMN n oidvector;
20 --ALTER TABLE tmp ADD COLUMN o lock;
21 ALTER TABLE tmp ADD COLUMN p smgr;
22 ALTER TABLE tmp ADD COLUMN q point;
23 ALTER TABLE tmp ADD COLUMN r lseg;
24 ALTER TABLE tmp ADD COLUMN s path;
25 ALTER TABLE tmp ADD COLUMN t box;
26 ALTER TABLE tmp ADD COLUMN u tinterval;
27 ALTER TABLE tmp ADD COLUMN v timestamp;
28 ALTER TABLE tmp ADD COLUMN w interval;
29 ALTER TABLE tmp ADD COLUMN x float8[];
30 ALTER TABLE tmp ADD COLUMN y float4[];
31 ALTER TABLE tmp ADD COLUMN z int2[];
32 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
34 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
35 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
36 314159, '(1,1)', '512',
37 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
38 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
39 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
41 initial | a | b | c | d | e | f | g | h | i | j | k | l | m | n | p | q | r | s | t | u | v | w | x | y | z
42 ---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
43 | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
47 -- the wolf bug - schema mods caused inconsistent row descriptors
51 ALTER TABLE tmp ADD COLUMN a int4;
52 ALTER TABLE tmp ADD COLUMN b name;
53 ALTER TABLE tmp ADD COLUMN c text;
54 ALTER TABLE tmp ADD COLUMN d float8;
55 ALTER TABLE tmp ADD COLUMN e float4;
56 ALTER TABLE tmp ADD COLUMN f int2;
57 ALTER TABLE tmp ADD COLUMN g polygon;
58 ALTER TABLE tmp ADD COLUMN h abstime;
59 ALTER TABLE tmp ADD COLUMN i char;
60 ALTER TABLE tmp ADD COLUMN j abstime[];
61 ALTER TABLE tmp ADD COLUMN k int4;
62 ALTER TABLE tmp ADD COLUMN l tid;
63 ALTER TABLE tmp ADD COLUMN m xid;
64 ALTER TABLE tmp ADD COLUMN n oidvector;
65 --ALTER TABLE tmp ADD COLUMN o lock;
66 ALTER TABLE tmp ADD COLUMN p smgr;
67 ALTER TABLE tmp ADD COLUMN q point;
68 ALTER TABLE tmp ADD COLUMN r lseg;
69 ALTER TABLE tmp ADD COLUMN s path;
70 ALTER TABLE tmp ADD COLUMN t box;
71 ALTER TABLE tmp ADD COLUMN u tinterval;
72 ALTER TABLE tmp ADD COLUMN v timestamp;
73 ALTER TABLE tmp ADD COLUMN w interval;
74 ALTER TABLE tmp ADD COLUMN x float8[];
75 ALTER TABLE tmp ADD COLUMN y float4[];
76 ALTER TABLE tmp ADD COLUMN z int2[];
77 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
79 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
80 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
81 314159, '(1,1)', '512',
82 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
83 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
84 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
86 initial | a | b | c | d | e | f | g | h | i | j | k | l | m | n | p | q | r | s | t | u | v | w | x | y | z
87 ---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
88 | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
94 -- should preserve indices, which we can check by seeing if a SELECT
95 -- chooses an indexscan; however, in the absence of vacuum statistics
96 -- it might not. Therefore, vacuum first.
99 ALTER TABLE tenk1 RENAME TO ten_k;
101 SELECT unique1 FROM ten_k WHERE unique1 < 20;
127 SELECT unique2 FROM ten_k WHERE unique2 < 20;
152 -- 100 values, sorted
153 SELECT hundred FROM ten_k WHERE hundred = 50;
258 ALTER TABLE ten_k RENAME TO tenk1;
260 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
270 -- ALTER TABLE ... RENAME on non-table relations
271 -- renaming indexes (FIXME: this should probably test the index's functionality)
272 ALTER TABLE onek_unique1 RENAME TO tmp_onek_unique1;
273 ALTER TABLE tmp_onek_unique1 RENAME TO onek_unique1;
275 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
276 ALTER TABLE tmp_view RENAME TO tmp_view_new;
278 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
288 DROP VIEW tmp_view_new;
289 -- toast-like relation name
290 alter table stud_emp rename to pg_toast_stud_emp;
291 alter table pg_toast_stud_emp rename to stud_emp;
292 -- FOREIGN KEY CONSTRAINT adding TEST
293 CREATE TABLE tmp2 (a int primary key);
294 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2"
295 CREATE TABLE tmp3 (a int, b int);
296 CREATE TABLE tmp4 (a int, b int, unique(a,b));
297 NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for table "tmp4"
298 CREATE TABLE tmp5 (a int, b int);
299 -- Insert rows into tmp2 (pktable)
300 INSERT INTO tmp2 values (1);
301 INSERT INTO tmp2 values (2);
302 INSERT INTO tmp2 values (3);
303 INSERT INTO tmp2 values (4);
304 -- Insert rows into tmp3
305 INSERT INTO tmp3 values (1,10);
306 INSERT INTO tmp3 values (1,20);
307 INSERT INTO tmp3 values (5,50);
308 -- Try (and fail) to add constraint due to invalid source columns
309 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
310 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
311 ERROR: column "c" referenced in foreign key constraint does not exist
312 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
313 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
314 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
315 ERROR: column "b" referenced in foreign key constraint does not exist
316 -- Try (and fail) to add constraint due to invalid data
317 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
318 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
319 ERROR: insert or update on "tmp3" violates foreign key constraint "tmpconstr"
320 DETAIL: Key (a)=(5) is not present in "tmp2".
321 -- Delete failing row
322 DELETE FROM tmp3 where a=5;
324 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
325 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
326 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
328 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
329 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
330 ERROR: there is no UNIQUE constraint matching given keys for referenced table "tmp4"
335 -- Foreign key adding test with mixed types
336 -- Note: these tables are TEMP to avoid name conflicts when this test
337 -- is run in parallel with foreign_key.sql.
338 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
339 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
340 CREATE TEMP TABLE FKTABLE (ftest1 inet);
341 -- This next should fail, because inet=int does not exist
342 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
343 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
344 ERROR: operator does not exist: inet = integer
345 HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts.
346 -- This should also fail for the same reason, but here we
347 -- give the column name
348 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
349 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
350 ERROR: operator does not exist: inet = integer
351 HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts.
352 -- This should succeed, even though they are different types
353 -- because varchar=int does exist
355 CREATE TEMP TABLE FKTABLE (ftest1 varchar);
356 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
357 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
359 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
360 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
361 DROP TABLE pktable cascade;
362 NOTICE: drop cascades to constraint $2 on table fktable
363 NOTICE: drop cascades to constraint $1 on table fktable
365 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
366 PRIMARY KEY(ptest1, ptest2));
367 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
368 -- This should fail, because we just chose really odd types
369 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
370 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
371 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
372 ERROR: operator does not exist: cidr = integer
373 HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts.
375 -- Again, so should this...
376 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
377 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
378 references pktable(ptest1, ptest2);
379 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
380 ERROR: operator does not exist: cidr = integer
381 HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts.
383 -- This fails because we mixed up the column ordering
384 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
385 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
386 references pktable(ptest2, ptest1);
387 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
388 ERROR: operator does not exist: integer = inet
389 HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts.
391 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
392 references pktable(ptest1, ptest2);
393 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
394 ERROR: operator does not exist: inet = integer
395 HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts.
396 -- temp tables should go away by themselves, need not drop them.
397 -- test check constraint adding
398 create table atacc1 ( test int );
399 -- add a check constraint
400 alter table atacc1 add constraint atacc_test1 check (test>3);
402 insert into atacc1 (test) values (2);
403 ERROR: new row for relation "atacc1" violates CHECK constraint "atacc_test1"
405 insert into atacc1 (test) values (4);
407 -- let's do one where the check fails when added
408 create table atacc1 ( test int );
409 -- insert a soon to be failing row
410 insert into atacc1 (test) values (2);
411 -- add a check constraint (fails)
412 alter table atacc1 add constraint atacc_test1 check (test>3);
413 ERROR: CHECK constraint "atacc_test1" is violated at some row(s)
414 insert into atacc1 (test) values (4);
416 -- let's do one where the check fails because the column doesn't exist
417 create table atacc1 ( test int );
418 -- add a check constraint (fails)
419 alter table atacc1 add constraint atacc_test1 check (test1>3);
420 ERROR: attribute "test1" not found
422 -- something a little more complicated
423 create table atacc1 ( test int, test2 int, test3 int);
424 -- add a check constraint (fails)
425 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
427 insert into atacc1 (test,test2,test3) values (4,4,2);
428 ERROR: new row for relation "atacc1" violates CHECK constraint "atacc_test1"
430 insert into atacc1 (test,test2,test3) values (4,4,5);
432 -- lets do some naming tests
433 create table atacc1 (test int check (test>3), test2 int);
434 alter table atacc1 add check (test2>test);
435 -- should fail for $2
436 insert into atacc1 (test2, test) values (3, 4);
437 ERROR: new row for relation "atacc1" violates CHECK constraint "$1"
439 -- inheritance related tests
440 create table atacc1 (test int);
441 create table atacc2 (test2 int);
442 create table atacc3 (test3 int) inherits (atacc1, atacc2);
443 alter table atacc2 add constraint foo check (test2>0);
444 -- fail and then succeed on atacc2
445 insert into atacc2 (test2) values (-3);
446 ERROR: new row for relation "atacc2" violates CHECK constraint "foo"
447 insert into atacc2 (test2) values (3);
448 -- fail and then succeed on atacc3
449 insert into atacc3 (test2) values (-3);
450 ERROR: new row for relation "atacc3" violates CHECK constraint "foo"
451 insert into atacc3 (test2) values (3);
455 -- let's try only to add only to the parent
456 create table atacc1 (test int);
457 create table atacc2 (test2 int);
458 create table atacc3 (test3 int) inherits (atacc1, atacc2);
459 alter table only atacc2 add constraint foo check (test2>0);
460 -- fail and then succeed on atacc2
461 insert into atacc2 (test2) values (-3);
462 ERROR: new row for relation "atacc2" violates CHECK constraint "foo"
463 insert into atacc2 (test2) values (3);
464 -- both succeed on atacc3
465 insert into atacc3 (test2) values (-3);
466 insert into atacc3 (test2) values (3);
470 -- test unique constraint adding
471 create table atacc1 ( test int );
472 -- add a unique constraint
473 alter table atacc1 add constraint atacc_test1 unique (test);
474 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
475 -- insert first value
476 insert into atacc1 (test) values (2);
478 insert into atacc1 (test) values (2);
479 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
481 insert into atacc1 (test) values (4);
482 -- try adding a unique oid constraint
483 alter table atacc1 add constraint atacc_oid1 unique(oid);
484 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"
486 -- let's do one where the unique constraint fails when added
487 create table atacc1 ( test int );
488 -- insert soon to be failing rows
489 insert into atacc1 (test) values (2);
490 insert into atacc1 (test) values (2);
491 -- add a unique constraint (fails)
492 alter table atacc1 add constraint atacc_test1 unique (test);
493 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
494 ERROR: Cannot create unique index. Table contains non-unique values
495 insert into atacc1 (test) values (3);
497 -- let's do one where the unique constraint fails
498 -- because the column doesn't exist
499 create table atacc1 ( test int );
500 -- add a unique constraint (fails)
501 alter table atacc1 add constraint atacc_test1 unique (test1);
502 ERROR: column "test1" named in key does not exist
504 -- something a little more complicated
505 create table atacc1 ( test int, test2 int);
506 -- add a unique constraint
507 alter table atacc1 add constraint atacc_test1 unique (test, test2);
508 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
509 -- insert initial value
510 insert into atacc1 (test,test2) values (4,4);
512 insert into atacc1 (test,test2) values (4,4);
513 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
514 -- should all succeed
515 insert into atacc1 (test,test2) values (4,5);
516 insert into atacc1 (test,test2) values (5,4);
517 insert into atacc1 (test,test2) values (5,5);
519 -- lets do some naming tests
520 create table atacc1 (test int, test2 int, unique(test));
521 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
522 alter table atacc1 add unique (test2);
523 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
524 -- should fail for @@ second one @@
525 insert into atacc1 (test2, test) values (3, 3);
526 insert into atacc1 (test2, test) values (2, 3);
527 ERROR: duplicate key violates UNIQUE constraint "atacc1_test_key"
529 -- test primary key constraint adding
530 create table atacc1 ( test int );
531 -- add a primary key constraint
532 alter table atacc1 add constraint atacc_test1 primary key (test);
533 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
534 -- insert first value
535 insert into atacc1 (test) values (2);
537 insert into atacc1 (test) values (2);
538 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
540 insert into atacc1 (test) values (4);
541 -- inserting NULL should fail
542 insert into atacc1 (test) values(NULL);
543 ERROR: null value for attribute "test" violates NOT NULL constraint
544 -- try adding a second primary key (should fail)
545 alter table atacc1 add constraint atacc_oid1 primary key(oid);
546 ERROR: multiple primary keys for table "atacc1" are not allowed
547 -- drop first primary key constraint
548 alter table atacc1 drop constraint atacc_test1 restrict;
549 -- try adding a primary key on oid (should succeed)
550 alter table atacc1 add constraint atacc_oid1 primary key(oid);
551 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
553 -- let's do one where the primary key constraint fails when added
554 create table atacc1 ( test int );
555 -- insert soon to be failing rows
556 insert into atacc1 (test) values (2);
557 insert into atacc1 (test) values (2);
558 -- add a primary key (fails)
559 alter table atacc1 add constraint atacc_test1 primary key (test);
560 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
561 ERROR: Cannot create unique index. Table contains non-unique values
562 insert into atacc1 (test) values (3);
564 -- let's do another one where the primary key constraint fails when added
565 create table atacc1 ( test int );
566 -- insert soon to be failing row
567 insert into atacc1 (test) values (NULL);
568 -- add a primary key (fails)
569 alter table atacc1 add constraint atacc_test1 primary key (test);
570 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
571 ERROR: attribute "test" contains NULL values
572 insert into atacc1 (test) values (3);
574 -- let's do one where the primary key constraint fails
575 -- because the column doesn't exist
576 create table atacc1 ( test int );
577 -- add a primary key constraint (fails)
578 alter table atacc1 add constraint atacc_test1 primary key (test1);
579 ERROR: column "test1" named in key does not exist
581 -- something a little more complicated
582 create table atacc1 ( test int, test2 int);
583 -- add a primary key constraint
584 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
585 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
586 -- try adding a second primary key - should fail
587 alter table atacc1 add constraint atacc_test2 primary key (test);
588 ERROR: multiple primary keys for table "atacc1" are not allowed
589 -- insert initial value
590 insert into atacc1 (test,test2) values (4,4);
592 insert into atacc1 (test,test2) values (4,4);
593 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
594 insert into atacc1 (test,test2) values (NULL,3);
595 ERROR: null value for attribute "test" violates NOT NULL constraint
596 insert into atacc1 (test,test2) values (3, NULL);
597 ERROR: null value for attribute "test2" violates NOT NULL constraint
598 insert into atacc1 (test,test2) values (NULL,NULL);
599 ERROR: null value for attribute "test" violates NOT NULL constraint
600 -- should all succeed
601 insert into atacc1 (test,test2) values (4,5);
602 insert into atacc1 (test,test2) values (5,4);
603 insert into atacc1 (test,test2) values (5,5);
605 -- lets do some naming tests
606 create table atacc1 (test int, test2 int, primary key(test));
607 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
608 -- only first should succeed
609 insert into atacc1 (test2, test) values (3, 3);
610 insert into atacc1 (test2, test) values (2, 3);
611 ERROR: duplicate key violates UNIQUE constraint "atacc1_pkey"
612 insert into atacc1 (test2, test) values (1, NULL);
613 ERROR: null value for attribute "test" violates NOT NULL constraint
615 -- alter table / alter column [set/drop] not null tests
616 -- try altering system catalogs, should fail
617 alter table pg_class alter column relname drop not null;
618 ERROR: "pg_class" is a system catalog
619 alter table pg_class alter relname set not null;
620 ERROR: "pg_class" is a system catalog
621 -- try altering non-existent table, should fail
622 alter table non_existent alter column bar set not null;
623 ERROR: relation "non_existent" does not exist
624 alter table non_existent alter column bar drop not null;
625 ERROR: relation "non_existent" does not exist
626 -- test setting columns to null and not null and vice versa
627 -- test checking for null values and primary key
628 create table atacc1 (test int not null);
629 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
630 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
631 alter table atacc1 alter column test drop not null;
632 ERROR: attribute "test" is in a primary key
633 alter table atacc1 drop constraint "atacc1_pkey";
634 alter table atacc1 alter column test drop not null;
635 insert into atacc1 values (null);
636 alter table atacc1 alter test set not null;
637 ERROR: attribute "test" contains NULL values
639 alter table atacc1 alter test set not null;
640 -- try altering a non-existent column, should fail
641 alter table atacc1 alter bar set not null;
642 ERROR: attribute "bar" of relation "atacc1" does not exist
643 alter table atacc1 alter bar drop not null;
644 ERROR: attribute "bar" of relation "atacc1" does not exist
645 -- try altering the oid column, should fail
646 alter table atacc1 alter oid set not null;
647 ERROR: cannot alter system attribute "oid"
648 alter table atacc1 alter oid drop not null;
649 ERROR: cannot alter system attribute "oid"
650 -- try creating a view and altering that, should fail
651 create view myview as select * from atacc1;
652 alter table myview alter column test drop not null;
653 ERROR: "myview" is not a table
654 alter table myview alter column test set not null;
655 ERROR: "myview" is not a table
659 create table parent (a int);
660 create table child (b varchar(255)) inherits (parent);
661 alter table parent alter a set not null;
662 insert into parent values (NULL);
663 ERROR: null value for attribute "a" violates NOT NULL constraint
664 insert into child (a, b) values (NULL, 'foo');
665 ERROR: null value for attribute "a" violates NOT NULL constraint
666 alter table parent alter a drop not null;
667 insert into parent values (NULL);
668 insert into child (a, b) values (NULL, 'foo');
669 alter table only parent alter a set not null;
670 ERROR: attribute "a" contains NULL values
671 alter table child alter a set not null;
672 ERROR: attribute "a" contains NULL values
674 alter table only parent alter a set not null;
675 insert into parent values (NULL);
676 ERROR: null value for attribute "a" violates NOT NULL constraint
677 alter table child alter a set not null;
678 insert into child (a, b) values (NULL, 'foo');
679 ERROR: null value for attribute "a" violates NOT NULL constraint
681 alter table child alter a set not null;
682 insert into child (a, b) values (NULL, 'foo');
683 ERROR: null value for attribute "a" violates NOT NULL constraint
686 -- test setting and removing default values
687 create table def_test (
689 c2 text default 'initial_default'
691 insert into def_test default values;
692 alter table def_test alter column c1 drop default;
693 insert into def_test default values;
694 alter table def_test alter column c2 drop default;
695 insert into def_test default values;
696 alter table def_test alter column c1 set default 10;
697 alter table def_test alter column c2 set default 'new_default';
698 insert into def_test default values;
699 select * from def_test;
701 ----+-----------------
708 -- set defaults to an incorrect type: this should fail
709 alter table def_test alter column c1 set default 'wrong_datatype';
710 ERROR: pg_atoi: error in "wrong_datatype": can't parse "wrong_datatype"
711 alter table def_test alter column c2 set default 20;
712 -- set defaults on a non-existent column: this should fail
713 alter table def_test alter column c3 set default 30;
714 ERROR: attribute "c3" of relation "def_test" does not exist
715 -- set defaults on views: we need to create a view, add a rule
716 -- to allow insertions into it, and then alter the view to add
718 create view def_view_test as select * from def_test;
719 create rule def_view_test_ins as
720 on insert to def_view_test
721 do instead insert into def_test select new.*;
722 insert into def_view_test default values;
723 alter table def_view_test alter column c1 set default 45;
724 insert into def_view_test default values;
725 alter table def_view_test alter column c2 set default 'view_default';
726 insert into def_view_test default values;
727 select * from def_view_test;
729 ----+-----------------
739 drop rule def_view_test_ins on def_view_test;
740 drop view def_view_test;
742 -- alter table / drop column tests
743 -- try altering system catalogs, should fail
744 alter table pg_class drop column relname;
745 ERROR: "pg_class" is a system catalog
746 -- try altering non-existent table, should fail
747 alter table foo drop column bar;
748 ERROR: relation "foo" does not exist
749 -- test dropping columns
750 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
751 insert into atacc1 values (1, 2, 3, 4);
752 alter table atacc1 drop a;
753 alter table atacc1 drop a;
754 ERROR: attribute "a" of relation "atacc1" does not exist
756 select * from atacc1;
762 select * from atacc1 order by a;
763 ERROR: attribute "a" not found
764 select * from atacc1 order by "........pg.dropped.1........";
765 ERROR: attribute "........pg.dropped.1........" not found
766 select * from atacc1 group by a;
767 ERROR: attribute "a" not found
768 select * from atacc1 group by "........pg.dropped.1........";
769 ERROR: attribute "........pg.dropped.1........" not found
770 select atacc1.* from atacc1;
776 select a from atacc1;
777 ERROR: attribute "a" not found
778 select atacc1.a from atacc1;
779 ERROR: no such attribute atacc1.a
780 select b,c,d from atacc1;
786 select a,b,c,d from atacc1;
787 ERROR: attribute "a" not found
788 select * from atacc1 where a = 1;
789 ERROR: attribute "a" not found
790 select "........pg.dropped.1........" from atacc1;
791 ERROR: attribute "........pg.dropped.1........" not found
792 select atacc1."........pg.dropped.1........" from atacc1;
793 ERROR: no such attribute atacc1.........pg.dropped.1........
794 select "........pg.dropped.1........",b,c,d from atacc1;
795 ERROR: attribute "........pg.dropped.1........" not found
796 select * from atacc1 where "........pg.dropped.1........" = 1;
797 ERROR: attribute "........pg.dropped.1........" not found
799 update atacc1 set a = 3;
800 ERROR: attribute "a" of relation "atacc1" does not exist
801 update atacc1 set b = 2 where a = 3;
802 ERROR: attribute "a" not found
803 update atacc1 set "........pg.dropped.1........" = 3;
804 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
805 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
806 ERROR: attribute "........pg.dropped.1........" not found
808 insert into atacc1 values (10, 11, 12, 13);
809 ERROR: INSERT has more expressions than target columns
810 insert into atacc1 values (default, 11, 12, 13);
811 ERROR: INSERT has more expressions than target columns
812 insert into atacc1 values (11, 12, 13);
813 insert into atacc1 (a) values (10);
814 ERROR: attribute "a" of relation "atacc1" does not exist
815 insert into atacc1 (a) values (default);
816 ERROR: attribute "a" of relation "atacc1" does not exist
817 insert into atacc1 (a,b,c,d) values (10,11,12,13);
818 ERROR: attribute "a" of relation "atacc1" does not exist
819 insert into atacc1 (a,b,c,d) values (default,11,12,13);
820 ERROR: attribute "a" of relation "atacc1" does not exist
821 insert into atacc1 (b,c,d) values (11,12,13);
822 insert into atacc1 ("........pg.dropped.1........") values (10);
823 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
824 insert into atacc1 ("........pg.dropped.1........") values (default);
825 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
826 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
827 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
828 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
829 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
831 delete from atacc1 where a = 3;
832 ERROR: attribute "a" not found
833 delete from atacc1 where "........pg.dropped.1........" = 3;
834 ERROR: attribute "........pg.dropped.1........" not found
836 -- try dropping a non-existent column, should fail
837 alter table atacc1 drop bar;
838 ERROR: attribute "bar" of relation "atacc1" does not exist
839 -- try dropping the oid column, should fail
840 alter table atacc1 drop oid;
841 ERROR: cannot drop system attribute "oid"
842 -- try creating a view and altering that, should fail
843 create view myview as select * from atacc1;
844 select * from myview;
849 alter table myview drop d;
850 ERROR: "myview" is not a table
852 -- test some commands to make sure they fail on the dropped column
854 ERROR: attribute "a" of relation "atacc1" does not exist
855 analyze atacc1("........pg.dropped.1........");
856 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
857 vacuum analyze atacc1(a);
858 ERROR: attribute "a" of relation "atacc1" does not exist
859 vacuum analyze atacc1("........pg.dropped.1........");
860 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
861 comment on column atacc1.a is 'testing';
862 ERROR: attribute "a" of relation "atacc1" does not exist
863 comment on column atacc1."........pg.dropped.1........" is 'testing';
864 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
865 alter table atacc1 alter a set storage plain;
866 ERROR: attribute "a" of relation "atacc1" does not exist
867 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
868 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
869 alter table atacc1 alter a set statistics 0;
870 ERROR: attribute "a" of relation "atacc1" does not exist
871 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
872 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
873 alter table atacc1 alter a set default 3;
874 ERROR: attribute "a" of relation "atacc1" does not exist
875 alter table atacc1 alter "........pg.dropped.1........" set default 3;
876 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
877 alter table atacc1 alter a drop default;
878 ERROR: attribute "a" of relation "atacc1" does not exist
879 alter table atacc1 alter "........pg.dropped.1........" drop default;
880 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
881 alter table atacc1 alter a set not null;
882 ERROR: attribute "a" of relation "atacc1" does not exist
883 alter table atacc1 alter "........pg.dropped.1........" set not null;
884 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
885 alter table atacc1 alter a drop not null;
886 ERROR: attribute "a" of relation "atacc1" does not exist
887 alter table atacc1 alter "........pg.dropped.1........" drop not null;
888 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
889 alter table atacc1 rename a to x;
890 ERROR: attribute "a" does not exist
891 alter table atacc1 rename "........pg.dropped.1........" to x;
892 ERROR: attribute "........pg.dropped.1........" does not exist
893 alter table atacc1 add primary key(a);
894 ERROR: column "a" named in key does not exist
895 alter table atacc1 add primary key("........pg.dropped.1........");
896 ERROR: column "........pg.dropped.1........" named in key does not exist
897 alter table atacc1 add unique(a);
898 ERROR: column "a" named in key does not exist
899 alter table atacc1 add unique("........pg.dropped.1........");
900 ERROR: column "........pg.dropped.1........" named in key does not exist
901 alter table atacc1 add check (a > 3);
902 ERROR: attribute "a" not found
903 alter table atacc1 add check ("........pg.dropped.1........" > 3);
904 ERROR: attribute "........pg.dropped.1........" not found
905 create table atacc2 (id int4 unique);
906 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
907 alter table atacc1 add foreign key (a) references atacc2(id);
908 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
909 ERROR: column "a" referenced in foreign key constraint does not exist
910 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
911 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
912 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
913 alter table atacc2 add foreign key (id) references atacc1(a);
914 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
915 ERROR: column "a" referenced in foreign key constraint does not exist
916 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
917 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
918 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
920 create index "testing_idx" on atacc1(a);
921 ERROR: attribute "a" does not exist
922 create index "testing_idx" on atacc1("........pg.dropped.1........");
923 ERROR: attribute "........pg.dropped.1........" does not exist
924 -- test create as and select into
925 insert into atacc1 values (21, 22, 23);
926 create table test1 as select * from atacc1;
934 select * into test2 from atacc1;
942 -- try dropping all columns
943 alter table atacc1 drop c;
944 alter table atacc1 drop d;
945 alter table atacc1 drop b;
946 select * from atacc1;
953 create table parent (a int, b int, c int);
954 insert into parent values (1, 2, 3);
955 alter table parent drop a;
956 create table child (d varchar(255)) inherits (parent);
957 insert into child values (12, 13, 'testing');
958 select * from parent;
971 alter table parent drop c;
972 select * from parent;
988 create table test (a int4, b int4, c int4);
989 insert into test values (1,2,3);
990 alter table test drop a;
993 copy test(a) to stdout;
994 ERROR: attribute "a" of relation "test" does not exist
995 copy test("........pg.dropped.1........") to stdout;
996 ERROR: attribute "........pg.dropped.1........" of relation "test" does not exist
997 copy test from stdin;
998 ERROR: extra data after last expected column
999 CONTEXT: COPY FROM, line 1
1006 copy test from stdin;
1014 copy test(a) from stdin;
1015 ERROR: attribute "a" of relation "test" does not exist
1016 copy test("........pg.dropped.1........") from stdin;
1017 ERROR: attribute "........pg.dropped.1........" of relation "test" does not exist
1018 copy test(b,c) from stdin;
1029 create table dropColumn (a int, b int, e int);
1030 create table dropColumnChild (c int) inherits (dropColumn);
1031 create table dropColumnAnother (d int) inherits (dropColumnChild);
1032 -- these two should fail
1033 alter table dropColumnchild drop column a;
1034 ERROR: cannot drop inherited attribute "a"
1035 alter table only dropColumnChild drop column b;
1036 ERROR: cannot drop inherited attribute "b"
1037 -- these three should work
1038 alter table only dropColumn drop column e;
1039 alter table dropColumnChild drop column c;
1040 alter table dropColumn drop column a;
1041 create table renameColumn (a int);
1042 create table renameColumnChild (b int) inherits (renameColumn);
1043 create table renameColumnAnother (c int) inherits (renameColumnChild);
1044 -- these three should fail
1045 alter table renameColumnChild rename column a to d;
1046 ERROR: cannot rename inherited attribute "a"
1047 alter table only renameColumnChild rename column a to d;
1048 ERROR: inherited attribute "a" must be renamed in child tables too
1049 alter table only renameColumn rename column a to d;
1050 ERROR: inherited attribute "a" must be renamed in child tables too
1051 -- these should work
1052 alter table renameColumn rename column a to d;
1053 alter table renameColumnChild rename column b to a;
1055 alter table renameColumn add column w int;
1057 alter table only renameColumn add column x int;
1058 ERROR: attribute must be added to child tables too
1059 -- Test corner cases in dropping of inherited columns
1060 create table p1 (f1 int, f2 int);
1061 create table c1 (f1 int not null) inherits(p1);
1062 NOTICE: merging attribute "f1" with inherited definition
1063 -- should be rejected since c1.f1 is inherited
1064 alter table c1 drop column f1;
1065 ERROR: cannot drop inherited attribute "f1"
1067 alter table p1 drop column f1;
1068 -- c1.f1 is still there, but no longer inherited
1074 alter table c1 drop column f1;
1076 ERROR: attribute "f1" not found
1077 drop table p1 cascade;
1078 NOTICE: drop cascades to table c1
1079 create table p1 (f1 int, f2 int);
1080 create table c1 () inherits(p1);
1081 -- should be rejected since c1.f1 is inherited
1082 alter table c1 drop column f1;
1083 ERROR: cannot drop inherited attribute "f1"
1084 alter table p1 drop column f1;
1085 -- c1.f1 is dropped now, since there is no local definition for it
1087 ERROR: attribute "f1" not found
1088 drop table p1 cascade;
1089 NOTICE: drop cascades to table c1
1090 create table p1 (f1 int, f2 int);
1091 create table c1 () inherits(p1);
1092 -- should be rejected since c1.f1 is inherited
1093 alter table c1 drop column f1;
1094 ERROR: cannot drop inherited attribute "f1"
1095 alter table only p1 drop column f1;
1096 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1097 alter table c1 drop column f1;
1098 drop table p1 cascade;
1099 NOTICE: drop cascades to table c1
1100 create table p1 (f1 int, f2 int);
1101 create table c1 (f1 int not null) inherits(p1);
1102 NOTICE: merging attribute "f1" with inherited definition
1103 -- should be rejected since c1.f1 is inherited
1104 alter table c1 drop column f1;
1105 ERROR: cannot drop inherited attribute "f1"
1106 alter table only p1 drop column f1;
1107 -- c1.f1 is still there, but no longer inherited
1108 alter table c1 drop column f1;
1109 drop table p1 cascade;
1110 NOTICE: drop cascades to table c1
1111 create table p1(id int, name text);
1112 create table p2(id2 int, name text, height int);
1113 create table c1(age int) inherits(p1,p2);
1114 NOTICE: merging multiple inherited definitions of attribute "name"
1115 create table gc1() inherits (c1);
1116 select relname, attname, attinhcount, attislocal
1117 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1118 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1119 order by relname, attnum;
1120 relname | attname | attinhcount | attislocal
1121 ---------+---------+-------------+------------
1130 gc1 | height | 1 | f
1140 alter table only p1 drop column name;
1141 -- should work. Now c1.name is local and inhcount is 0.
1142 alter table p2 drop column name;
1143 -- should be rejected since its inherited
1144 alter table gc1 drop column name;
1145 ERROR: cannot drop inherited attribute "name"
1146 -- should work, and drop gc1.name along
1147 alter table c1 drop column name;
1148 -- should fail: column does not exist
1149 alter table gc1 drop column name;
1150 ERROR: attribute "name" of relation "gc1" does not exist
1151 -- should work and drop the attribute in all tables
1152 alter table p2 drop column height;
1153 select relname, attname, attinhcount, attislocal
1154 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1155 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1156 order by relname, attnum;
1157 relname | attname | attinhcount | attislocal
1158 ---------+---------+-------------+------------
1169 drop table p1, p2 cascade;
1170 NOTICE: drop cascades to table c1
1171 NOTICE: drop cascades to table gc1
1173 -- Test the ALTER TABLE WITHOUT OIDS command
1175 create table altstartwith (col integer) with oids;
1176 insert into altstartwith values (1);
1177 select oid > 0, * from altstartwith;
1183 alter table altstartwith set without oids;
1184 select oid > 0, * from altstartwith; -- fails
1185 ERROR: attribute "oid" not found
1186 select * from altstartwith;
1192 -- Run inheritance tests
1193 create table altwithoid (col integer) with oids;
1194 -- Inherits parents oid column
1195 create table altinhoid () inherits (altwithoid) without oids;
1196 insert into altinhoid values (1);
1197 select oid > 0, * from altwithoid;
1203 select oid > 0, * from altinhoid;
1209 alter table altwithoid set without oids;
1210 alter table altinhoid set without oids; -- fails
1211 NOTICE: table "altinhoid" is already WITHOUT OIDS
1212 select oid > 0, * from altwithoid; -- fails
1213 ERROR: attribute "oid" not found
1214 select oid > 0, * from altinhoid; -- fails
1215 ERROR: attribute "oid" not found
1216 select * from altwithoid;
1222 select * from altinhoid;
1228 -- test renumbering of child-table columns in inherited operations
1229 create table p1 (f1 int);
1230 create table c1 (f2 text, f3 int) inherits (p1);
1231 alter table p1 add column a1 int check (a1 > 0);
1232 alter table p1 add column f2 text;
1233 NOTICE: merging definition of column "f2" for child "c1"
1234 insert into p1 values (1,2,'abc');
1235 insert into c1 values(11,'xyz',33,0); -- should fail
1236 ERROR: new row for relation "c1" violates CHECK constraint "p1_a1"
1237 insert into c1 values(11,'xyz',33,22);
1245 update p1 set a1 = a1 + 1, f2 = upper(f2);
1253 drop table p1 cascade;
1254 NOTICE: drop cascades to table c1
1255 NOTICE: drop cascades to constraint p1_a1 on table c1
1256 -- test that operations with a dropped column do not try to reference
1258 create domain mytype as text;
1259 create temp table foo (f1 text, f2 mytype, f3 text);
1260 insert into foo values('aa','bb','cc');
1267 drop domain mytype cascade;
1268 NOTICE: drop cascades to table foo column f2
1275 insert into foo values('qq','rr');
1283 update foo set f3 = 'zz';
1291 select f3,max(f1) from foo group by f3;