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;
+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_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;
--- analyze to ensure we get an indexscan here
+-- 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;
-- toast-like relation name
alter table stud_emp rename to pg_toast_stud_emp;
-- 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"
+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;
-ERROR: foreign key constraint "$1" cannot be implemented
+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);
-ERROR: foreign key constraint "$1" cannot be implemented
+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 succeed, even though they are different types
--- because varchar=int does exist
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;
-WARNING: foreign key constraint "$1" will require costly sequential scans
-DETAIL: Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
--- As should this
-ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
-WARNING: foreign key constraint "$2" will require costly sequential scans
-DETAIL: Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
-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"
-- 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;
-ERROR: foreign key constraint "$1" cannot be implemented
+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);
-ERROR: foreign key constraint "$1" cannot be implemented
+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);
-ERROR: foreign key constraint "$1" cannot be implemented
+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);
-ERROR: foreign key constraint "$1" cannot be implemented
+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
alter table atacc1 add check (test2>test);
-- should fail for $2
insert into atacc1 (test2, test) values (3, 4);
-ERROR: new row for relation "atacc1" violates 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);
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);
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"
insert into atacc1 (test) values (2);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: duplicate key violates unique constraint "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
insert into atacc1 (test,test2) values (4,4);
-- should fail
insert into atacc1 (test,test2) values (4,4);
-ERROR: duplicate key violates unique constraint "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);
-- should fail for @@ second one @@
insert into atacc1 (test2, test) values (3, 3);
insert into atacc1 (test2, test) values (2, 3);
-ERROR: duplicate key violates unique constraint "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);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
insert into atacc1 (test) values (2);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: duplicate key violates unique constraint "atacc_test1"
+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);
-- 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: column "test" contains null values
insert into atacc1 (test) values (3);
drop table atacc1;
insert into atacc1 (test,test2) values (4,4);
-- should fail
insert into atacc1 (test,test2) values (4,4);
-ERROR: duplicate key violates unique constraint "atacc_test1"
+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);
-- only first should succeed
insert into atacc1 (test2, test) values (3, 3);
insert into atacc1 (test2, test) values (2, 3);
-ERROR: duplicate key violates unique constraint "atacc1_pkey"
+ERROR: duplicate key value violates unique constraint "atacc1_pkey"
insert into atacc1 (test2, test) values (1, NULL);
ERROR: null value in column "test" violates not-null constraint
drop table atacc1;
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"
alter table atacc1 alter column test drop not null;
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;
select * from atacc1 order by a;
ERROR: column "a" does not exist
+LINE 1: select * from atacc1 order by a;
+ ^
select * from atacc1 order by "........pg.dropped.1........";
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
+ ^
select * from atacc1 group by a;
ERROR: column "a" does not exist
+LINE 1: select * from atacc1 group by a;
+ ^
select * from atacc1 group by "........pg.dropped.1........";
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
+ ^
select atacc1.* from atacc1;
b | c | d
---+---+---
select a from atacc1;
ERROR: column "a" does not exist
+LINE 1: select a from atacc1;
+ ^
select atacc1.a from atacc1;
ERROR: column atacc1.a does not exist
+LINE 1: select atacc1.a from atacc1;
+ ^
select b,c,d from atacc1;
b | c | d
---+---+---
select a,b,c,d from atacc1;
ERROR: column "a" does not exist
+LINE 1: select a,b,c,d from atacc1;
+ ^
select * from atacc1 where a = 1;
ERROR: column "a" does not exist
+LINE 1: select * from atacc1 where a = 1;
+ ^
select "........pg.dropped.1........" from atacc1;
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select "........pg.dropped.1........" from atacc1;
+ ^
select atacc1."........pg.dropped.1........" from atacc1;
ERROR: column atacc1.........pg.dropped.1........ does not exist
+LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
+ ^
select "........pg.dropped.1........",b,c,d from atacc1;
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
+ ^
select * from atacc1 where "........pg.dropped.1........" = 1;
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
+ ^
-- UPDATEs
update atacc1 set a = 3;
ERROR: column "a" of relation "atacc1" does not exist
+LINE 1: update atacc1 set a = 3;
+ ^
update atacc1 set b = 2 where a = 3;
ERROR: column "a" does not exist
+LINE 1: update atacc1 set b = 2 where a = 3;
+ ^
update atacc1 set "........pg.dropped.1........" = 3;
ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
+LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
+ ^
update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
+ ^
-- INSERTs
insert into atacc1 values (10, 11, 12, 13);
ERROR: INSERT has more expressions than target columns
insert into atacc1 values (11, 12, 13);
insert into atacc1 (a) values (10);
ERROR: column "a" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 (a) values (10);
+ ^
insert into atacc1 (a) values (default);
ERROR: column "a" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 (a) values (default);
+ ^
insert into atacc1 (a,b,c,d) values (10,11,12,13);
ERROR: column "a" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
+ ^
insert into atacc1 (a,b,c,d) values (default,11,12,13);
ERROR: column "a" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
+ ^
insert into atacc1 (b,c,d) values (11,12,13);
insert into atacc1 ("........pg.dropped.1........") values (10);
ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
+ ^
insert into atacc1 ("........pg.dropped.1........") values (default);
ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
+ ^
insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
+ ^
insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
+LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
+ ^
-- DELETEs
delete from atacc1 where a = 3;
ERROR: column "a" does not exist
+LINE 1: delete from atacc1 where a = 3;
+ ^
delete from atacc1 where "........pg.dropped.1........" = 3;
ERROR: column "........pg.dropped.1........" does not exist
+LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
+ ^
delete from atacc1;
-- try dropping a non-existent column, should fail
alter table atacc1 drop bar;
ERROR: column "bar" of relation "atacc1" does not exist
--- try dropping the oid column, should fail
+-- try dropping the oid column, should succeed
alter table atacc1 drop oid;
-ERROR: cannot drop system column "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;
alter table atacc1 drop d;
alter table atacc1 drop b;
select * from atacc1;
-
--
(1 row)
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);
-- 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);
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
-----
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
-----
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 "p1_a1"
+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
drop table p1 cascade;
NOTICE: drop cascades to table c1
-NOTICE: drop cascades to constraint p1_a1 on 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;
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