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: could not create unique index
495 DETAIL: Table contains duplicated values.
496 insert into atacc1 (test) values (3);
498 -- let's do one where the unique constraint fails
499 -- because the column doesn't exist
500 create table atacc1 ( test int );
501 -- add a unique constraint (fails)
502 alter table atacc1 add constraint atacc_test1 unique (test1);
503 ERROR: column "test1" named in key does not exist
505 -- something a little more complicated
506 create table atacc1 ( test int, test2 int);
507 -- add a unique constraint
508 alter table atacc1 add constraint atacc_test1 unique (test, test2);
509 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
510 -- insert initial value
511 insert into atacc1 (test,test2) values (4,4);
513 insert into atacc1 (test,test2) values (4,4);
514 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
515 -- should all succeed
516 insert into atacc1 (test,test2) values (4,5);
517 insert into atacc1 (test,test2) values (5,4);
518 insert into atacc1 (test,test2) values (5,5);
520 -- lets do some naming tests
521 create table atacc1 (test int, test2 int, unique(test));
522 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
523 alter table atacc1 add unique (test2);
524 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
525 -- should fail for @@ second one @@
526 insert into atacc1 (test2, test) values (3, 3);
527 insert into atacc1 (test2, test) values (2, 3);
528 ERROR: duplicate key violates UNIQUE constraint "atacc1_test_key"
530 -- test primary key constraint adding
531 create table atacc1 ( test int );
532 -- add a primary key constraint
533 alter table atacc1 add constraint atacc_test1 primary key (test);
534 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
535 -- insert first value
536 insert into atacc1 (test) values (2);
538 insert into atacc1 (test) values (2);
539 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
541 insert into atacc1 (test) values (4);
542 -- inserting NULL should fail
543 insert into atacc1 (test) values(NULL);
544 ERROR: null value for attribute "test" violates NOT NULL constraint
545 -- try adding a second primary key (should fail)
546 alter table atacc1 add constraint atacc_oid1 primary key(oid);
547 ERROR: multiple primary keys for table "atacc1" are not allowed
548 -- drop first primary key constraint
549 alter table atacc1 drop constraint atacc_test1 restrict;
550 -- try adding a primary key on oid (should succeed)
551 alter table atacc1 add constraint atacc_oid1 primary key(oid);
552 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
554 -- let's do one where the primary key constraint fails when added
555 create table atacc1 ( test int );
556 -- insert soon to be failing rows
557 insert into atacc1 (test) values (2);
558 insert into atacc1 (test) values (2);
559 -- add a primary key (fails)
560 alter table atacc1 add constraint atacc_test1 primary key (test);
561 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
562 ERROR: could not create unique index
563 DETAIL: Table contains duplicated values.
564 insert into atacc1 (test) values (3);
566 -- let's do another one where the primary key constraint fails when added
567 create table atacc1 ( test int );
568 -- insert soon to be failing row
569 insert into atacc1 (test) values (NULL);
570 -- add a primary key (fails)
571 alter table atacc1 add constraint atacc_test1 primary key (test);
572 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
573 ERROR: attribute "test" contains NULL values
574 insert into atacc1 (test) values (3);
576 -- let's do one where the primary key constraint fails
577 -- because the column doesn't exist
578 create table atacc1 ( test int );
579 -- add a primary key constraint (fails)
580 alter table atacc1 add constraint atacc_test1 primary key (test1);
581 ERROR: column "test1" named in key does not exist
583 -- something a little more complicated
584 create table atacc1 ( test int, test2 int);
585 -- add a primary key constraint
586 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
587 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
588 -- try adding a second primary key - should fail
589 alter table atacc1 add constraint atacc_test2 primary key (test);
590 ERROR: multiple primary keys for table "atacc1" are not allowed
591 -- insert initial value
592 insert into atacc1 (test,test2) values (4,4);
594 insert into atacc1 (test,test2) values (4,4);
595 ERROR: duplicate key violates UNIQUE constraint "atacc_test1"
596 insert into atacc1 (test,test2) values (NULL,3);
597 ERROR: null value for attribute "test" violates NOT NULL constraint
598 insert into atacc1 (test,test2) values (3, NULL);
599 ERROR: null value for attribute "test2" violates NOT NULL constraint
600 insert into atacc1 (test,test2) values (NULL,NULL);
601 ERROR: null value for attribute "test" violates NOT NULL constraint
602 -- should all succeed
603 insert into atacc1 (test,test2) values (4,5);
604 insert into atacc1 (test,test2) values (5,4);
605 insert into atacc1 (test,test2) values (5,5);
607 -- lets do some naming tests
608 create table atacc1 (test int, test2 int, primary key(test));
609 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
610 -- only first should succeed
611 insert into atacc1 (test2, test) values (3, 3);
612 insert into atacc1 (test2, test) values (2, 3);
613 ERROR: duplicate key violates UNIQUE constraint "atacc1_pkey"
614 insert into atacc1 (test2, test) values (1, NULL);
615 ERROR: null value for attribute "test" violates NOT NULL constraint
617 -- alter table / alter column [set/drop] not null tests
618 -- try altering system catalogs, should fail
619 alter table pg_class alter column relname drop not null;
620 ERROR: permission denied: "pg_class" is a system catalog
621 alter table pg_class alter relname set not null;
622 ERROR: permission denied: "pg_class" is a system catalog
623 -- try altering non-existent table, should fail
624 alter table non_existent alter column bar set not null;
625 ERROR: relation "non_existent" does not exist
626 alter table non_existent alter column bar drop not null;
627 ERROR: relation "non_existent" does not exist
628 -- test setting columns to null and not null and vice versa
629 -- test checking for null values and primary key
630 create table atacc1 (test int not null);
631 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
632 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
633 alter table atacc1 alter column test drop not null;
634 ERROR: attribute "test" is in a primary key
635 alter table atacc1 drop constraint "atacc1_pkey";
636 alter table atacc1 alter column test drop not null;
637 insert into atacc1 values (null);
638 alter table atacc1 alter test set not null;
639 ERROR: attribute "test" contains NULL values
641 alter table atacc1 alter test set not null;
642 -- try altering a non-existent column, should fail
643 alter table atacc1 alter bar set not null;
644 ERROR: attribute "bar" of relation "atacc1" does not exist
645 alter table atacc1 alter bar drop not null;
646 ERROR: attribute "bar" of relation "atacc1" does not exist
647 -- try altering the oid column, should fail
648 alter table atacc1 alter oid set not null;
649 ERROR: cannot alter system attribute "oid"
650 alter table atacc1 alter oid drop not null;
651 ERROR: cannot alter system attribute "oid"
652 -- try creating a view and altering that, should fail
653 create view myview as select * from atacc1;
654 alter table myview alter column test drop not null;
655 ERROR: "myview" is not a table
656 alter table myview alter column test set not null;
657 ERROR: "myview" is not a table
661 create table parent (a int);
662 create table child (b varchar(255)) inherits (parent);
663 alter table parent alter a set not null;
664 insert into parent values (NULL);
665 ERROR: null value for attribute "a" violates NOT NULL constraint
666 insert into child (a, b) values (NULL, 'foo');
667 ERROR: null value for attribute "a" violates NOT NULL constraint
668 alter table parent alter a drop not null;
669 insert into parent values (NULL);
670 insert into child (a, b) values (NULL, 'foo');
671 alter table only parent alter a set not null;
672 ERROR: attribute "a" contains NULL values
673 alter table child alter a set not null;
674 ERROR: attribute "a" contains NULL values
676 alter table only parent alter a set not null;
677 insert into parent values (NULL);
678 ERROR: null value for attribute "a" violates NOT NULL constraint
679 alter table child alter a set not null;
680 insert into child (a, b) values (NULL, 'foo');
681 ERROR: null value for attribute "a" violates NOT NULL constraint
683 alter table child alter a set not null;
684 insert into child (a, b) values (NULL, 'foo');
685 ERROR: null value for attribute "a" violates NOT NULL constraint
688 -- test setting and removing default values
689 create table def_test (
691 c2 text default 'initial_default'
693 insert into def_test default values;
694 alter table def_test alter column c1 drop default;
695 insert into def_test default values;
696 alter table def_test alter column c2 drop default;
697 insert into def_test default values;
698 alter table def_test alter column c1 set default 10;
699 alter table def_test alter column c2 set default 'new_default';
700 insert into def_test default values;
701 select * from def_test;
703 ----+-----------------
710 -- set defaults to an incorrect type: this should fail
711 alter table def_test alter column c1 set default 'wrong_datatype';
712 ERROR: invalid input syntax for integer: "wrong_datatype"
713 alter table def_test alter column c2 set default 20;
714 -- set defaults on a non-existent column: this should fail
715 alter table def_test alter column c3 set default 30;
716 ERROR: attribute "c3" of relation "def_test" does not exist
717 -- set defaults on views: we need to create a view, add a rule
718 -- to allow insertions into it, and then alter the view to add
720 create view def_view_test as select * from def_test;
721 create rule def_view_test_ins as
722 on insert to def_view_test
723 do instead insert into def_test select new.*;
724 insert into def_view_test default values;
725 alter table def_view_test alter column c1 set default 45;
726 insert into def_view_test default values;
727 alter table def_view_test alter column c2 set default 'view_default';
728 insert into def_view_test default values;
729 select * from def_view_test;
731 ----+-----------------
741 drop rule def_view_test_ins on def_view_test;
742 drop view def_view_test;
744 -- alter table / drop column tests
745 -- try altering system catalogs, should fail
746 alter table pg_class drop column relname;
747 ERROR: permission denied: "pg_class" is a system catalog
748 -- try altering non-existent table, should fail
749 alter table foo drop column bar;
750 ERROR: relation "foo" does not exist
751 -- test dropping columns
752 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
753 insert into atacc1 values (1, 2, 3, 4);
754 alter table atacc1 drop a;
755 alter table atacc1 drop a;
756 ERROR: attribute "a" of relation "atacc1" does not exist
758 select * from atacc1;
764 select * from atacc1 order by a;
765 ERROR: attribute "a" not found
766 select * from atacc1 order by "........pg.dropped.1........";
767 ERROR: attribute "........pg.dropped.1........" not found
768 select * from atacc1 group by a;
769 ERROR: attribute "a" not found
770 select * from atacc1 group by "........pg.dropped.1........";
771 ERROR: attribute "........pg.dropped.1........" not found
772 select atacc1.* from atacc1;
778 select a from atacc1;
779 ERROR: attribute "a" not found
780 select atacc1.a from atacc1;
781 ERROR: no such attribute atacc1.a
782 select b,c,d from atacc1;
788 select a,b,c,d from atacc1;
789 ERROR: attribute "a" not found
790 select * from atacc1 where a = 1;
791 ERROR: attribute "a" not found
792 select "........pg.dropped.1........" from atacc1;
793 ERROR: attribute "........pg.dropped.1........" not found
794 select atacc1."........pg.dropped.1........" from atacc1;
795 ERROR: no such attribute atacc1.........pg.dropped.1........
796 select "........pg.dropped.1........",b,c,d from atacc1;
797 ERROR: attribute "........pg.dropped.1........" not found
798 select * from atacc1 where "........pg.dropped.1........" = 1;
799 ERROR: attribute "........pg.dropped.1........" not found
801 update atacc1 set a = 3;
802 ERROR: attribute "a" of relation "atacc1" does not exist
803 update atacc1 set b = 2 where a = 3;
804 ERROR: attribute "a" not found
805 update atacc1 set "........pg.dropped.1........" = 3;
806 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
807 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
808 ERROR: attribute "........pg.dropped.1........" not found
810 insert into atacc1 values (10, 11, 12, 13);
811 ERROR: INSERT has more expressions than target columns
812 insert into atacc1 values (default, 11, 12, 13);
813 ERROR: INSERT has more expressions than target columns
814 insert into atacc1 values (11, 12, 13);
815 insert into atacc1 (a) values (10);
816 ERROR: attribute "a" of relation "atacc1" does not exist
817 insert into atacc1 (a) values (default);
818 ERROR: attribute "a" of relation "atacc1" does not exist
819 insert into atacc1 (a,b,c,d) values (10,11,12,13);
820 ERROR: attribute "a" of relation "atacc1" does not exist
821 insert into atacc1 (a,b,c,d) values (default,11,12,13);
822 ERROR: attribute "a" of relation "atacc1" does not exist
823 insert into atacc1 (b,c,d) values (11,12,13);
824 insert into atacc1 ("........pg.dropped.1........") values (10);
825 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
826 insert into atacc1 ("........pg.dropped.1........") values (default);
827 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
828 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
829 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
830 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
831 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
833 delete from atacc1 where a = 3;
834 ERROR: attribute "a" not found
835 delete from atacc1 where "........pg.dropped.1........" = 3;
836 ERROR: attribute "........pg.dropped.1........" not found
838 -- try dropping a non-existent column, should fail
839 alter table atacc1 drop bar;
840 ERROR: attribute "bar" of relation "atacc1" does not exist
841 -- try dropping the oid column, should fail
842 alter table atacc1 drop oid;
843 ERROR: cannot drop system attribute "oid"
844 -- try creating a view and altering that, should fail
845 create view myview as select * from atacc1;
846 select * from myview;
851 alter table myview drop d;
852 ERROR: "myview" is not a table
854 -- test some commands to make sure they fail on the dropped column
856 ERROR: attribute "a" of relation "atacc1" does not exist
857 analyze atacc1("........pg.dropped.1........");
858 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
859 vacuum analyze atacc1(a);
860 ERROR: attribute "a" of relation "atacc1" does not exist
861 vacuum analyze atacc1("........pg.dropped.1........");
862 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
863 comment on column atacc1.a is 'testing';
864 ERROR: attribute "a" of relation "atacc1" does not exist
865 comment on column atacc1."........pg.dropped.1........" is 'testing';
866 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
867 alter table atacc1 alter a set storage plain;
868 ERROR: attribute "a" of relation "atacc1" does not exist
869 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
870 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
871 alter table atacc1 alter a set statistics 0;
872 ERROR: attribute "a" of relation "atacc1" does not exist
873 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
874 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
875 alter table atacc1 alter a set default 3;
876 ERROR: attribute "a" of relation "atacc1" does not exist
877 alter table atacc1 alter "........pg.dropped.1........" set default 3;
878 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
879 alter table atacc1 alter a drop default;
880 ERROR: attribute "a" of relation "atacc1" does not exist
881 alter table atacc1 alter "........pg.dropped.1........" drop default;
882 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
883 alter table atacc1 alter a set not null;
884 ERROR: attribute "a" of relation "atacc1" does not exist
885 alter table atacc1 alter "........pg.dropped.1........" set not null;
886 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
887 alter table atacc1 alter a drop not null;
888 ERROR: attribute "a" of relation "atacc1" does not exist
889 alter table atacc1 alter "........pg.dropped.1........" drop not null;
890 ERROR: attribute "........pg.dropped.1........" of relation "atacc1" does not exist
891 alter table atacc1 rename a to x;
892 ERROR: attribute "a" does not exist
893 alter table atacc1 rename "........pg.dropped.1........" to x;
894 ERROR: attribute "........pg.dropped.1........" does not exist
895 alter table atacc1 add primary key(a);
896 ERROR: column "a" named in key does not exist
897 alter table atacc1 add primary key("........pg.dropped.1........");
898 ERROR: column "........pg.dropped.1........" named in key does not exist
899 alter table atacc1 add unique(a);
900 ERROR: column "a" named in key does not exist
901 alter table atacc1 add unique("........pg.dropped.1........");
902 ERROR: column "........pg.dropped.1........" named in key does not exist
903 alter table atacc1 add check (a > 3);
904 ERROR: attribute "a" not found
905 alter table atacc1 add check ("........pg.dropped.1........" > 3);
906 ERROR: attribute "........pg.dropped.1........" not found
907 create table atacc2 (id int4 unique);
908 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
909 alter table atacc1 add foreign key (a) references atacc2(id);
910 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
911 ERROR: column "a" referenced in foreign key constraint does not exist
912 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
913 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
914 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
915 alter table atacc2 add foreign key (id) references atacc1(a);
916 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
917 ERROR: column "a" referenced in foreign key constraint does not exist
918 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
919 NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
920 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
922 create index "testing_idx" on atacc1(a);
923 ERROR: attribute "a" does not exist
924 create index "testing_idx" on atacc1("........pg.dropped.1........");
925 ERROR: attribute "........pg.dropped.1........" does not exist
926 -- test create as and select into
927 insert into atacc1 values (21, 22, 23);
928 create table test1 as select * from atacc1;
936 select * into test2 from atacc1;
944 -- try dropping all columns
945 alter table atacc1 drop c;
946 alter table atacc1 drop d;
947 alter table atacc1 drop b;
948 select * from atacc1;
955 create table parent (a int, b int, c int);
956 insert into parent values (1, 2, 3);
957 alter table parent drop a;
958 create table child (d varchar(255)) inherits (parent);
959 insert into child values (12, 13, 'testing');
960 select * from parent;
973 alter table parent drop c;
974 select * from parent;
990 create table test (a int4, b int4, c int4);
991 insert into test values (1,2,3);
992 alter table test drop a;
995 copy test(a) to stdout;
996 ERROR: attribute "a" of relation "test" does not exist
997 copy test("........pg.dropped.1........") to stdout;
998 ERROR: attribute "........pg.dropped.1........" of relation "test" does not exist
999 copy test from stdin;
1000 ERROR: extra data after last expected column
1001 CONTEXT: COPY FROM, line 1
1008 copy test from stdin;
1016 copy test(a) from stdin;
1017 ERROR: attribute "a" of relation "test" does not exist
1018 copy test("........pg.dropped.1........") from stdin;
1019 ERROR: attribute "........pg.dropped.1........" of relation "test" does not exist
1020 copy test(b,c) from stdin;
1031 create table dropColumn (a int, b int, e int);
1032 create table dropColumnChild (c int) inherits (dropColumn);
1033 create table dropColumnAnother (d int) inherits (dropColumnChild);
1034 -- these two should fail
1035 alter table dropColumnchild drop column a;
1036 ERROR: cannot drop inherited attribute "a"
1037 alter table only dropColumnChild drop column b;
1038 ERROR: cannot drop inherited attribute "b"
1039 -- these three should work
1040 alter table only dropColumn drop column e;
1041 alter table dropColumnChild drop column c;
1042 alter table dropColumn drop column a;
1043 create table renameColumn (a int);
1044 create table renameColumnChild (b int) inherits (renameColumn);
1045 create table renameColumnAnother (c int) inherits (renameColumnChild);
1046 -- these three should fail
1047 alter table renameColumnChild rename column a to d;
1048 ERROR: cannot rename inherited attribute "a"
1049 alter table only renameColumnChild rename column a to d;
1050 ERROR: inherited attribute "a" must be renamed in child tables too
1051 alter table only renameColumn rename column a to d;
1052 ERROR: inherited attribute "a" must be renamed in child tables too
1053 -- these should work
1054 alter table renameColumn rename column a to d;
1055 alter table renameColumnChild rename column b to a;
1057 alter table renameColumn add column w int;
1059 alter table only renameColumn add column x int;
1060 ERROR: attribute must be added to child tables too
1061 -- Test corner cases in dropping of inherited columns
1062 create table p1 (f1 int, f2 int);
1063 create table c1 (f1 int not null) inherits(p1);
1064 NOTICE: merging attribute "f1" with inherited definition
1065 -- should be rejected since c1.f1 is inherited
1066 alter table c1 drop column f1;
1067 ERROR: cannot drop inherited attribute "f1"
1069 alter table p1 drop column f1;
1070 -- c1.f1 is still there, but no longer inherited
1076 alter table c1 drop column f1;
1078 ERROR: attribute "f1" not found
1079 drop table p1 cascade;
1080 NOTICE: drop cascades to table c1
1081 create table p1 (f1 int, f2 int);
1082 create table c1 () inherits(p1);
1083 -- should be rejected since c1.f1 is inherited
1084 alter table c1 drop column f1;
1085 ERROR: cannot drop inherited attribute "f1"
1086 alter table p1 drop column f1;
1087 -- c1.f1 is dropped now, since there is no local definition for it
1089 ERROR: attribute "f1" not found
1090 drop table p1 cascade;
1091 NOTICE: drop cascades to table c1
1092 create table p1 (f1 int, f2 int);
1093 create table c1 () inherits(p1);
1094 -- should be rejected since c1.f1 is inherited
1095 alter table c1 drop column f1;
1096 ERROR: cannot drop inherited attribute "f1"
1097 alter table only p1 drop column f1;
1098 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1099 alter table c1 drop column f1;
1100 drop table p1 cascade;
1101 NOTICE: drop cascades to table c1
1102 create table p1 (f1 int, f2 int);
1103 create table c1 (f1 int not null) inherits(p1);
1104 NOTICE: merging attribute "f1" with inherited definition
1105 -- should be rejected since c1.f1 is inherited
1106 alter table c1 drop column f1;
1107 ERROR: cannot drop inherited attribute "f1"
1108 alter table only p1 drop column f1;
1109 -- c1.f1 is still there, but no longer inherited
1110 alter table c1 drop column f1;
1111 drop table p1 cascade;
1112 NOTICE: drop cascades to table c1
1113 create table p1(id int, name text);
1114 create table p2(id2 int, name text, height int);
1115 create table c1(age int) inherits(p1,p2);
1116 NOTICE: merging multiple inherited definitions of attribute "name"
1117 create table gc1() inherits (c1);
1118 select relname, attname, attinhcount, attislocal
1119 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1120 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1121 order by relname, attnum;
1122 relname | attname | attinhcount | attislocal
1123 ---------+---------+-------------+------------
1132 gc1 | height | 1 | f
1142 alter table only p1 drop column name;
1143 -- should work. Now c1.name is local and inhcount is 0.
1144 alter table p2 drop column name;
1145 -- should be rejected since its inherited
1146 alter table gc1 drop column name;
1147 ERROR: cannot drop inherited attribute "name"
1148 -- should work, and drop gc1.name along
1149 alter table c1 drop column name;
1150 -- should fail: column does not exist
1151 alter table gc1 drop column name;
1152 ERROR: attribute "name" of relation "gc1" does not exist
1153 -- should work and drop the attribute in all tables
1154 alter table p2 drop column height;
1155 select relname, attname, attinhcount, attislocal
1156 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1157 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1158 order by relname, attnum;
1159 relname | attname | attinhcount | attislocal
1160 ---------+---------+-------------+------------
1171 drop table p1, p2 cascade;
1172 NOTICE: drop cascades to table c1
1173 NOTICE: drop cascades to table gc1
1175 -- Test the ALTER TABLE WITHOUT OIDS command
1177 create table altstartwith (col integer) with oids;
1178 insert into altstartwith values (1);
1179 select oid > 0, * from altstartwith;
1185 alter table altstartwith set without oids;
1186 select oid > 0, * from altstartwith; -- fails
1187 ERROR: attribute "oid" not found
1188 select * from altstartwith;
1194 -- Run inheritance tests
1195 create table altwithoid (col integer) with oids;
1196 -- Inherits parents oid column
1197 create table altinhoid () inherits (altwithoid) without oids;
1198 insert into altinhoid values (1);
1199 select oid > 0, * from altwithoid;
1205 select oid > 0, * from altinhoid;
1211 alter table altwithoid set without oids;
1212 alter table altinhoid set without oids;
1213 select oid > 0, * from altwithoid; -- fails
1214 ERROR: attribute "oid" not found
1215 select oid > 0, * from altinhoid; -- fails
1216 ERROR: attribute "oid" not found
1217 select * from altwithoid;
1223 select * from altinhoid;
1229 -- test renumbering of child-table columns in inherited operations
1230 create table p1 (f1 int);
1231 create table c1 (f2 text, f3 int) inherits (p1);
1232 alter table p1 add column a1 int check (a1 > 0);
1233 alter table p1 add column f2 text;
1234 NOTICE: merging definition of column "f2" for child "c1"
1235 insert into p1 values (1,2,'abc');
1236 insert into c1 values(11,'xyz',33,0); -- should fail
1237 ERROR: new row for relation "c1" violates CHECK constraint "p1_a1"
1238 insert into c1 values(11,'xyz',33,22);
1246 update p1 set a1 = a1 + 1, f2 = upper(f2);
1254 drop table p1 cascade;
1255 NOTICE: drop cascades to table c1
1256 NOTICE: drop cascades to constraint p1_a1 on table c1
1257 -- test that operations with a dropped column do not try to reference
1259 create domain mytype as text;
1260 create temp table foo (f1 text, f2 mytype, f3 text);
1261 insert into foo values('aa','bb','cc');
1268 drop domain mytype cascade;
1269 NOTICE: drop cascades to table foo column f2
1276 insert into foo values('qq','rr');
1284 update foo set f3 = 'zz';
1292 select f3,max(f1) from foo group by f3;