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[];
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[];
4
(5 rows)
+-- ALTER TABLE ... RENAME on non-table relations
+-- renaming indexes (FIXME: this should probably test the index's functionality)
+ALTER TABLE onek_unique1 RENAME TO tmp_onek_unique1;
+ALTER TABLE 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;
+-- 5 values, sorted
+SELECT unique1 FROM tenk1 WHERE unique1 < 5;
+ unique1
+---------
+ 0
+ 1
+ 2
+ 3
+ 4
+(5 rows)
+
+DROP VIEW tmp_view_new;
+-- renaming sequences
+CREATE SEQUENCE foo_seq;
+ALTER TABLE foo_seq RENAME TO foo_seq_new;
+SELECT * FROM foo_seq_new;
+ sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
+---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
+ foo_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
+(1 row)
+
+DROP SEQUENCE foo_seq_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);
-INFO: 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));
-INFO: 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);
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;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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
-- 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;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: UNIQUE constraint matching given keys for referenced table "tmp2" not found
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: ALTER TABLE: 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;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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
-- 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;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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
DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
-INFO: DROP TABLE implicitly drops referential integrity trigger from table "tmp2"
-INFO: 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);
-INFO: 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'
+CREATE TEMP TABLE FKTABLE (ftest1 inet);
+-- This next should fail, because inet=int does not exist
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- 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);
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: Unable to identify an operator '=' for types 'inet' 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
DROP TABLE FKTABLE;
CREATE TEMP TABLE FKTABLE (ftest1 varchar);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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);
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-DROP TABLE pktable;
-INFO: DROP TABLE implicitly drops referential integrity trigger from table "fktable"
-INFO: DROP TABLE implicitly drops referential integrity trigger from table "fktable"
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+DROP TABLE pktable cascade;
+NOTICE: Drop cascades to constraint $2 on table fktable
+NOTICE: Drop cascades to constraint $1 on table fktable
DROP TABLE fktable;
-CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 text,
+CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
PRIMARY KEY(ptest1, ptest2));
-INFO: 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;
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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...
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);
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+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
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);
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: Unable to identify an operator '=' for types 'integer' and 'inet'
You will have to retype this query using an explicit cast
-- As does this...
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
references pktable(ptest1, ptest2);
-INFO: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
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: ExecInsert: rejected due to CHECK constraint "atacc_test1" on "atacc1"
-- should succeed
insert into atacc1 (test) values (4);
drop table atacc1;
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: Attribute "test1" not found
drop table atacc1;
-- something a little more complicated
create table atacc1 ( test int, test2 int, test3 int);
alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
-- should fail
insert into atacc1 (test,test2,test3) values (4,4,2);
-ERROR: ExecAppend: rejected due to CHECK constraint atacc_test1
+ERROR: ExecInsert: rejected due to CHECK constraint "atacc_test1" on "atacc1"
-- should succeed
insert into atacc1 (test,test2,test3) values (4,4,5);
drop table atacc1;
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: ExecInsert: rejected due to CHECK constraint "$1" on "atacc1"
drop table atacc1;
-- inheritance related tests
create table atacc1 (test int);
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: ExecInsert: rejected due to CHECK constraint "foo" on "atacc2"
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: ExecInsert: rejected due to CHECK constraint "foo" on "atacc3"
insert into atacc3 (test2) values (3);
drop table atacc3;
drop table 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: ExecInsert: rejected due to CHECK constraint "foo" on "atacc2"
insert into atacc2 (test2) values (3);
-- both succeed on atacc3
insert into atacc3 (test2) values (-3);
create table atacc1 ( test int );
-- add a unique constraint
alter table atacc1 add constraint atacc_test1 unique (test);
-INFO: 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 (4);
-- try adding a unique oid constraint
alter table atacc1 add constraint atacc_oid1 unique(oid);
-INFO: 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 );
insert into atacc1 (test) values (2);
-- add a unique constraint (fails)
alter table atacc1 add constraint atacc_test1 unique (test);
-INFO: 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'
ERROR: Cannot create unique index. Table contains non-unique 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)
create table atacc1 ( test int, test2 int);
-- add a unique constraint
alter table atacc1 add constraint atacc_test1 unique (test, test2);
-INFO: 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
drop table atacc1;
-- lets do some naming tests
create table atacc1 (test int, test2 int, unique(test));
-INFO: 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);
-INFO: 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
drop table atacc1;
+-- test primary key constraint adding
+create table atacc1 ( test int );
+-- 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: Cannot insert a duplicate key into unique index atacc_test1
+-- should succeed
+insert into atacc1 (test) values (4);
+-- inserting NULL should fail
+insert into atacc1 (test) values(NULL);
+ERROR: ExecInsert: Fail to add null value in not null attribute test
+-- try adding a second primary key (should fail)
+alter table atacc1 add constraint atacc_oid1 primary key(oid);
+ERROR: ALTER TABLE / PRIMARY KEY 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: Cannot create unique index. Table contains non-unique 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);
+NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1'
+ERROR: ALTER TABLE: Attribute "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: ALTER TABLE: 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: ALTER TABLE / PRIMARY KEY 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: Cannot insert a duplicate key into unique index atacc_test1
+insert into atacc1 (test,test2) values (NULL,3);
+ERROR: ExecInsert: Fail to add null value in not null attribute test
+insert into atacc1 (test,test2) values (3, NULL);
+ERROR: ExecInsert: Fail to add null value in not null attribute test2
+insert into atacc1 (test,test2) values (NULL,NULL);
+ERROR: ExecInsert: Fail to add null value in not null attribute test
+-- 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: Cannot insert a duplicate key into unique index atacc1_pkey
+insert into atacc1 (test2, test) values (1, NULL);
+ERROR: ExecInsert: Fail to add null value in not null attribute test
+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: ALTER TABLE: relation "pg_class" is a system catalog
+alter table pg_class alter relname set not null;
+ERROR: ALTER TABLE: relation "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);
+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: ALTER TABLE: Attribute "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: ALTER TABLE: Attribute "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: Relation "atacc1" has no column "bar"
+alter table atacc1 alter bar drop not null;
+ERROR: Relation "atacc1" has no column "bar"
+-- try altering the oid column, should fail
+alter table atacc1 alter oid set not null;
+ERROR: ALTER TABLE: Cannot alter system attribute "oid"
+alter table atacc1 alter oid drop not null;
+ERROR: ALTER TABLE: Cannot alter system attribute "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: ALTER TABLE: relation "myview" is not a table
+alter table myview alter column test set not null;
+ERROR: ALTER TABLE: relation "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: ExecInsert: Fail to add null value in not null attribute a
+insert into child (a, b) values (NULL, 'foo');
+ERROR: ExecInsert: Fail to add null value in not null attribute a
+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: ALTER TABLE: Attribute "a" contains NULL values
+alter table child alter a set not null;
+ERROR: ALTER TABLE: Attribute "a" contains NULL values
+delete from parent;
+alter table only parent alter a set not null;
+insert into parent values (NULL);
+ERROR: ExecInsert: Fail to add null value in not null attribute a
+alter table child alter a set not null;
+insert into child (a, b) values (NULL, 'foo');
+ERROR: ExecInsert: Fail to add null value in not null attribute a
+delete from child;
+alter table child alter a set not null;
+insert into child (a, b) values (NULL, 'foo');
+ERROR: ExecInsert: Fail to add null value in not null attribute a
+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: pg_atoi: error in "wrong_datatype": can't parse "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: Relation "def_test" has no column "c3"
+-- 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: ALTER TABLE: relation "pg_class" is a system catalog
+-- try altering non-existent table, should fail
+alter table foo drop column bar;
+ERROR: Relation "foo" does not exist
+-- test dropping columns
+create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
+insert into atacc1 values (1, 2, 3, 4);
+alter table atacc1 drop a;
+alter table atacc1 drop a;
+ERROR: Relation "atacc1" has no column "a"
+-- SELECTs
+select * from atacc1;
+ b | c | d
+---+---+---
+ 2 | 3 | 4
+(1 row)
+
+select * from atacc1 order by a;
+ERROR: Attribute "a" not found
+select * from atacc1 order by "........pg.dropped.1........";
+ERROR: Attribute "........pg.dropped.1........" not found
+select * from atacc1 group by a;
+ERROR: Attribute "a" not found
+select * from atacc1 group by "........pg.dropped.1........";
+ERROR: Attribute "........pg.dropped.1........" not found
+select atacc1.* from atacc1;
+ b | c | d
+---+---+---
+ 2 | 3 | 4
+(1 row)
+
+select a from atacc1;
+ERROR: Attribute "a" not found
+select atacc1.a from atacc1;
+ERROR: No such attribute atacc1.a
+select b,c,d from atacc1;
+ b | c | d
+---+---+---
+ 2 | 3 | 4
+(1 row)
+
+select a,b,c,d from atacc1;
+ERROR: Attribute "a" not found
+select * from atacc1 where a = 1;
+ERROR: Attribute "a" not found
+select "........pg.dropped.1........" from atacc1;
+ERROR: Attribute "........pg.dropped.1........" not found
+select atacc1."........pg.dropped.1........" from atacc1;
+ERROR: No such attribute atacc1.........pg.dropped.1........
+select "........pg.dropped.1........",b,c,d from atacc1;
+ERROR: Attribute "........pg.dropped.1........" not found
+select * from atacc1 where "........pg.dropped.1........" = 1;
+ERROR: Attribute "........pg.dropped.1........" not found
+-- UPDATEs
+update atacc1 set a = 3;
+ERROR: Relation "atacc1" has no column "a"
+update atacc1 set b = 2 where a = 3;
+ERROR: Attribute "a" not found
+update atacc1 set "........pg.dropped.1........" = 3;
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
+ERROR: Attribute "........pg.dropped.1........" not found
+-- 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: Relation "atacc1" has no column "a"
+insert into atacc1 (a) values (default);
+ERROR: Relation "atacc1" has no column "a"
+insert into atacc1 (a,b,c,d) values (10,11,12,13);
+ERROR: Relation "atacc1" has no column "a"
+insert into atacc1 (a,b,c,d) values (default,11,12,13);
+ERROR: Relation "atacc1" has no column "a"
+insert into atacc1 (b,c,d) values (11,12,13);
+insert into atacc1 ("........pg.dropped.1........") values (10);
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+insert into atacc1 ("........pg.dropped.1........") values (default);
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+-- DELETEs
+delete from atacc1 where a = 3;
+ERROR: Attribute "a" not found
+delete from atacc1 where "........pg.dropped.1........" = 3;
+ERROR: Attribute "........pg.dropped.1........" not found
+delete from atacc1;
+-- try dropping a non-existent column, should fail
+alter table atacc1 drop bar;
+ERROR: Relation "atacc1" has no column "bar"
+-- try dropping the oid column, should fail
+alter table atacc1 drop oid;
+ERROR: ALTER TABLE: Cannot drop system attribute "oid"
+-- 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: ALTER TABLE: relation "myview" is not a table
+drop view myview;
+-- test some commands to make sure they fail on the dropped column
+analyze atacc1(a);
+ERROR: Relation "atacc1" has no column "a"
+analyze atacc1("........pg.dropped.1........");
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+vacuum analyze atacc1(a);
+ERROR: Relation "atacc1" has no column "a"
+vacuum analyze atacc1("........pg.dropped.1........");
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+comment on column atacc1.a is 'testing';
+ERROR: Relation "atacc1" has no column "a"
+comment on column atacc1."........pg.dropped.1........" is 'testing';
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 alter a set storage plain;
+ERROR: ALTER TABLE: relation "atacc1" has no column "a"
+alter table atacc1 alter "........pg.dropped.1........" set storage plain;
+ERROR: ALTER TABLE: relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 alter a set statistics 0;
+ERROR: ALTER TABLE: relation "atacc1" has no column "a"
+alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
+ERROR: ALTER TABLE: relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 alter a set default 3;
+ERROR: Relation "atacc1" has no column "a"
+alter table atacc1 alter "........pg.dropped.1........" set default 3;
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 alter a drop default;
+ERROR: Relation "atacc1" has no column "a"
+alter table atacc1 alter "........pg.dropped.1........" drop default;
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 alter a set not null;
+ERROR: Relation "atacc1" has no column "a"
+alter table atacc1 alter "........pg.dropped.1........" set not null;
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 alter a drop not null;
+ERROR: Relation "atacc1" has no column "a"
+alter table atacc1 alter "........pg.dropped.1........" drop not null;
+ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+alter table atacc1 rename a to x;
+ERROR: renameatt: attribute "a" does not exist
+alter table atacc1 rename "........pg.dropped.1........" to x;
+ERROR: renameatt: attribute "........pg.dropped.1........" does not exist
+alter table atacc1 add primary key(a);
+ERROR: ALTER TABLE: column "a" named in key does not exist
+alter table atacc1 add primary key("........pg.dropped.1........");
+ERROR: ALTER TABLE: column "........pg.dropped.1........" named in key does not exist
+alter table atacc1 add unique(a);
+ERROR: ALTER TABLE: column "a" named in key does not exist
+alter table atacc1 add unique("........pg.dropped.1........");
+ERROR: ALTER TABLE: column "........pg.dropped.1........" named in key does not exist
+alter table atacc1 add check (a > 3);
+ERROR: Attribute "a" not found
+alter table atacc1 add check ("........pg.dropped.1........" > 3);
+ERROR: Attribute "........pg.dropped.1........" not found
+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);
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: ALTER TABLE: column "a" referenced in foreign key constraint does not exist
+alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: ALTER TABLE: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
+alter table atacc2 add foreign key (id) references atacc1(a);
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: ALTER TABLE: column "a" referenced in foreign key constraint does not exist
+alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
+NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: ALTER TABLE: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
+drop table atacc2;
+create index "testing_idx" on atacc1(a);
+ERROR: DefineIndex: attribute "a" not found
+create index "testing_idx" on atacc1("........pg.dropped.1........");
+ERROR: DefineIndex: attribute "........pg.dropped.1........" not found
+-- 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: Relation "test" has no column "a"
+copy test("........pg.dropped.1........") to stdout;
+ERROR: Relation "test" has no column "........pg.dropped.1........"
+copy test from stdin;
+ERROR: copy: line 1, Extra data after last expected column
+lost synchronization with server, resetting connection
+SET autocommit TO 'on';
+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: Relation "test" has no column "a"
+copy test("........pg.dropped.1........") from stdin;
+ERROR: Relation "test" has no column "........pg.dropped.1........"
+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: ALTER TABLE: Cannot drop inherited column "a"
+alter table only dropColumnChild drop column b;
+ERROR: ALTER TABLE: 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: renameatt: inherited attribute "a" may not be renamed
+alter table only renameColumnChild rename column a to d;
+ERROR: Inherited attribute "a" must be renamed in child tables too
+alter table only renameColumn rename column a to d;
+ERROR: Inherited attribute "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: Attribute 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: CREATE TABLE: merging attribute "f1" with inherited definition
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+ERROR: ALTER TABLE: 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: Attribute "f1" not found
+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: ALTER TABLE: 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: Attribute "f1" not found
+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: ALTER TABLE: 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: CREATE TABLE: merging attribute "f1" with inherited definition
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+ERROR: ALTER TABLE: 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: CREATE TABLE: merging multiple inherited definitions of attribute "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: ALTER TABLE: 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: Relation "gc1" has no column "name"
+-- 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 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: ALTER TABLE: 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: ExecInsert: rejected due to CHECK constraint "p1_a1" on "c1"
+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 p1_a1 on table c1
projects / postgresql / blobdiff