-- 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;
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)
+CREATE TABLE tmp (regtable int);
+CREATE TEMP TABLE tmp (tmptable int);
+ALTER TABLE tmp RENAME TO tmp_new;
+SELECT * FROM tmp;
+ regtable
+----------
+(0 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)
+SELECT * FROM tmp_new;
+ tmptable
+----------
+(0 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 tmp RENAME TO tmp_new2;
+SELECT * FROM tmp; -- should fail
+ERROR: relation "tmp" does not exist
+SELECT * FROM tmp_new;
+ tmptable
+----------
+(0 rows)
-ALTER TABLE ten_k RENAME TO tenk1;
--- 5 values, sorted
-SELECT unique1 FROM tenk1 WHERE unique1 < 5;
- unique1
----------
- 0
- 1
- 2
- 3
- 4
-(5 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 TABLE onek_unique1 RENAME TO tmp_onek_unique1;
-ALTER TABLE tmp_onek_unique1 RENAME TO onek_unique1;
+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
4
(5 rows)
+reset enable_seqscan;
+reset enable_bitmapscan;
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);
-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);
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;
-- 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'
+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 inet=int does not exist
+-- 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 'inet' 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 '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
+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 cascade;
-NOTICE: Drop cascades to constraint $2 on table fktable
-NOTICE: Drop cascades to constraint $1 on table fktable
-DROP TABLE fktable;
+-- 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 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
+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;
-- 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
+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;
-- 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 'inet'
- 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 'inet' 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 );
alter table atacc1 add constraint atacc_test1 check (test>3);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: ExecInsert: 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;
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);
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: ExecInsert: rejected due to CHECK constraint atacc_test1
+ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
-- 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: ExecInsert: rejected due to CHECK constraint $1
+ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
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: ExecInsert: 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: ExecInsert: 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);
alter table only atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
-ERROR: ExecInsert: 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);
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 );
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 constraint fails
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);
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 );
+create table atacc1 ( test int ) with oids;
-- add a primary key constraint
alter table atacc1 add constraint atacc_test1 primary key (test);
-ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
+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);
--- try adding a primary key oid constraint
+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);
-NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_oid1' for table 'atacc1'
+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 into atacc1 (test) values (2);
-- add a primary key (fails)
alter table atacc1 add constraint atacc_test1 primary key (test);
-ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
+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
insert into atacc1 (test) values (NULL);
-- add a primary key (fails)
alter table atacc1 add constraint atacc_test1 primary key (test);
-ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
+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
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
+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);
-ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
+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: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
+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);
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'
+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
+ERROR: duplicate key value violates unique constraint "atacc1_pkey"
insert into atacc1 (test2, test) values (1, NULL);
-ERROR: ExecInsert: Fail to add null value in not null attribute test
+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: ALTER TABLE: relation "pg_class" is a system catalog
+ERROR: permission denied: "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
+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
+ERROR: relation "non_existent" does not exist
alter table non_existent alter column bar drop not null;
-ERROR: Relation "non_existent" does not exist
+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);
+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'
+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
+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: ALTER TABLE: Attribute "test" contains NULL values
+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: Relation "atacc1" has no column "bar"
+ERROR: column "bar" of relation "atacc1" does not exist
alter table atacc1 alter bar drop not null;
-ERROR: Relation "atacc1" has no column "bar"
+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: ALTER TABLE: Cannot alter system attribute "oid"
+ERROR: cannot alter system column "oid"
alter table atacc1 alter oid drop not null;
-ERROR: ALTER TABLE: Cannot alter system attribute "oid"
+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: ALTER TABLE: relation "myview" is not a table
+ERROR: "myview" is not a table
alter table myview alter column test set not null;
-ERROR: ALTER TABLE: relation "myview" is not a table
+ERROR: "myview" is not a table
drop view myview;
drop table atacc1;
-- test inheritance
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
+ERROR: null value in column "a" violates not-null constraint
insert into child (a, b) values (NULL, 'foo');
-ERROR: ExecInsert: Fail to add null value in not null attribute a
+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: ALTER TABLE: Attribute "a" contains NULL values
+ERROR: column "a" contains null values
alter table child alter a set not null;
-ERROR: ALTER TABLE: Attribute "a" contains NULL values
+ERROR: column "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
+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: ExecInsert: Fail to add null value in not null attribute a
+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: ExecInsert: Fail to add null value in not null attribute a
+ERROR: null value in column "a" violates not-null constraint
drop table child;
drop table parent;
-- test setting and removing default values
-- 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"
+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: Relation "def_test" has no column "c3"
+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
-- 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
+ERROR: permission denied: "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
+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);
+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: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
-- SELECTs
select * from atacc1;
b | c | d
(1 row)
select * from atacc1 order by a;
-ERROR: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: select * from atacc1 order by a;
+ ^
select * from atacc1 order by "........pg.dropped.1........";
-ERROR: Attribute "........pg.dropped.1........" not found
+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: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: select * from atacc1 group by a;
+ ^
select * from atacc1 group by "........pg.dropped.1........";
-ERROR: Attribute "........pg.dropped.1........" not found
+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
---+---+---
(1 row)
select a from atacc1;
-ERROR: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: select a from atacc1;
+ ^
select atacc1.a from atacc1;
-ERROR: No such attribute atacc1.a
+ERROR: column atacc1.a does not exist
+LINE 1: select atacc1.a from atacc1;
+ ^
select b,c,d from atacc1;
b | c | d
---+---+---
(1 row)
select a,b,c,d from atacc1;
-ERROR: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: select a,b,c,d from atacc1;
+ ^
select * from atacc1 where a = 1;
-ERROR: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: select * from atacc1 where a = 1;
+ ^
select "........pg.dropped.1........" from atacc1;
-ERROR: Attribute "........pg.dropped.1........" not found
+ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select "........pg.dropped.1........" from atacc1;
+ ^
select atacc1."........pg.dropped.1........" from atacc1;
-ERROR: No such attribute atacc1.........pg.dropped.1........
+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: Attribute "........pg.dropped.1........" not found
+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: Attribute "........pg.dropped.1........" not found
+ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
+ ^
-- UPDATEs
update atacc1 set a = 3;
-ERROR: Relation "atacc1" has no column "a"
+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: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: update atacc1 set b = 2 where a = 3;
+ ^
update atacc1 set "........pg.dropped.1........" = 3;
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+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: Attribute "........pg.dropped.1........" not found
+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
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"
+ERROR: column "a" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 (a) values (10);
+ ^
insert into atacc1 (a) values (default);
-ERROR: Relation "atacc1" has no column "a"
+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: Relation "atacc1" has no column "a"
+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: Relation "atacc1" has no column "a"
+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: Relation "atacc1" has no column "........pg.dropped.1........"
+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: Relation "atacc1" has no column "........pg.dropped.1........"
+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: Relation "atacc1" has no column "........pg.dropped.1........"
+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: Relation "atacc1" has no column "........pg.dropped.1........"
+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: Attribute "a" not found
+ERROR: column "a" does not exist
+LINE 1: delete from atacc1 where a = 3;
+ ^
delete from atacc1 where "........pg.dropped.1........" = 3;
-ERROR: Attribute "........pg.dropped.1........" not found
+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: Relation "atacc1" has no column "bar"
--- try dropping the oid column, should fail
+ERROR: column "bar" of relation "atacc1" does not exist
+-- try dropping the oid column, should succeed
alter table atacc1 drop oid;
-ERROR: ALTER TABLE: Cannot drop system attribute "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;
(0 rows)
alter table myview drop d;
-ERROR: ALTER TABLE: relation "myview" is not a table
+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: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
analyze atacc1("........pg.dropped.1........");
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
vacuum analyze atacc1(a);
-ERROR: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
vacuum analyze atacc1("........pg.dropped.1........");
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
comment on column atacc1.a is 'testing';
-ERROR: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
comment on column atacc1."........pg.dropped.1........" is 'testing';
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set storage plain;
-ERROR: ALTER TABLE: relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set storage plain;
-ERROR: ALTER TABLE: relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set statistics 0;
-ERROR: ALTER TABLE: relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
-ERROR: ALTER TABLE: relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set default 3;
-ERROR: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set default 3;
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a drop default;
-ERROR: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" drop default;
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set not null;
-ERROR: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set not null;
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a drop not null;
-ERROR: Relation "atacc1" has no column "a"
+ERROR: column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" drop not null;
-ERROR: Relation "atacc1" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 rename a to x;
-ERROR: renameatt: attribute "a" does not exist
+ERROR: column "a" does not exist
alter table atacc1 rename "........pg.dropped.1........" to x;
-ERROR: renameatt: attribute "........pg.dropped.1........" does not exist
+ERROR: column "........pg.dropped.1........" does not exist
alter table atacc1 add primary key(a);
-ERROR: ALTER TABLE: column "a" named in key does not exist
+ERROR: 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
+ERROR: 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
+ERROR: 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
+ERROR: column "........pg.dropped.1........" named in key does not exist
alter table atacc1 add check (a > 3);
-ERROR: Attribute "a" not found
+ERROR: column "a" does not exist
alter table atacc1 add check ("........pg.dropped.1........" > 3);
-ERROR: Attribute "........pg.dropped.1........" not found
+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'
+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
+ERROR: 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
+ERROR: 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: UNIQUE constraint matching given keys for referenced table "atacc1" not found
+ERROR: 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: UNIQUE constraint matching given keys for referenced table "atacc1" not found
+ERROR: 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
+ERROR: column "a" does not exist
create index "testing_idx" on atacc1("........pg.dropped.1........");
-ERROR: DefineIndex: attribute "........pg.dropped.1........" not found
+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;
alter table atacc1 drop c;
alter table atacc1 drop d;
alter table atacc1 drop b;
-ERROR: ALTER TABLE: Cannot drop last column from table "atacc1"
select * from atacc1;
- b
-----
- 21
+--
(1 row)
drop table atacc1;
copy test to stdout;
2 3
copy test(a) to stdout;
-ERROR: Relation "test" has no column "a"
+ERROR: column "a" of relation "test" does not exist
copy test("........pg.dropped.1........") to stdout;
-ERROR: Relation "test" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "test" does not exist
copy test from stdin;
-ERROR: copy: line 1, Extra data after last expected column
-lost synchronization with server, resetting connection
+ERROR: extra data after last expected column
+CONTEXT: COPY test, line 1: "10 11 12"
select * from test;
b | c
---+---
(2 rows)
copy test(a) from stdin;
-ERROR: Relation "test" has no column "a"
+ERROR: column "a" of relation "test" does not exist
copy test("........pg.dropped.1........") from stdin;
-ERROR: Relation "test" has no column "........pg.dropped.1........"
+ERROR: column "........pg.dropped.1........" of relation "test" does not exist
copy test(b,c) from stdin;
select * from test;
b | c
(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
projects / postgresql / blobdiff