X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=src%2Ftest%2Fregress%2Fexpected%2Falter_table.out;h=bf2d66af49e5a1322231359d8c27bf964a22ab89;hb=f4a3789b39e91635ad9eec6d46be305d3302219e;hp=7d6209c3e8c1951c1900f2841ba1676e2f003343;hpb=92288a1cf9490be3835dc8524ee2ba407f1b885a;p=postgresql diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 7d6209c3e8..bf2d66af49 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3,7 +3,11 @@ -- add attribute -- CREATE TABLE tmp (initial int4); -ALTER TABLE tmp ADD COLUMN a int4; +COMMENT ON TABLE tmp_wrong IS 'table comment'; +ERROR: relation "tmp_wrong" does not exist +COMMENT ON TABLE tmp IS 'table comment'; +COMMENT ON TABLE tmp IS NULL; +ALTER TABLE tmp ADD COLUMN a int4 default 3; ALTER TABLE tmp ADD COLUMN b name; ALTER TABLE tmp ADD COLUMN c text; ALTER TABLE tmp ADD COLUMN d float8; @@ -24,8 +28,8 @@ ALTER TABLE tmp ADD COLUMN r lseg; ALTER TABLE tmp ADD COLUMN s path; ALTER TABLE tmp ADD COLUMN t box; ALTER TABLE tmp ADD COLUMN u tinterval; -ALTER TABLE tmp ADD COLUMN v datetime; -ALTER TABLE tmp ADD COLUMN w timespan; +ALTER TABLE tmp ADD COLUMN v timestamp; +ALTER TABLE tmp ADD COLUMN w interval; ALTER TABLE tmp ADD COLUMN x float8[]; ALTER TABLE tmp ADD COLUMN y float4[]; ALTER TABLE tmp ADD COLUMN z int2[]; @@ -69,8 +73,8 @@ ALTER TABLE tmp ADD COLUMN r lseg; ALTER TABLE tmp ADD COLUMN s path; ALTER TABLE tmp ADD COLUMN t box; ALTER TABLE tmp ADD COLUMN u tinterval; -ALTER TABLE tmp ADD COLUMN v datetime; -ALTER TABLE tmp ADD COLUMN w timespan; +ALTER TABLE tmp ADD COLUMN v timestamp; +ALTER TABLE tmp ADD COLUMN w interval; ALTER TABLE tmp ADD COLUMN x float8[]; ALTER TABLE tmp ADD COLUMN y float4[]; ALTER TABLE tmp ADD COLUMN z int2[]; @@ -90,172 +94,47 @@ SELECT * FROM tmp; DROP TABLE tmp; -- --- rename - --- should preserve indices, which we can check by seeing if a SELECT --- chooses an indexscan; however, in the absence of vacuum statistics --- it might not. Therefore, vacuum first. +-- rename - check on both non-temp and temp tables -- -VACUUM ANALYZE tenk1; -ALTER TABLE tenk1 RENAME TO ten_k; --- 20 values, sorted -SELECT unique1 FROM ten_k WHERE unique1 < 20; - unique1 ---------- - 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 - 11 - 12 - 13 - 14 - 15 - 16 - 17 - 18 - 19 -(20 rows) - --- 20 values, sorted -SELECT unique2 FROM ten_k WHERE unique2 < 20; - unique2 ---------- - 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 - 11 - 12 - 13 - 14 - 15 - 16 - 17 - 18 - 19 -(20 rows) - --- 100 values, sorted -SELECT hundred FROM ten_k WHERE hundred = 50; - hundred ---------- - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 -(100 rows) - -ALTER TABLE ten_k RENAME TO tenk1; +CREATE TABLE tmp (regtable int); +CREATE TEMP TABLE tmp (tmptable int); +ALTER TABLE tmp RENAME TO tmp_new; +SELECT * FROM tmp; + regtable +---------- +(0 rows) + +SELECT * FROM tmp_new; + tmptable +---------- +(0 rows) + +ALTER TABLE tmp RENAME TO tmp_new2; +SELECT * FROM tmp; -- should fail +ERROR: relation "tmp" does not exist +SELECT * FROM tmp_new; + tmptable +---------- +(0 rows) + +SELECT * FROM tmp_new2; + regtable +---------- +(0 rows) + +DROP TABLE tmp_new; +DROP TABLE tmp_new2; +-- ALTER TABLE ... RENAME on non-table relations +-- renaming indexes (FIXME: this should probably test the index's functionality) +ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1; +ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1; +-- renaming views +CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1; +ALTER TABLE tmp_view RENAME TO tmp_view_new; +-- hack to ensure we get an indexscan here +ANALYZE tenk1; +set enable_seqscan to off; +set enable_bitmapscan to off; -- 5 values, sorted SELECT unique1 FROM tenk1 WHERE unique1 < 5; unique1 @@ -267,12 +146,18 @@ SELECT unique1 FROM tenk1 WHERE unique1 < 5; 4 (5 rows) +reset enable_seqscan; +reset enable_bitmapscan; +DROP VIEW tmp_view_new; +-- toast-like relation name +alter table stud_emp rename to pg_toast_stud_emp; +alter table pg_toast_stud_emp rename to stud_emp; -- FOREIGN KEY CONSTRAINT adding TEST CREATE TABLE tmp2 (a int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp2_pkey' for table 'tmp2' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2" CREATE TABLE tmp3 (a int, b int); CREATE TABLE tmp4 (a int, b int, unique(a,b)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index 'tmp4_a_key' for table 'tmp4' +NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for table "tmp4" CREATE TABLE tmp5 (a int, b int); -- Insert rows into tmp2 (pktable) INSERT INTO tmp2 values (1); @@ -285,93 +170,103 @@ INSERT INTO tmp3 values (1,20); INSERT INTO tmp3 values (5,50); -- Try (and fail) to add constraint due to invalid source columns ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: ALTER TABLE: column "c" referenced in foreign key constraint does not exist +ERROR: column "c" referenced in foreign key constraint does not exist -- Try (and fail) to add constraint due to invalide destination columns explicitly given ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: UNIQUE constraint matching given keys for referenced table "tmp2" not found +ERROR: column "b" referenced in foreign key constraint does not exist -- Try (and fail) to add constraint due to invalid data ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: tmpconstr referential integrity violation - key referenced from tmp3 not found in tmp2 +ERROR: insert or update on table "tmp3" violates foreign key constraint "tmpconstr" +DETAIL: Key (a)=(5) is not present in table "tmp2". -- Delete failing row DELETE FROM tmp3 where a=5; -- Try (and succeed) ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on -- tmp4 is a,b ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: UNIQUE constraint matching given keys for referenced table "tmp4" not found +ERROR: there is no unique constraint matching given keys for referenced table "tmp4" DROP TABLE tmp5; DROP TABLE tmp4; DROP TABLE tmp3; -NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tmp2" -NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tmp2" DROP TABLE tmp2; -- Foreign key adding test with mixed types -- Note: these tables are TEMP to avoid name conflicts when this test -- is run in parallel with foreign_key.sql. CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' -CREATE TEMP TABLE FKTABLE (ftest1 text); --- This next should fail, because text=int does not exist +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +INSERT INTO PKTABLE VALUES(42); +CREATE TEMP TABLE FKTABLE (ftest1 inet); +-- This next should fail, because int=inet does not exist ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: Unable to identify an operator '=' for types 'text' and 'integer' - You will have to retype this query using an explicit cast +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. -- This should also fail for the same reason, but here we -- give the column name ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: Unable to identify an operator '=' for types 'text' and 'integer' - You will have to retype this query using an explicit cast --- This should succeed, even though they are different types --- because varchar=int does exist +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. DROP TABLE FKTABLE; -CREATE TEMP TABLE FKTABLE (ftest1 varchar); +-- This should succeed, even though they are different types, +-- because int=int8 exists and is a member of the integer opfamily +CREATE TEMP TABLE FKTABLE (ftest1 int8); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) --- As should this -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -DROP TABLE pktable; -NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" -NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" -DROP TABLE fktable; -CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 text, +-- Check it actually works +INSERT INTO FKTABLE VALUES(42); -- should succeed +INSERT INTO FKTABLE VALUES(43); -- should fail +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(43) is not present in table "pktable". +DROP TABLE FKTABLE; +-- This should fail, because we'd have to cast numeric to int which is +-- not an implicit coercion (or use numeric=numeric, but that's not part +-- of the integer opfamily) +CREATE TEMP TABLE FKTABLE (ftest1 numeric); +ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer. +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; +-- On the other hand, this should work because int implicitly promotes to +-- numeric, and we allow promotion on the FK side +CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +INSERT INTO PKTABLE VALUES(42); +CREATE TEMP TABLE FKTABLE (ftest1 int); +ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; +-- Check it actually works +INSERT INTO FKTABLE VALUES(42); -- should succeed +INSERT INTO FKTABLE VALUES(43); -- should fail +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(43) is not present in table "pktable". +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; +CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -- This should fail, because we just chose really odd types -CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime); +CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer' - You will have to retype this query using an explicit cast --- Again, so should this... +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 datetime); +-- Again, so should this... +CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer' - You will have to retype this query using an explicit cast --- This fails because we mixed up the column ordering +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 text); +-- This fails because we mixed up the column ordering +CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: Unable to identify an operator '=' for types 'integer' and 'text' - You will have to retype this query using an explicit cast +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet. -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: Unable to identify an operator '=' for types 'text' and 'integer' - You will have to retype this query using an explicit cast +ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented +DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. -- temp tables should go away by themselves, need not drop them. -- test check constraint adding create table atacc1 ( test int ); @@ -379,7 +274,7 @@ create table atacc1 ( test int ); alter table atacc1 add constraint atacc_test1 check (test>3); -- should fail insert into atacc1 (test) values (2); -ERROR: ExecAppend: rejected due to CHECK constraint atacc_test1 +ERROR: new row for relation "atacc1" violates check constraint "atacc_test1" -- should succeed insert into atacc1 (test) values (4); drop table atacc1; @@ -389,14 +284,14 @@ create table atacc1 ( test int ); insert into atacc1 (test) values (2); -- add a check constraint (fails) alter table atacc1 add constraint atacc_test1 check (test>3); -ERROR: AlterTableAddConstraint: rejected due to CHECK constraint atacc_test1 +ERROR: check constraint "atacc_test1" is violated by some row insert into atacc1 (test) values (4); drop table atacc1; -- let's do one where the check fails because the column doesn't exist create table atacc1 ( test int ); -- add a check constraint (fails) alter table atacc1 add constraint atacc_test1 check (test1>3); -ERROR: Attribute 'test1' not found +ERROR: column "test1" does not exist drop table atacc1; -- something a little more complicated create table atacc1 ( test int, test2 int, test3 int); @@ -404,7 +299,7 @@ create table atacc1 ( test int, test2 int, test3 int); alter table atacc1 add constraint atacc_test1 check (test+test23), test2 int); alter table atacc1 add check (test2>test); -- should fail for $2 insert into atacc1 (test2, test) values (3, 4); -ERROR: ExecAppend: rejected due to CHECK constraint $2 +ERROR: new row for relation "atacc1" violates check constraint "atacc1_check" drop table atacc1; -- inheritance related tests create table atacc1 (test int); @@ -422,15 +317,67 @@ create table atacc3 (test3 int) inherits (atacc1, atacc2); alter table atacc2 add constraint foo check (test2>0); -- fail and then succeed on atacc2 insert into atacc2 (test2) values (-3); -ERROR: ExecAppend: rejected due to CHECK constraint foo +ERROR: new row for relation "atacc2" violates check constraint "foo" insert into atacc2 (test2) values (3); -- fail and then succeed on atacc3 insert into atacc3 (test2) values (-3); -ERROR: ExecAppend: rejected due to CHECK constraint foo +ERROR: new row for relation "atacc3" violates check constraint "foo" insert into atacc3 (test2) values (3); drop table atacc3; drop table atacc2; drop table atacc1; +-- same things with one created with INHERIT +create table atacc1 (test int); +create table atacc2 (test2 int); +create table atacc3 (test3 int) inherits (atacc1, atacc2); +alter table atacc3 no inherit atacc2; +-- fail +alter table atacc3 no inherit atacc2; +ERROR: relation "atacc2" is not a parent of relation "atacc3" +-- make sure it really isn't a child +insert into atacc3 (test2) values (3); +select test2 from atacc2; + test2 +------- +(0 rows) + +-- fail due to missing constraint +alter table atacc2 add constraint foo check (test2>0); +alter table atacc3 inherit atacc2; +ERROR: child table is missing constraint "foo" +-- fail due to missing column +alter table atacc3 rename test2 to testx; +alter table atacc3 inherit atacc2; +ERROR: child table is missing column "test2" +-- fail due to mismatched data type +alter table atacc3 add test2 bool; +alter table atacc3 add inherit atacc2; +alter table atacc3 drop test2; +-- succeed +alter table atacc3 add test2 int; +update atacc3 set test2 = 4 where test2 is null; +alter table atacc3 add constraint foo check (test2>0); +alter table atacc3 inherit atacc2; +-- fail due to duplicates and circular inheritance +alter table atacc3 inherit atacc2; +ERROR: relation "atacc2" would be inherited from more than once +alter table atacc2 inherit atacc3; +ERROR: circular inheritance not allowed +DETAIL: "atacc3" is already a child of "atacc2". +alter table atacc2 inherit atacc2; +ERROR: circular inheritance not allowed +DETAIL: "atacc2" is already a child of "atacc2". +-- test that we really are a child now (should see 4 not 3 and cascade should go through) +select test2 from atacc2; + test2 +------- + 4 +(1 row) + +drop table atacc2 cascade; +NOTICE: drop cascades to table atacc3 +NOTICE: drop cascades to constraint foo on table atacc3 +drop table atacc1; -- let's try only to add only to the parent create table atacc1 (test int); create table atacc2 (test2 int); @@ -438,7 +385,7 @@ create table atacc3 (test3 int) inherits (atacc1, atacc2); alter table only atacc2 add constraint foo check (test2>0); -- fail and then succeed on atacc2 insert into atacc2 (test2) values (-3); -ERROR: ExecAppend: rejected due to CHECK constraint foo +ERROR: new row for relation "atacc2" violates check constraint "foo" insert into atacc2 (test2) values (3); -- both succeed on atacc3 insert into atacc3 (test2) values (-3); @@ -447,20 +394,20 @@ drop table atacc3; drop table atacc2; drop table atacc1; -- test unique constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" -- insert first value insert into atacc1 (test) values (2); -- should fail insert into atacc1 (test) values (2); -ERROR: Cannot insert a duplicate key into unique index atacc_test1 +ERROR: duplicate key value violates unique constraint "atacc_test1" -- should succeed insert into atacc1 (test) values (4); -- try adding a unique oid constraint alter table atacc1 add constraint atacc_oid1 unique(oid); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_oid1' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1" drop table atacc1; -- let's do one where the unique constraint fails when added create table atacc1 ( test int ); @@ -469,27 +416,28 @@ insert into atacc1 (test) values (2); insert into atacc1 (test) values (2); -- add a unique constraint (fails) alter table atacc1 add constraint atacc_test1 unique (test); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for table 'atacc1' -ERROR: Cannot create unique index. Table contains non-unique values +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" +ERROR: could not create unique index +DETAIL: Table contains duplicated values. insert into atacc1 (test) values (3); drop table atacc1; --- let's do one where the unique contsraint fails +-- let's do one where the unique constraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) alter table atacc1 add constraint atacc_test1 unique (test1); -ERROR: ALTER TABLE: column "test1" named in key does not exist +ERROR: column "test1" named in key does not exist drop table atacc1; -- something a little more complicated create table atacc1 ( test int, test2 int); -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test, test2); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" -- insert initial value insert into atacc1 (test,test2) values (4,4); -- should fail insert into atacc1 (test,test2) values (4,4); -ERROR: Cannot insert a duplicate key into unique index atacc_test1 +ERROR: duplicate key value violates unique constraint "atacc_test1" -- should all succeed insert into atacc1 (test,test2) values (4,5); insert into atacc1 (test,test2) values (5,4); @@ -497,11 +445,1023 @@ insert into atacc1 (test,test2) values (5,5); drop table atacc1; -- lets do some naming tests create table atacc1 (test int, test2 int, unique(test)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index 'atacc1_test_key' for table 'atacc1' +NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1" alter table atacc1 add unique (test2); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc1_test2_key' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1" -- should fail for @@ second one @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); -ERROR: Cannot insert a duplicate key into unique index atacc1_test_key +ERROR: duplicate key value violates unique constraint "atacc1_test_key" +drop table atacc1; +-- test primary key constraint adding +create table atacc1 ( test int ) with oids; +-- add a primary key constraint +alter table atacc1 add constraint atacc_test1 primary key (test); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +-- insert first value +insert into atacc1 (test) values (2); +-- should fail +insert into atacc1 (test) values (2); +ERROR: duplicate key value violates unique constraint "atacc_test1" +-- should succeed +insert into atacc1 (test) values (4); +-- inserting NULL should fail +insert into atacc1 (test) values(NULL); +ERROR: null value in column "test" violates not-null constraint +-- try adding a second primary key (should fail) +alter table atacc1 add constraint atacc_oid1 primary key(oid); +ERROR: multiple primary keys for table "atacc1" are not allowed +-- drop first primary key constraint +alter table atacc1 drop constraint atacc_test1 restrict; +-- try adding a primary key on oid (should succeed) +alter table atacc1 add constraint atacc_oid1 primary key(oid); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1" +drop table atacc1; +-- let's do one where the primary key constraint fails when added +create table atacc1 ( test int ); +-- insert soon to be failing rows +insert into atacc1 (test) values (2); +insert into atacc1 (test) values (2); +-- add a primary key (fails) +alter table atacc1 add constraint atacc_test1 primary key (test); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +ERROR: could not create unique index +DETAIL: Table contains duplicated values. +insert into atacc1 (test) values (3); +drop table atacc1; +-- let's do another one where the primary key constraint fails when added +create table atacc1 ( test int ); +-- insert soon to be failing row +insert into atacc1 (test) values (NULL); +-- add a primary key (fails) +alter table atacc1 add constraint atacc_test1 primary key (test); +ERROR: column "test" contains null values +insert into atacc1 (test) values (3); +drop table atacc1; +-- let's do one where the primary key constraint fails +-- because the column doesn't exist +create table atacc1 ( test int ); +-- add a primary key constraint (fails) +alter table atacc1 add constraint atacc_test1 primary key (test1); +ERROR: column "test1" named in key does not exist +drop table atacc1; +-- something a little more complicated +create table atacc1 ( test int, test2 int); +-- add a primary key constraint +alter table atacc1 add constraint atacc_test1 primary key (test, test2); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +-- try adding a second primary key - should fail +alter table atacc1 add constraint atacc_test2 primary key (test); +ERROR: multiple primary keys for table "atacc1" are not allowed +-- insert initial value +insert into atacc1 (test,test2) values (4,4); +-- should fail +insert into atacc1 (test,test2) values (4,4); +ERROR: duplicate key value violates unique constraint "atacc_test1" +insert into atacc1 (test,test2) values (NULL,3); +ERROR: null value in column "test" violates not-null constraint +insert into atacc1 (test,test2) values (3, NULL); +ERROR: null value in column "test2" violates not-null constraint +insert into atacc1 (test,test2) values (NULL,NULL); +ERROR: null value in column "test" violates not-null constraint +-- should all succeed +insert into atacc1 (test,test2) values (4,5); +insert into atacc1 (test,test2) values (5,4); +insert into atacc1 (test,test2) values (5,5); +drop table atacc1; +-- lets do some naming tests +create table atacc1 (test int, test2 int, primary key(test)); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +-- only first should succeed +insert into atacc1 (test2, test) values (3, 3); +insert into atacc1 (test2, test) values (2, 3); +ERROR: duplicate key value violates unique constraint "atacc1_pkey" +insert into atacc1 (test2, test) values (1, NULL); +ERROR: null value in column "test" violates not-null constraint +drop table atacc1; +-- alter table / alter column [set/drop] not null tests +-- try altering system catalogs, should fail +alter table pg_class alter column relname drop not null; +ERROR: permission denied: "pg_class" is a system catalog +alter table pg_class alter relname set not null; +ERROR: permission denied: "pg_class" is a system catalog +-- try altering non-existent table, should fail +alter table non_existent alter column bar set not null; +ERROR: relation "non_existent" does not exist +alter table non_existent alter column bar drop not null; +ERROR: relation "non_existent" does not exist +-- test setting columns to null and not null and vice versa +-- test checking for null values and primary key +create table atacc1 (test int not null) with oids; +alter table atacc1 add constraint "atacc1_pkey" primary key (test); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +alter table atacc1 alter column test drop not null; +ERROR: column "test" is in a primary key +alter table atacc1 drop constraint "atacc1_pkey"; +alter table atacc1 alter column test drop not null; +insert into atacc1 values (null); +alter table atacc1 alter test set not null; +ERROR: column "test" contains null values +delete from atacc1; +alter table atacc1 alter test set not null; +-- try altering a non-existent column, should fail +alter table atacc1 alter bar set not null; +ERROR: column "bar" of relation "atacc1" does not exist +alter table atacc1 alter bar drop not null; +ERROR: column "bar" of relation "atacc1" does not exist +-- try altering the oid column, should fail +alter table atacc1 alter oid set not null; +ERROR: cannot alter system column "oid" +alter table atacc1 alter oid drop not null; +ERROR: cannot alter system column "oid" +-- try creating a view and altering that, should fail +create view myview as select * from atacc1; +alter table myview alter column test drop not null; +ERROR: "myview" is not a table +alter table myview alter column test set not null; +ERROR: "myview" is not a table +drop view myview; +drop table atacc1; +-- test inheritance +create table parent (a int); +create table child (b varchar(255)) inherits (parent); +alter table parent alter a set not null; +insert into parent values (NULL); +ERROR: null value in column "a" violates not-null constraint +insert into child (a, b) values (NULL, 'foo'); +ERROR: null value in column "a" violates not-null constraint +alter table parent alter a drop not null; +insert into parent values (NULL); +insert into child (a, b) values (NULL, 'foo'); +alter table only parent alter a set not null; +ERROR: column "a" contains null values +alter table child alter a set not null; +ERROR: column "a" contains null values +delete from parent; +alter table only parent alter a set not null; +insert into parent values (NULL); +ERROR: null value in column "a" violates not-null constraint +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +ERROR: null value in column "a" violates not-null constraint +delete from child; +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +ERROR: null value in column "a" violates not-null constraint +drop table child; +drop table parent; +-- test setting and removing default values +create table def_test ( + c1 int4 default 5, + c2 text default 'initial_default' +); +insert into def_test default values; +alter table def_test alter column c1 drop default; +insert into def_test default values; +alter table def_test alter column c2 drop default; +insert into def_test default values; +alter table def_test alter column c1 set default 10; +alter table def_test alter column c2 set default 'new_default'; +insert into def_test default values; +select * from def_test; + c1 | c2 +----+----------------- + 5 | initial_default + | initial_default + | + 10 | new_default +(4 rows) + +-- set defaults to an incorrect type: this should fail +alter table def_test alter column c1 set default 'wrong_datatype'; +ERROR: invalid input syntax for integer: "wrong_datatype" +alter table def_test alter column c2 set default 20; +-- set defaults on a non-existent column: this should fail +alter table def_test alter column c3 set default 30; +ERROR: column "c3" of relation "def_test" does not exist +-- set defaults on views: we need to create a view, add a rule +-- to allow insertions into it, and then alter the view to add +-- a default +create view def_view_test as select * from def_test; +create rule def_view_test_ins as + on insert to def_view_test + do instead insert into def_test select new.*; +insert into def_view_test default values; +alter table def_view_test alter column c1 set default 45; +insert into def_view_test default values; +alter table def_view_test alter column c2 set default 'view_default'; +insert into def_view_test default values; +select * from def_view_test; + c1 | c2 +----+----------------- + 5 | initial_default + | initial_default + | + 10 | new_default + | + 45 | + 45 | view_default +(7 rows) + +drop rule def_view_test_ins on def_view_test; +drop view def_view_test; +drop table def_test; +-- alter table / drop column tests +-- try altering system catalogs, should fail +alter table pg_class drop column relname; +ERROR: permission denied: "pg_class" is a system catalog +-- try altering non-existent table, should fail +alter table nosuchtable drop column bar; +ERROR: relation "nosuchtable" does not exist +-- test dropping columns +create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; +insert into atacc1 values (1, 2, 3, 4); +alter table atacc1 drop a; +alter table atacc1 drop a; +ERROR: column "a" of relation "atacc1" does not exist +-- SELECTs +select * from atacc1; + b | c | d +---+---+--- + 2 | 3 | 4 +(1 row) + +select * from atacc1 order by a; +ERROR: column "a" does not exist +LINE 1: select * from atacc1 order by a; + ^ +select * from atacc1 order by "........pg.dropped.1........"; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select * from atacc1 order by "........pg.dropped.1........"... + ^ +select * from atacc1 group by a; +ERROR: column "a" does not exist +LINE 1: select * from atacc1 group by a; + ^ +select * from atacc1 group by "........pg.dropped.1........"; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select * from atacc1 group by "........pg.dropped.1........"... + ^ +select atacc1.* from atacc1; + b | c | d +---+---+--- + 2 | 3 | 4 +(1 row) + +select a from atacc1; +ERROR: column "a" does not exist +LINE 1: select a from atacc1; + ^ +select atacc1.a from atacc1; +ERROR: column atacc1.a does not exist +LINE 1: select atacc1.a from atacc1; + ^ +select b,c,d from atacc1; + b | c | d +---+---+--- + 2 | 3 | 4 +(1 row) + +select a,b,c,d from atacc1; +ERROR: column "a" does not exist +LINE 1: select a,b,c,d from atacc1; + ^ +select * from atacc1 where a = 1; +ERROR: column "a" does not exist +LINE 1: select * from atacc1 where a = 1; + ^ +select "........pg.dropped.1........" from atacc1; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select "........pg.dropped.1........" from atacc1; + ^ +select atacc1."........pg.dropped.1........" from atacc1; +ERROR: column atacc1.........pg.dropped.1........ does not exist +LINE 1: select atacc1."........pg.dropped.1........" from atacc1; + ^ +select "........pg.dropped.1........",b,c,d from atacc1; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select "........pg.dropped.1........",b,c,d from atacc1; + ^ +select * from atacc1 where "........pg.dropped.1........" = 1; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select * from atacc1 where "........pg.dropped.1........" = ... + ^ +-- UPDATEs +update atacc1 set a = 3; +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: update atacc1 set a = 3; + ^ +update atacc1 set b = 2 where a = 3; +ERROR: column "a" does not exist +LINE 1: update atacc1 set b = 2 where a = 3; + ^ +update atacc1 set "........pg.dropped.1........" = 3; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: update atacc1 set "........pg.dropped.1........" = 3; + ^ +update atacc1 set b = 2 where "........pg.dropped.1........" = 3; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"... + ^ +-- INSERTs +insert into atacc1 values (10, 11, 12, 13); +ERROR: INSERT has more expressions than target columns +insert into atacc1 values (default, 11, 12, 13); +ERROR: INSERT has more expressions than target columns +insert into atacc1 values (11, 12, 13); +insert into atacc1 (a) values (10); +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a) values (10); + ^ +insert into atacc1 (a) values (default); +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a) values (default); + ^ +insert into atacc1 (a,b,c,d) values (10,11,12,13); +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13); + ^ +insert into atacc1 (a,b,c,d) values (default,11,12,13); +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13); + ^ +insert into atacc1 (b,c,d) values (11,12,13); +insert into atacc1 ("........pg.dropped.1........") values (10); +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........") values (... + ^ +insert into atacc1 ("........pg.dropped.1........") values (default); +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........") values (... + ^ +insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13); +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va... + ^ +insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13); +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va... + ^ +-- DELETEs +delete from atacc1 where a = 3; +ERROR: column "a" does not exist +LINE 1: delete from atacc1 where a = 3; + ^ +delete from atacc1 where "........pg.dropped.1........" = 3; +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3; + ^ +delete from atacc1; +-- try dropping a non-existent column, should fail +alter table atacc1 drop bar; +ERROR: column "bar" of relation "atacc1" does not exist +-- try dropping the oid column, should succeed +alter table atacc1 drop oid; +-- try dropping the xmin column, should fail +alter table atacc1 drop xmin; +ERROR: cannot drop system column "xmin" +-- try creating a view and altering that, should fail +create view myview as select * from atacc1; +select * from myview; + b | c | d +---+---+--- +(0 rows) + +alter table myview drop d; +ERROR: "myview" is not a table +drop view myview; +-- test some commands to make sure they fail on the dropped column +analyze atacc1(a); +ERROR: column "a" of relation "atacc1" does not exist +analyze atacc1("........pg.dropped.1........"); +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +vacuum analyze atacc1(a); +ERROR: column "a" of relation "atacc1" does not exist +vacuum analyze atacc1("........pg.dropped.1........"); +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +comment on column atacc1.a is 'testing'; +ERROR: column "a" of relation "atacc1" does not exist +comment on column atacc1."........pg.dropped.1........" is 'testing'; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 alter a set storage plain; +ERROR: column "a" of relation "atacc1" does not exist +alter table atacc1 alter "........pg.dropped.1........" set storage plain; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 alter a set statistics 0; +ERROR: column "a" of relation "atacc1" does not exist +alter table atacc1 alter "........pg.dropped.1........" set statistics 0; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 alter a set default 3; +ERROR: column "a" of relation "atacc1" does not exist +alter table atacc1 alter "........pg.dropped.1........" set default 3; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 alter a drop default; +ERROR: column "a" of relation "atacc1" does not exist +alter table atacc1 alter "........pg.dropped.1........" drop default; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 alter a set not null; +ERROR: column "a" of relation "atacc1" does not exist +alter table atacc1 alter "........pg.dropped.1........" set not null; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 alter a drop not null; +ERROR: column "a" of relation "atacc1" does not exist +alter table atacc1 alter "........pg.dropped.1........" drop not null; +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +alter table atacc1 rename a to x; +ERROR: column "a" does not exist +alter table atacc1 rename "........pg.dropped.1........" to x; +ERROR: column "........pg.dropped.1........" does not exist +alter table atacc1 add primary key(a); +ERROR: column "a" named in key does not exist +alter table atacc1 add primary key("........pg.dropped.1........"); +ERROR: column "........pg.dropped.1........" named in key does not exist +alter table atacc1 add unique(a); +ERROR: column "a" named in key does not exist +alter table atacc1 add unique("........pg.dropped.1........"); +ERROR: column "........pg.dropped.1........" named in key does not exist +alter table atacc1 add check (a > 3); +ERROR: column "a" does not exist +alter table atacc1 add check ("........pg.dropped.1........" > 3); +ERROR: column "........pg.dropped.1........" does not exist +create table atacc2 (id int4 unique); +NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2" +alter table atacc1 add foreign key (a) references atacc2(id); +ERROR: column "a" referenced in foreign key constraint does not exist +alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id); +ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist +alter table atacc2 add foreign key (id) references atacc1(a); +ERROR: column "a" referenced in foreign key constraint does not exist +alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........"); +ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist +drop table atacc2; +create index "testing_idx" on atacc1(a); +ERROR: column "a" does not exist +create index "testing_idx" on atacc1("........pg.dropped.1........"); +ERROR: column "........pg.dropped.1........" does not exist +-- test create as and select into +insert into atacc1 values (21, 22, 23); +create table test1 as select * from atacc1; +select * from test1; + b | c | d +----+----+---- + 21 | 22 | 23 +(1 row) + +drop table test1; +select * into test2 from atacc1; +select * from test2; + b | c | d +----+----+---- + 21 | 22 | 23 +(1 row) + +drop table test2; +-- try dropping all columns +alter table atacc1 drop c; +alter table atacc1 drop d; +alter table atacc1 drop b; +select * from atacc1; +-- +(1 row) + drop table atacc1; +-- test inheritance +create table parent (a int, b int, c int); +insert into parent values (1, 2, 3); +alter table parent drop a; +create table child (d varchar(255)) inherits (parent); +insert into child values (12, 13, 'testing'); +select * from parent; + b | c +----+---- + 2 | 3 + 12 | 13 +(2 rows) + +select * from child; + b | c | d +----+----+--------- + 12 | 13 | testing +(1 row) + +alter table parent drop c; +select * from parent; + b +---- + 2 + 12 +(2 rows) + +select * from child; + b | d +----+--------- + 12 | testing +(1 row) + +drop table child; +drop table parent; +-- test copy in/out +create table test (a int4, b int4, c int4); +insert into test values (1,2,3); +alter table test drop a; +copy test to stdout; +2 3 +copy test(a) to stdout; +ERROR: column "a" of relation "test" does not exist +copy test("........pg.dropped.1........") to stdout; +ERROR: column "........pg.dropped.1........" of relation "test" does not exist +copy test from stdin; +ERROR: extra data after last expected column +CONTEXT: COPY test, line 1: "10 11 12" +select * from test; + b | c +---+--- + 2 | 3 +(1 row) + +copy test from stdin; +select * from test; + b | c +----+---- + 2 | 3 + 21 | 22 +(2 rows) + +copy test(a) from stdin; +ERROR: column "a" of relation "test" does not exist +copy test("........pg.dropped.1........") from stdin; +ERROR: column "........pg.dropped.1........" of relation "test" does not exist +copy test(b,c) from stdin; +select * from test; + b | c +----+---- + 2 | 3 + 21 | 22 + 31 | 32 +(3 rows) + +drop table test; +-- test inheritance +create table dropColumn (a int, b int, e int); +create table dropColumnChild (c int) inherits (dropColumn); +create table dropColumnAnother (d int) inherits (dropColumnChild); +-- these two should fail +alter table dropColumnchild drop column a; +ERROR: cannot drop inherited column "a" +alter table only dropColumnChild drop column b; +ERROR: cannot drop inherited column "b" +-- these three should work +alter table only dropColumn drop column e; +alter table dropColumnChild drop column c; +alter table dropColumn drop column a; +create table renameColumn (a int); +create table renameColumnChild (b int) inherits (renameColumn); +create table renameColumnAnother (c int) inherits (renameColumnChild); +-- these three should fail +alter table renameColumnChild rename column a to d; +ERROR: cannot rename inherited column "a" +alter table only renameColumnChild rename column a to d; +ERROR: inherited column "a" must be renamed in child tables too +alter table only renameColumn rename column a to d; +ERROR: inherited column "a" must be renamed in child tables too +-- these should work +alter table renameColumn rename column a to d; +alter table renameColumnChild rename column b to a; +-- this should work +alter table renameColumn add column w int; +-- this should fail +alter table only renameColumn add column x int; +ERROR: column must be added to child tables too +-- Test corner cases in dropping of inherited columns +create table p1 (f1 int, f2 int); +create table c1 (f1 int not null) inherits(p1); +NOTICE: merging column "f1" with inherited definition +-- should be rejected since c1.f1 is inherited +alter table c1 drop column f1; +ERROR: cannot drop inherited column "f1" +-- should work +alter table p1 drop column f1; +-- c1.f1 is still there, but no longer inherited +select f1 from c1; + f1 +---- +(0 rows) + +alter table c1 drop column f1; +select f1 from c1; +ERROR: column "f1" does not exist +LINE 1: select f1 from c1; + ^ +drop table p1 cascade; +NOTICE: drop cascades to table c1 +create table p1 (f1 int, f2 int); +create table c1 () inherits(p1); +-- should be rejected since c1.f1 is inherited +alter table c1 drop column f1; +ERROR: cannot drop inherited column "f1" +alter table p1 drop column f1; +-- c1.f1 is dropped now, since there is no local definition for it +select f1 from c1; +ERROR: column "f1" does not exist +LINE 1: select f1 from c1; + ^ +drop table p1 cascade; +NOTICE: drop cascades to table c1 +create table p1 (f1 int, f2 int); +create table c1 () inherits(p1); +-- should be rejected since c1.f1 is inherited +alter table c1 drop column f1; +ERROR: cannot drop inherited column "f1" +alter table only p1 drop column f1; +-- c1.f1 is NOT dropped, but must now be considered non-inherited +alter table c1 drop column f1; +drop table p1 cascade; +NOTICE: drop cascades to table c1 +create table p1 (f1 int, f2 int); +create table c1 (f1 int not null) inherits(p1); +NOTICE: merging column "f1" with inherited definition +-- should be rejected since c1.f1 is inherited +alter table c1 drop column f1; +ERROR: cannot drop inherited column "f1" +alter table only p1 drop column f1; +-- c1.f1 is still there, but no longer inherited +alter table c1 drop column f1; +drop table p1 cascade; +NOTICE: drop cascades to table c1 +create table p1(id int, name text); +create table p2(id2 int, name text, height int); +create table c1(age int) inherits(p1,p2); +NOTICE: merging multiple inherited definitions of column "name" +create table gc1() inherits (c1); +select relname, attname, attinhcount, attislocal +from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) +where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped +order by relname, attnum; + relname | attname | attinhcount | attislocal +---------+---------+-------------+------------ + c1 | id | 1 | f + c1 | name | 2 | f + c1 | id2 | 1 | f + c1 | height | 1 | f + c1 | age | 0 | t + gc1 | id | 1 | f + gc1 | name | 1 | f + gc1 | id2 | 1 | f + gc1 | height | 1 | f + gc1 | age | 1 | f + p1 | id | 0 | t + p1 | name | 0 | t + p2 | id2 | 0 | t + p2 | name | 0 | t + p2 | height | 0 | t +(15 rows) + +-- should work +alter table only p1 drop column name; +-- should work. Now c1.name is local and inhcount is 0. +alter table p2 drop column name; +-- should be rejected since its inherited +alter table gc1 drop column name; +ERROR: cannot drop inherited column "name" +-- should work, and drop gc1.name along +alter table c1 drop column name; +-- should fail: column does not exist +alter table gc1 drop column name; +ERROR: column "name" of relation "gc1" does not exist +-- should work and drop the attribute in all tables +alter table p2 drop column height; +select relname, attname, attinhcount, attislocal +from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) +where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped +order by relname, attnum; + relname | attname | attinhcount | attislocal +---------+---------+-------------+------------ + c1 | id | 1 | f + c1 | id2 | 1 | f + c1 | age | 0 | t + gc1 | id | 1 | f + gc1 | id2 | 1 | f + gc1 | age | 1 | f + p1 | id | 0 | t + p2 | id2 | 0 | t +(8 rows) + +drop table p1, p2 cascade; +NOTICE: drop cascades to table c1 +NOTICE: drop cascades to table gc1 +-- +-- Test the ALTER TABLE WITHOUT OIDS command +-- +create table altstartwith (col integer) with oids; +insert into altstartwith values (1); +select oid > 0, * from altstartwith; + ?column? | col +----------+----- + t | 1 +(1 row) + +alter table altstartwith set without oids; +select oid > 0, * from altstartwith; -- fails +ERROR: column "oid" does not exist +LINE 1: select oid > 0, * from altstartwith; + ^ +select * from altstartwith; + col +----- + 1 +(1 row) + +-- Run inheritance tests +create table altwithoid (col integer) with oids; +-- Inherits parents oid column +create table altinhoid () inherits (altwithoid) without oids; +insert into altinhoid values (1); +select oid > 0, * from altwithoid; + ?column? | col +----------+----- + t | 1 +(1 row) + +select oid > 0, * from altinhoid; + ?column? | col +----------+----- + t | 1 +(1 row) + +alter table altwithoid set without oids; +alter table altinhoid set without oids; +select oid > 0, * from altwithoid; -- fails +ERROR: column "oid" does not exist +LINE 1: select oid > 0, * from altwithoid; + ^ +select oid > 0, * from altinhoid; -- fails +ERROR: column "oid" does not exist +LINE 1: select oid > 0, * from altinhoid; + ^ +select * from altwithoid; + col +----- + 1 +(1 row) + +select * from altinhoid; + col +----- + 1 +(1 row) + +-- test renumbering of child-table columns in inherited operations +create table p1 (f1 int); +create table c1 (f2 text, f3 int) inherits (p1); +alter table p1 add column a1 int check (a1 > 0); +alter table p1 add column f2 text; +NOTICE: merging definition of column "f2" for child "c1" +insert into p1 values (1,2,'abc'); +insert into c1 values(11,'xyz',33,0); -- should fail +ERROR: new row for relation "c1" violates check constraint "c1_a1_check" +insert into c1 values(11,'xyz',33,22); +select * from p1; + f1 | a1 | f2 +----+----+----- + 1 | 2 | abc + 11 | 22 | xyz +(2 rows) + +update p1 set a1 = a1 + 1, f2 = upper(f2); +select * from p1; + f1 | a1 | f2 +----+----+----- + 1 | 3 | ABC + 11 | 23 | XYZ +(2 rows) + +drop table p1 cascade; +NOTICE: drop cascades to table c1 +NOTICE: drop cascades to constraint c1_a1_check on table c1 +-- test that operations with a dropped column do not try to reference +-- its datatype +create domain mytype as text; +create temp table foo (f1 text, f2 mytype, f3 text); +insert into foo values('aa','bb','cc'); +select * from foo; + f1 | f2 | f3 +----+----+---- + aa | bb | cc +(1 row) + +drop domain mytype cascade; +NOTICE: drop cascades to table foo column f2 +select * from foo; + f1 | f3 +----+---- + aa | cc +(1 row) + +insert into foo values('qq','rr'); +select * from foo; + f1 | f3 +----+---- + aa | cc + qq | rr +(2 rows) + +update foo set f3 = 'zz'; +select * from foo; + f1 | f3 +----+---- + aa | zz + qq | zz +(2 rows) + +select f3,max(f1) from foo group by f3; + f3 | max +----+----- + zz | qq +(1 row) + +-- Simple tests for alter table column type +alter table foo alter f1 TYPE integer; -- fails +ERROR: column "f1" cannot be cast to type "pg_catalog.int4" +alter table foo alter f1 TYPE varchar(10); +create table anothertab (atcol1 serial8, atcol2 boolean, + constraint anothertab_chk check (atcol1 <= 3)); +NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1" +insert into anothertab (atcol1, atcol2) values (default, true); +insert into anothertab (atcol1, atcol2) values (default, false); +select * from anothertab; + atcol1 | atcol2 +--------+-------- + 1 | t + 2 | f +(2 rows) + +alter table anothertab alter column atcol1 type boolean; -- fails +ERROR: column "atcol1" cannot be cast to type "pg_catalog.bool" +alter table anothertab alter column atcol1 type integer; +select * from anothertab; + atcol1 | atcol2 +--------+-------- + 1 | t + 2 | f +(2 rows) + +insert into anothertab (atcol1, atcol2) values (45, null); -- fails +ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk" +insert into anothertab (atcol1, atcol2) values (default, null); +select * from anothertab; + atcol1 | atcol2 +--------+-------- + 1 | t + 2 | f + 3 | +(3 rows) + +alter table anothertab alter column atcol2 type text + using case when atcol2 is true then 'IT WAS TRUE' + when atcol2 is false then 'IT WAS FALSE' + else 'IT WAS NULL!' end; +select * from anothertab; + atcol1 | atcol2 +--------+-------------- + 1 | IT WAS TRUE + 2 | IT WAS FALSE + 3 | IT WAS NULL! +(3 rows) + +alter table anothertab alter column atcol1 type boolean + using case when atcol1 % 2 = 0 then true else false end; -- fails +ERROR: default for column "atcol1" cannot be cast to type "pg_catalog.bool" +alter table anothertab alter column atcol1 drop default; +alter table anothertab alter column atcol1 type boolean + using case when atcol1 % 2 = 0 then true else false end; -- fails +ERROR: operator does not exist: boolean <= integer +HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. +alter table anothertab drop constraint anothertab_chk; +alter table anothertab alter column atcol1 type boolean + using case when atcol1 % 2 = 0 then true else false end; +select * from anothertab; + atcol1 | atcol2 +--------+-------------- + f | IT WAS TRUE + t | IT WAS FALSE + f | IT WAS NULL! +(3 rows) + +drop table anothertab; +create table another (f1 int, f2 text); +insert into another values(1, 'one'); +insert into another values(2, 'two'); +insert into another values(3, 'three'); +select * from another; + f1 | f2 +----+------- + 1 | one + 2 | two + 3 | three +(3 rows) + +alter table another + alter f1 type text using f2 || ' more', + alter f2 type bigint using f1 * 10; +select * from another; + f1 | f2 +------------+---- + one more | 10 + two more | 20 + three more | 30 +(3 rows) + +drop table another; +-- +-- alter function +-- +create function test_strict(text) returns text as + 'select coalesce($1, ''got passed a null'');' + language sql returns null on null input; +select test_strict(NULL); + test_strict +------------- + +(1 row) + +alter function test_strict(text) called on null input; +select test_strict(NULL); + test_strict +------------------- + got passed a null +(1 row) + +create function non_strict(text) returns text as + 'select coalesce($1, ''got passed a null'');' + language sql called on null input; +select non_strict(NULL); + non_strict +------------------- + got passed a null +(1 row) + +alter function non_strict(text) returns null on null input; +select non_strict(NULL); + non_strict +------------ + +(1 row) + +-- +-- alter object set schema +-- +create schema alter1; +create schema alter2; +create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); +NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" +create view alter1.v1 as select * from alter1.t1; +create function alter1.plus1(int) returns int as 'select $1+1' language sql; +create domain alter1.posint integer check (value > 0); +create type alter1.ctype as (f1 int, f2 text); +insert into alter1.t1(f2) values(11); +insert into alter1.t1(f2) values(12); +alter table alter1.t1 set schema alter2; +alter table alter1.v1 set schema alter2; +alter function alter1.plus1(int) set schema alter2; +alter domain alter1.posint set schema alter2; +alter type alter1.ctype set schema alter2; +-- this should succeed because nothing is left in alter1 +drop schema alter1; +insert into alter2.t1(f2) values(13); +insert into alter2.t1(f2) values(14); +select * from alter2.t1; + f1 | f2 +----+---- + 1 | 11 + 2 | 12 + 3 | 13 + 4 | 14 +(4 rows) + +select * from alter2.v1; + f1 | f2 +----+---- + 1 | 11 + 2 | 12 + 3 | 13 + 4 | 14 +(4 rows) + +select alter2.plus1(41); + plus1 +------- + 42 +(1 row) + +-- clean up +drop schema alter2 cascade; +NOTICE: drop cascades to type alter2.ctype +NOTICE: drop cascades to type alter2.posint +NOTICE: drop cascades to function alter2.plus1(integer) +NOTICE: drop cascades to view alter2.v1 +NOTICE: drop cascades to rule _RETURN on view alter2.v1 +NOTICE: drop cascades to sequence alter2.t1_f1_seq +NOTICE: drop cascades to default for table alter2.t1 column f1 +NOTICE: drop cascades to table alter2.t1 +NOTICE: drop cascades to constraint t1_f2_check on table alter2.t1