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 xmin integer; -- fails
+ERROR: column name "xmin" conflicts with a system column name
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 z int2[];
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
v, w, x, y, z)
- VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
- 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
+ VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
+ 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
314159, '(1,1)', '512',
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
(1 row)
DROP TABLE tmp;
--- the wolf bug - schema mods caused inconsistent row descriptors
+-- the wolf bug - schema mods caused inconsistent row descriptors
CREATE TABLE tmp (
initial int4
);
ALTER TABLE tmp ADD COLUMN z int2[];
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
v, w, x, y, z)
- VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
- 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
+ VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
+ 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
314159, '(1,1)', '512',
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
ALTER TABLE tmp RENAME TO tmp_new2;
SELECT * FROM tmp; -- should fail
ERROR: relation "tmp" does not exist
+LINE 1: SELECT * FROM tmp;
+ ^
SELECT * FROM tmp_new;
tmptable
----------
DROP TABLE tmp_new2;
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
+ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
+NOTICE: relation "__onek_unique1" does not exist, skipping
+ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
+NOTICE: relation "__tmp_onek_unique1" does not exist, skipping
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
+-- 5 values, sorted
SELECT unique1 FROM tenk1 WHERE unique1 < 5;
unique1
---------
-- toast-like relation name
alter table stud_emp rename to pg_toast_stud_emp;
alter table pg_toast_stud_emp rename to stud_emp;
+-- renaming index should rename constraint as well
+ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
+ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
+ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
+-- renaming constraint
+ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
+ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
+ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
+-- renaming constraint should rename index as well
+ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
+DROP INDEX onek_unique1_constraint; -- to see whether it's there
+ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it
+HINT: You can drop constraint onek_unique1_constraint on table onek instead.
+ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
+DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
+ERROR: cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it
+HINT: You can drop constraint onek_unique1_constraint_foo on table onek instead.
+ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
+-- renaming constraints vs. inheritance
+CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+Check constraints:
+ "con1" CHECK (a > 0)
+
+CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging constraint "con1" with inherited definition
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
+ERROR: cannot rename inherited constraint "con1"
+ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
+ERROR: inherited constraint "con1" must be renamed in child tables too
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
+ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+ "con2bar" CHECK (b > 0) NO INHERIT
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer | not null
+ b | integer |
+ c | integer |
+Indexes:
+ "con3foo" PRIMARY KEY, btree (a)
+Check constraints:
+ "con1foo" CHECK (a > 0)
+ "con2bar" CHECK (b > 0) NO INHERIT
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+DROP TABLE constraint_rename_test2;
+DROP TABLE constraint_rename_test;
+ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok
+NOTICE: relation "constraint_not_exist" does not exist, skipping
+ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
+NOTICE: relation "constraint_rename_test" does not exist, skipping
-- 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"
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"
CREATE TABLE tmp5 (a int, b int);
-- Insert rows into tmp2 (pktable)
INSERT INTO tmp2 values (1);
DELETE FROM tmp3 where a=5;
-- Try (and succeed)
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
+ALTER TABLE tmp3 drop constraint tmpconstr;
+INSERT INTO tmp3 values (5,50);
+-- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
+ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
+ALTER TABLE tmp3 validate constraint tmpconstr;
+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) and repeat to show it works on already valid constraint
+ALTER TABLE tmp3 validate constraint tmpconstr;
+ALTER TABLE tmp3 validate constraint tmpconstr;
+-- Try a non-verified CHECK constraint
+ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
+ERROR: check constraint "b_greater_than_ten" is violated by some row
+ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
+ERROR: check constraint "b_greater_than_ten" is violated by some row
+DELETE FROM tmp3 WHERE NOT b > 10;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
+-- Test inherited NOT VALID CHECK constraints
+select * from tmp3;
+ a | b
+---+----
+ 1 | 20
+(1 row)
+
+CREATE TABLE tmp6 () INHERITS (tmp3);
+CREATE TABLE tmp7 () INHERITS (tmp3);
+INSERT INTO tmp6 VALUES (6, 30), (7, 16);
+ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- fails
+ERROR: check constraint "b_le_20" is violated by some row
+DELETE FROM tmp6 WHERE b > 20;
+ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
+-- An already validated constraint must not be revalidated
+CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
+INSERT INTO tmp7 VALUES (8, 18);
+ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
+NOTICE: boo: 18
+ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
+NOTICE: merging constraint "identity" with inherited definition
+ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
+NOTICE: boo: 16
+NOTICE: boo: 20
-- 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;
ERROR: there is no unique constraint matching given keys for referenced table "tmp4"
+DROP TABLE tmp7;
+DROP TABLE tmp6;
DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
+-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
+-- exclusion until validated
+set constraint_exclusion TO 'partition';
+create table nv_parent (d date);
+create table nv_child_2010 () inherits (nv_parent);
+create table nv_child_2011 () inherits (nv_parent);
+alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
+alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
+explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2010
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2011
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+(7 rows)
+
+create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
+explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2010
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+ -> Seq Scan on nv_child_2011
+ Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
+(7 rows)
+
+explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2010
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2011
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2009
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+(9 rows)
+
+-- after validation, the constraint should be used
+alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
+explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Append
+ -> Seq Scan on nv_parent
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2010
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+ -> Seq Scan on nv_child_2009
+ Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
+(7 rows)
+
+-- add an inherited NOT VALID constraint
+alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid;
+\d nv_child_2009
+Table "public.nv_child_2009"
+ Column | Type | Modifiers
+--------+------+-----------
+ d | date |
+Check constraints:
+ "nv_child_2009_d_check" CHECK (d >= '01-01-2009'::date AND d <= '12-31-2009'::date)
+ "nv_parent_d_check" CHECK (d >= '01-01-2001'::date AND d <= '12-31-2099'::date) NOT VALID
+Inherits: nv_parent
+
+-- we leave nv_parent and children around to help test pg_dump logic
-- Foreign key adding test with mixed types
-- Note: these tables are TEMP to avoid name conflicts when this test
-- is run in parallel with foreign_key.sql.
CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
+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 "fktable_ftest1_fkey" cannot be implemented
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
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 "fktable_ftest1_fkey" 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 "fktable_ftest1_fkey1" 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 fktable_ftest1_fkey1 on table fktable
-NOTICE: drop cascades to constraint fktable_ftest1_fkey 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);
+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;
-- should fail
insert into atacc1 (test) values (2);
ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
+DETAIL: Failing row contains (2).
-- should succeed
insert into atacc1 (test) values (4);
drop table atacc1;
-- add a check constraint (fails)
alter table atacc1 add constraint atacc_test1 check (test1>3);
ERROR: column "test1" does not exist
+HINT: Perhaps you meant to reference the column "atacc1"."test".
drop table atacc1;
-- something a little more complicated
create table atacc1 ( test int, test2 int, test3 int);
-- should fail
insert into atacc1 (test,test2,test3) values (4,4,2);
ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
+DETAIL: Failing row contains (4, 4, 2).
-- should succeed
insert into atacc1 (test,test2,test3) values (4,4,5);
drop table atacc1;
-- should fail for $2
insert into atacc1 (test2, test) values (3, 4);
ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
+DETAIL: Failing row contains (4, 3).
drop table atacc1;
-- inheritance related tests
create table atacc1 (test int);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
ERROR: new row for relation "atacc2" violates check constraint "foo"
+DETAIL: Failing row contains (-3).
insert into atacc2 (test2) values (3);
-- fail and then succeed on atacc3
insert into atacc3 (test2) values (-3);
ERROR: new row for relation "atacc3" violates check constraint "foo"
+DETAIL: Failing row contains (null, -3, null).
insert into atacc3 (test2) values (3);
drop table atacc3;
drop table atacc2;
drop table atacc1;
--- let's try only to add only to the parent
+-- 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 only atacc2 add constraint foo check (test2>0);
--- fail and then succeed on atacc2
-insert into atacc2 (test2) values (-3);
-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);
+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);
-drop table atacc3;
+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 inherit atacc2;
+ERROR: child table "atacc3" has different type for column "test2"
+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
+drop table atacc1;
+-- adding only to a parent is allowed as of 9.2
+create table atacc1 (test int);
+create table atacc2 (test2 int) inherits (atacc1);
+-- ok:
+alter table atacc1 add constraint foo check (test>0) no inherit;
+-- check constraint is not there on child
+insert into atacc2 (test) values (-3);
+-- check constraint is there on parent
+insert into atacc1 (test) values (-3);
+ERROR: new row for relation "atacc1" violates check constraint "foo"
+DETAIL: Failing row contains (-3).
+insert into atacc1 (test) values (3);
+-- fail, violating row:
+alter table atacc2 add constraint foo check (test>0) no inherit;
+ERROR: check constraint "foo" is violated by some row
drop table atacc2;
drop table atacc1;
-- test unique constraint adding
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 first value
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"
+DETAIL: Key (test)=(2) already exists.
-- 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"
+-- try to create duplicates via alter table using - should fail
+alter table atacc1 alter column test type integer using 0;
+ERROR: could not create unique index "atacc_test1"
+DETAIL: Key (test)=(0) is duplicated.
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: could not create unique index
-DETAIL: Table contains duplicated values.
+ERROR: could not create unique index "atacc_test1"
+DETAIL: Key (test)=(2) is duplicated.
insert into atacc1 (test) values (3);
drop table atacc1;
-- let's do one where the unique constraint fails
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"
-- insert initial value
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"
+DETAIL: Key (test, test2)=(4, 4) already exists.
-- 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"
alter table atacc1 add unique (test2);
-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: duplicate key violates unique constraint "atacc1_test_key"
+ERROR: duplicate key value violates unique constraint "atacc1_test_key"
+DETAIL: Key (test)=(3) already exists.
drop table atacc1;
-- test primary key constraint adding
create table atacc1 ( test int ) with oids;
-- add a primary key constraint
alter table atacc1 add constraint atacc_test1 primary key (test);
-NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
-- insert first value
insert into atacc1 (test) values (2);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: duplicate key violates unique constraint "atacc_test1"
+ERROR: duplicate key value violates unique constraint "atacc_test1"
+DETAIL: Key (test)=(2) already exists.
-- should succeed
insert into atacc1 (test) values (4);
-- inserting NULL should fail
insert into atacc1 (test) values(NULL);
ERROR: null value in column "test" violates not-null constraint
+DETAIL: Failing row contains (null).
-- try adding a second primary key (should fail)
alter table atacc1 add constraint atacc_oid1 primary key(oid);
ERROR: multiple primary keys for table "atacc1" are not allowed
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);
-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.
+ERROR: could not create unique index "atacc_test1"
+DETAIL: Key (test)=(2) is duplicated.
insert into atacc1 (test) values (3);
drop table atacc1;
-- let's do another one where the primary key constraint fails when added
alter table atacc1 add constraint atacc_test1 primary key (test1);
ERROR: column "test1" named in key does not exist
drop table atacc1;
+-- adding a new column as primary key to a non-empty table.
+-- should fail unless the column has a non-null default value.
+create table atacc1 ( test int );
+insert into atacc1 (test) values (0);
+-- add a primary key column without a default (fails).
+alter table atacc1 add column test2 int primary key;
+ERROR: column "test2" contains null values
+-- now add a primary key column with a default (succeeds).
+alter table atacc1 add column test2 int default 0 primary key;
+drop table atacc1;
-- something a little more complicated
create table atacc1 ( test int, test2 int);
-- add a primary key constraint
alter table atacc1 add constraint atacc_test1 primary key (test, test2);
-NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
-- try adding a second primary key - should fail
alter table atacc1 add constraint atacc_test2 primary key (test);
ERROR: multiple primary keys for table "atacc1" are not allowed
insert 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"
+DETAIL: Key (test, test2)=(4, 4) already exists.
insert into atacc1 (test,test2) values (NULL,3);
ERROR: null value in column "test" violates not-null constraint
+DETAIL: Failing row contains (null, 3).
insert into atacc1 (test,test2) values (3, NULL);
ERROR: null value in column "test2" violates not-null constraint
+DETAIL: Failing row contains (3, null).
insert into atacc1 (test,test2) values (NULL,NULL);
ERROR: null value in column "test" violates not-null constraint
+DETAIL: Failing row contains (null, null).
-- 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"
-- 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"
+DETAIL: Key (test)=(3) already exists.
insert into atacc1 (test2, test) values (1, NULL);
ERROR: null value in column "test" violates not-null constraint
+DETAIL: Failing row contains (null, 1).
drop table atacc1;
-- alter table / alter column [set/drop] not null tests
-- try altering system catalogs, should fail
-- test checking for null values and primary key
create table atacc1 (test int not null) with oids;
alter table atacc1 add constraint "atacc1_pkey" primary key (test);
-NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
alter table atacc1 alter column test drop not null;
ERROR: column "test" is in a primary key
alter table atacc1 drop constraint "atacc1_pkey";
-- try creating a view and altering that, should fail
create view myview as select * from atacc1;
alter table myview alter column test drop not null;
-ERROR: "myview" is not a table
+ERROR: "myview" is not a table or foreign table
alter table myview alter column test set not null;
-ERROR: "myview" is not a table
+ERROR: "myview" is not a table or foreign table
drop view myview;
drop table atacc1;
-- test inheritance
alter table parent alter a set not null;
insert into parent values (NULL);
ERROR: null value in column "a" violates not-null constraint
+DETAIL: Failing row contains (null).
insert into child (a, b) values (NULL, 'foo');
ERROR: null value in column "a" violates not-null constraint
+DETAIL: Failing row contains (null, foo).
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;
insert into parent values (NULL);
ERROR: null value in column "a" violates not-null constraint
+DETAIL: Failing row contains (null).
alter table child alter a set not null;
insert into child (a, b) values (NULL, 'foo');
ERROR: null value in column "a" violates not-null constraint
+DETAIL: Failing row contains (null, foo).
delete from child;
alter table child alter a set not null;
insert into child (a, b) values (NULL, 'foo');
ERROR: null value in column "a" violates not-null constraint
+DETAIL: Failing row contains (null, foo).
drop table child;
drop table parent;
-- test setting and removing default values
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
+LINE 1: insert into atacc1 values (10, 11, 12, 13);
+ ^
insert into atacc1 values (default, 11, 12, 13);
ERROR: INSERT has more expressions than target columns
+LINE 1: insert into atacc1 values (default, 11, 12, 13);
+ ^
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;
(0 rows)
alter table myview drop d;
-ERROR: "myview" is not a table
+ERROR: "myview" is not a table, composite type, or foreign table
drop view myview;
-- test some commands to make sure they fail on the dropped column
analyze atacc1(a);
alter table atacc1 add check ("........pg.dropped.1........" > 3);
ERROR: column "........pg.dropped.1........" does not exist
create table atacc2 (id int4 unique);
-NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
alter table atacc1 add foreign key (a) references atacc2(id);
ERROR: column "a" referenced in foreign key constraint does not exist
alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
--
(1 row)
+drop table atacc1;
+-- test constraint error reporting in presence of dropped columns
+create table atacc1 (id serial primary key, value int check (value < 10));
+insert into atacc1(value) values (100);
+ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check"
+DETAIL: Failing row contains (1, 100).
+alter table atacc1 drop column value;
+alter table atacc1 add column value int check (value < 10);
+insert into atacc1(value) values (100);
+ERROR: new row for relation "atacc1" violates check constraint "atacc1_value_check"
+DETAIL: Failing row contains (2, 100).
+insert into atacc1(id, value) values (null, 0);
+ERROR: null value in column "id" violates not-null constraint
+DETAIL: Failing row contains (null, 0).
drop table atacc1;
-- test inheritance
create table parent (a int, b int, c int);
-- these should work
alter table renameColumn rename column a to d;
alter table renameColumnChild rename column b to a;
+-- these should work
+alter table if exists doesnt_exist_tab rename column a to d;
+NOTICE: relation "doesnt_exist_tab" does not exist, skipping
+alter table if exists doesnt_exist_tab rename column b to a;
+NOTICE: relation "doesnt_exist_tab" does not exist, skipping
-- this should work
alter table renameColumn add column w int;
-- this should fail
alter table c1 drop column f1;
select f1 from c1;
ERROR: column "f1" does not exist
+LINE 1: select f1 from c1;
+ ^
+HINT: Perhaps you meant to reference the column "c1"."f2".
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;
+ ^
+HINT: Perhaps you meant to reference the column "c1"."f2".
drop table p1 cascade;
NOTICE: drop cascades to table c1
create table p1 (f1 int, f2 int);
ERROR: column "name" of relation "gc1" does not exist
-- should work and drop the attribute in all tables
alter table p2 drop column height;
+-- IF EXISTS test
+create table dropColumnExists ();
+alter table dropColumnExists drop column non_existing; --fail
+ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
+alter table dropColumnExists drop column if exists non_existing; --succeed
+NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
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
(8 rows)
drop table p1, p2 cascade;
-NOTICE: drop cascades to table c1
-NOTICE: drop cascades to table gc1
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table c1
+drop cascades to table gc1
+-- test attinhcount tracking with merged columns
+create table depth0();
+create table depth1(c text) inherits (depth0);
+create table depth2() inherits (depth1);
+alter table depth0 add c text;
+NOTICE: merging definition of column "c" for child "depth1"
+select attrelid::regclass, attname, attinhcount, attislocal
+from pg_attribute
+where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
+order by attrelid::regclass::text, attnum;
+ attrelid | attname | attinhcount | attislocal
+----------+---------+-------------+------------
+ depth0 | c | 0 | t
+ depth1 | c | 1 | t
+ depth2 | c | 1 | f
+(3 rows)
+
--
--- Test the ALTER TABLE WITHOUT OIDS command
+-- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
--
create table altstartwith (col integer) with oids;
insert into altstartwith values (1);
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
+alter table altstartwith set with oids;
+select oid > 0, * from altstartwith;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+drop table altstartwith;
+-- Check inheritance cases
create table altwithoid (col integer) with oids;
--- Inherits parents oid column
+-- Inherits parents oid column anyway
create table altinhoid () inherits (altwithoid) without oids;
insert into altinhoid values (1);
select oid > 0, * from altwithoid;
(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)
+alter table altwithoid set with oids;
+select oid > 0, * from altwithoid;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+select oid > 0, * from altinhoid;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+drop table altwithoid cascade;
+NOTICE: drop cascades to table altinhoid
+create table altwithoid (col integer) without oids;
+-- child can have local oid column
+create table altinhoid () inherits (altwithoid) with oids;
+insert into altinhoid values (1);
+select oid > 0, * from altwithoid; -- fails
+ERROR: column "oid" does not exist
+LINE 1: select oid > 0, * from altwithoid;
+ ^
+select oid > 0, * from altinhoid;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+alter table altwithoid set with oids;
+NOTICE: merging definition of column "oid" for child "altinhoid"
+select oid > 0, * from altwithoid;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+select oid > 0, * from altinhoid;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+-- the child's local definition should remain
+alter table altwithoid 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;
+ ?column? | col
+----------+-----
+ t | 1
+(1 row)
+
+drop table altwithoid cascade;
+NOTICE: drop cascades to table altinhoid
-- test renumbering of child-table columns in inherited operations
create table p1 (f1 int);
create table c1 (f2 text, f3 int) inherits (p1);
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"
+ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
+DETAIL: Failing row contains (11, xyz, 33, 0).
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 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');
+insert into foo values('bb','cc','dd');
select * from foo;
f1 | f2 | f3
----+----+----
- aa | bb | cc
+ bb | cc | dd
(1 row)
drop domain mytype cascade;
select * from foo;
f1 | f3
----+----
- aa | cc
+ bb | dd
(1 row)
insert into foo values('qq','rr');
select * from foo;
f1 | f3
----+----
- aa | cc
+ bb | dd
qq | rr
(2 rows)
select * from foo;
f1 | f3
----+----
- aa | zz
+ bb | zz
qq | zz
(2 rows)
-- 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"
+ERROR: column "f1" cannot be cast automatically to type integer
+HINT: You might need to specify "USING f1::integer".
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;
(2 rows)
alter table anothertab alter column atcol1 type boolean; -- fails
-ERROR: column "atcol1" cannot be cast to type "pg_catalog.bool"
+ERROR: column "atcol1" cannot be cast automatically to type boolean
+HINT: You might need to specify "USING atcol1::boolean".
+alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails
+ERROR: result of USING clause for column "atcol1" cannot be cast automatically to type boolean
+HINT: You might need to add an explicit cast.
alter table anothertab alter column atcol1 type integer;
select * from anothertab;
atcol1 | atcol2
insert into anothertab (atcol1, atcol2) values (45, null); -- fails
ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
+DETAIL: Failing row contains (45, null).
insert into anothertab (atcol1, atcol2) values (default, null);
select * from anothertab;
atcol1 | atcol2
(3 rows)
alter table anothertab alter column atcol2 type text
- using case when atcol2 is true then 'IT WAS TRUE'
+ 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;
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"
+ERROR: default for column "atcol1" cannot be cast automatically to type boolean
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 may need to add explicit type casts.
+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 drop constraint anothertab_chk; -- fails
+ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
+alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
+NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
alter table anothertab alter column atcol1 type boolean
using case when atcol1 % 2 = 0 then true else false end;
select * from anothertab;
(3 rows)
drop table another;
+-- table's row type
+create table tab1 (a int, b text);
+create table tab2 (x int, y tab1);
+alter table tab1 alter column b type varchar; -- fails
+ERROR: cannot alter table "tab1" because column "tab2.y" uses its row type
+-- disallow recursive containment of row types
+create temp table recur1 (f1 int);
+alter table recur1 add column f2 recur1; -- fails
+ERROR: composite type recur1 cannot be made a member of itself
+alter table recur1 add column f2 recur1[]; -- fails
+ERROR: composite type recur1 cannot be made a member of itself
+create domain array_of_recur1 as recur1[];
+alter table recur1 add column f2 array_of_recur1; -- fails
+ERROR: composite type recur1 cannot be made a member of itself
+create temp table recur2 (f1 int, f2 recur1);
+alter table recur1 add column f2 recur2; -- fails
+ERROR: composite type recur1 cannot be made a member of itself
+alter table recur1 add column f2 int;
+alter table recur1 alter column f2 type recur2; -- fails
+ERROR: composite type recur1 cannot be made a member of itself
+-- SET STORAGE may need to add a TOAST table
+create table test_storage (a text);
+alter table test_storage alter a set storage plain;
+alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
+alter table test_storage alter a set storage extended; -- re-add TOAST table
+select reltoastrelid <> 0 as has_toast_table
+from pg_class
+where oid = 'test_storage'::regclass;
+ has_toast_table
+-----------------
+ t
+(1 row)
+
+-- ALTER TYPE with a check constraint and a child table (bug before Nov 2012)
+CREATE TABLE test_inh_check (a float check (a > 10.2));
+CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
+ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
+\d test_inh_check
+Table "public.test_inh_check"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | numeric |
+Check constraints:
+ "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d test_inh_check_child
+Table "public.test_inh_check_child"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | numeric |
+Check constraints:
+ "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
+Inherits: test_inh_check
+
+-- check for rollback of ANALYZE corrupting table property flags (bug #11638)
+CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text);
+CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text);
+BEGIN;
+ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey;
+ANALYZE check_fk_presence_2;
+ROLLBACK;
+\d check_fk_presence_2
+Table "public.check_fk_presence_2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ id | integer |
+ t | text |
+Foreign-key constraints:
+ "check_fk_presence_2_id_fkey" FOREIGN KEY (id) REFERENCES check_fk_presence_1(id)
+
+DROP TABLE check_fk_presence_1, check_fk_presence_2;
+--
+-- lock levels
+--
+drop type lockmodes;
+ERROR: type "lockmodes" does not exist
+create type lockmodes as enum (
+ 'SIReadLock'
+,'AccessShareLock'
+,'RowShareLock'
+,'RowExclusiveLock'
+,'ShareUpdateExclusiveLock'
+,'ShareLock'
+,'ShareRowExclusiveLock'
+,'ExclusiveLock'
+,'AccessExclusiveLock'
+);
+drop view my_locks;
+ERROR: view "my_locks" does not exist
+create or replace view my_locks as
+select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
+from pg_locks l join pg_class c on l.relation = c.oid
+where virtualtransaction = (
+ select virtualtransaction
+ from pg_locks
+ where transactionid = txid_current()::integer)
+and locktype = 'relation'
+and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
+and c.relname != 'my_locks'
+group by c.relname;
+create table alterlock (f1 int primary key, f2 text);
+insert into alterlock values (1, 'foo');
+create table alterlock2 (f3 int primary key, f1 int);
+insert into alterlock2 values (1, 1);
+begin; alter table alterlock alter column f2 set statistics 150;
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+(1 row)
+
+rollback;
+begin; alter table alterlock cluster on alterlock_pkey;
+select * from my_locks order by 1;
+ relname | max_lockmode
+----------------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+ alterlock_pkey | ShareUpdateExclusiveLock
+(2 rows)
+
+commit;
+begin; alter table alterlock set without cluster;
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+(1 row)
+
+commit;
+begin; alter table alterlock set (fillfactor = 100);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+---------------------
+ alterlock | AccessExclusiveLock
+ pg_toast | AccessExclusiveLock
+(2 rows)
+
+commit;
+begin; alter table alterlock reset (fillfactor);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+---------------------
+ alterlock | AccessExclusiveLock
+ pg_toast | AccessExclusiveLock
+(2 rows)
+
+commit;
+begin; alter table alterlock set (toast.autovacuum_enabled = off);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+ pg_toast | ShareUpdateExclusiveLock
+(2 rows)
+
+commit;
+begin; alter table alterlock set (autovacuum_enabled = off);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+ pg_toast | ShareUpdateExclusiveLock
+(2 rows)
+
+commit;
+begin; alter table alterlock alter column f2 set (n_distinct = 1);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+(1 row)
+
+rollback;
+-- test that mixing options with different lock levels works as expected
+begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+---------------------
+ alterlock | AccessExclusiveLock
+ pg_toast | AccessExclusiveLock
+(2 rows)
+
+commit;
+begin; alter table alterlock alter column f2 set storage extended;
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+---------------------
+ alterlock | AccessExclusiveLock
+(1 row)
+
+rollback;
+begin; alter table alterlock alter column f2 set default 'x';
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+---------------------
+ alterlock | AccessExclusiveLock
+(1 row)
+
+rollback;
+begin;
+create trigger ttdummy
+ before delete or update on alterlock
+ for each row
+ execute procedure
+ ttdummy (1, 1);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------+-----------------------
+ alterlock | ShareRowExclusiveLock
+(1 row)
+
+rollback;
+begin;
+select * from my_locks order by 1;
+ relname | max_lockmode
+---------+--------------
+(0 rows)
+
+alter table alterlock2 add foreign key (f1) references alterlock (f1);
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------------+-----------------------
+ alterlock | ShareRowExclusiveLock
+ alterlock2 | ShareRowExclusiveLock
+ alterlock2_pkey | AccessShareLock
+ alterlock_pkey | AccessShareLock
+(4 rows)
+
+rollback;
+begin;
+alter table alterlock2
+add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID;
+select * from my_locks order by 1;
+ relname | max_lockmode
+------------+-----------------------
+ alterlock | ShareRowExclusiveLock
+ alterlock2 | ShareRowExclusiveLock
+(2 rows)
+
+commit;
+begin;
+alter table alterlock2 validate constraint alterlock2nv;
+select * from my_locks order by 1;
+ relname | max_lockmode
+-----------------+--------------------------
+ alterlock | RowShareLock
+ alterlock2 | ShareUpdateExclusiveLock
+ alterlock2_pkey | AccessShareLock
+ alterlock_pkey | AccessShareLock
+(4 rows)
+
+rollback;
+create or replace view my_locks as
+select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
+from pg_locks l join pg_class c on l.relation = c.oid
+where virtualtransaction = (
+ select virtualtransaction
+ from pg_locks
+ where transactionid = txid_current()::integer)
+and locktype = 'relation'
+and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
+and c.relname = 'my_locks'
+group by c.relname;
+-- raise exception
+alter table my_locks set (autovacuum_enabled = false);
+ERROR: unrecognized parameter "autovacuum_enabled"
+alter view my_locks set (autovacuum_enabled = false);
+ERROR: unrecognized parameter "autovacuum_enabled"
+alter table my_locks reset (autovacuum_enabled);
+alter view my_locks reset (autovacuum_enabled);
+begin;
+alter view my_locks set (security_barrier=off);
+select * from my_locks order by 1;
+ relname | max_lockmode
+----------+---------------------
+ my_locks | AccessExclusiveLock
+(1 row)
+
+alter view my_locks reset (security_barrier);
+rollback;
+-- this test intentionally applies the ALTER TABLE command against a view, but
+-- uses a view option so we expect this to succeed. This form of SQL is
+-- accepted for historical reasons, as shown in the docs for ALTER VIEW
+begin;
+alter table my_locks set (security_barrier=off);
+select * from my_locks order by 1;
+ relname | max_lockmode
+----------+---------------------
+ my_locks | AccessExclusiveLock
+(1 row)
+
+alter table my_locks reset (security_barrier);
+rollback;
+-- cleanup
+drop table alterlock2;
+drop table alterlock;
+drop view my_locks;
+drop type lockmodes;
--
-- alter function
--
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);
+create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
+as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
+create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
+create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
+ operator 1 alter1.=(alter1.ctype, alter1.ctype);
+create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
+create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
+create text search configuration alter1.cfg(parser = alter1.prs);
+create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
+create text search dictionary alter1.dict(template = alter1.tmpl);
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 operator class alter1.ctype_hash_ops using hash set schema alter2;
+alter operator family alter1.ctype_hash_ops using hash set schema alter2;
+alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
+alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
alter type alter1.ctype set schema alter2;
+alter conversion alter1.ascii_to_utf8 set schema alter2;
+alter text search parser alter1.prs set schema alter2;
+alter text search configuration alter1.cfg set schema alter2;
+alter text search template alter1.tmpl set schema alter2;
+alter text search dictionary alter1.dict set schema alter2;
-- this should succeed because nothing is left in alter1
drop schema alter1;
insert into alter2.t1(f2) values(13);
-- clean up
drop schema alter2 cascade;
-NOTICE: drop cascades to composite type alter2.ctype
-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 table alter2.t1 column f1
-NOTICE: drop cascades to table alter2.t1
-NOTICE: drop cascades to constraint t1_f2_check on table alter2.t1
+NOTICE: drop cascades to 13 other objects
+DETAIL: drop cascades to table alter2.t1
+drop cascades to view alter2.v1
+drop cascades to function alter2.plus1(integer)
+drop cascades to type alter2.posint
+drop cascades to operator family alter2.ctype_hash_ops for access method hash
+drop cascades to type alter2.ctype
+drop cascades to function alter2.same(alter2.ctype,alter2.ctype)
+drop cascades to operator alter2.=(alter2.ctype,alter2.ctype)
+drop cascades to conversion ascii_to_utf8
+drop cascades to text search parser prs
+drop cascades to text search configuration cfg
+drop cascades to text search template tmpl
+drop cascades to text search dictionary dict
+--
+-- composite types
+--
+CREATE TYPE test_type AS (a int);
+\d test_type
+Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+
+ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
+ERROR: relation "nosuchtype" does not exist
+ALTER TYPE test_type ADD ATTRIBUTE b text;
+\d test_type
+Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | text |
+
+ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
+ERROR: column "b" of relation "test_type" already exists
+ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
+\d test_type
+ Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+-------------------+-----------
+ a | integer |
+ b | character varying |
+
+ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
+\d test_type
+Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+
+ALTER TYPE test_type DROP ATTRIBUTE b;
+\d test_type
+Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+
+ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
+ERROR: column "c" of relation "test_type" does not exist
+ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
+NOTICE: column "c" of relation "test_type" does not exist, skipping
+ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
+\d test_type
+Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+---------+-----------
+ d | boolean |
+
+ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
+ERROR: column "a" does not exist
+ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
+\d test_type
+Composite type "public.test_type"
+ Column | Type | Modifiers
+--------+---------+-----------
+ dd | boolean |
+
+DROP TYPE test_type;
+CREATE TYPE test_type1 AS (a int, b text);
+CREATE TABLE test_tbl1 (x int, y test_type1);
+ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
+ERROR: cannot alter type "test_type1" because column "test_tbl1.y" uses it
+CREATE TYPE test_type2 AS (a int, b text);
+CREATE TABLE test_tbl2 OF test_type2;
+CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
+\d test_type2
+Composite type "public.test_type2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | text |
+
+\d test_tbl2
+ Table "public.test_tbl2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | text |
+Number of child tables: 1 (Use \d+ to list them.)
+Typed table of type: test_type2
+
+ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
+ERROR: cannot alter type "test_type2" because it is the type of a typed table
+HINT: Use ALTER ... CASCADE to alter the typed tables too.
+ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
+\d test_type2
+Composite type "public.test_type2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | text |
+ c | text |
+
+\d test_tbl2
+ Table "public.test_tbl2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | text |
+ c | text |
+Number of child tables: 1 (Use \d+ to list them.)
+Typed table of type: test_type2
+
+ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
+ERROR: cannot alter type "test_type2" because it is the type of a typed table
+HINT: Use ALTER ... CASCADE to alter the typed tables too.
+ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
+\d test_type2
+ Composite type "public.test_type2"
+ Column | Type | Modifiers
+--------+-------------------+-----------
+ a | integer |
+ b | character varying |
+ c | text |
+
+\d test_tbl2
+ Table "public.test_tbl2"
+ Column | Type | Modifiers
+--------+-------------------+-----------
+ a | integer |
+ b | character varying |
+ c | text |
+Number of child tables: 1 (Use \d+ to list them.)
+Typed table of type: test_type2
+
+ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
+ERROR: cannot alter type "test_type2" because it is the type of a typed table
+HINT: Use ALTER ... CASCADE to alter the typed tables too.
+ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
+\d test_type2
+Composite type "public.test_type2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ c | text |
+
+\d test_tbl2
+ Table "public.test_tbl2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ c | text |
+Number of child tables: 1 (Use \d+ to list them.)
+Typed table of type: test_type2
+
+ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
+ERROR: cannot alter type "test_type2" because it is the type of a typed table
+HINT: Use ALTER ... CASCADE to alter the typed tables too.
+ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
+\d test_type2
+Composite type "public.test_type2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ aa | integer |
+ c | text |
+
+\d test_tbl2
+ Table "public.test_tbl2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ aa | integer |
+ c | text |
+Number of child tables: 1 (Use \d+ to list them.)
+Typed table of type: test_type2
+
+\d test_tbl2_subclass
+Table "public.test_tbl2_subclass"
+ Column | Type | Modifiers
+--------+---------+-----------
+ aa | integer |
+ c | text |
+Inherits: test_tbl2
+
+DROP TABLE test_tbl2_subclass;
+-- This test isn't that interesting on its own, but the purpose is to leave
+-- behind a table to test pg_upgrade with. The table has a composite type
+-- column in it, and the composite type has a dropped attribute.
+CREATE TYPE test_type3 AS (a int);
+CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
+ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
+CREATE TYPE test_type_empty AS ();
+DROP TYPE test_type_empty;
+--
+-- typed tables: OF / NOT OF
+--
+CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
+ALTER TYPE tt_t0 DROP ATTRIBUTE z;
+CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
+CREATE TABLE tt1 (x int, y bigint); -- wrong base type
+CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
+CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
+CREATE TABLE tt4 (x int); -- too few columns
+CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
+CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
+CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
+ALTER TABLE tt7 DROP q; -- OK
+ALTER TABLE tt0 OF tt_t0;
+ALTER TABLE tt1 OF tt_t0;
+ERROR: table "tt1" has different type for column "y"
+ALTER TABLE tt2 OF tt_t0;
+ERROR: table "tt2" has different type for column "y"
+ALTER TABLE tt3 OF tt_t0;
+ERROR: table has column "y" where type requires "x"
+ALTER TABLE tt4 OF tt_t0;
+ERROR: table is missing column "y"
+ALTER TABLE tt5 OF tt_t0;
+ERROR: table has extra column "z"
+ALTER TABLE tt6 OF tt_t0;
+ERROR: typed tables cannot inherit
+ALTER TABLE tt7 OF tt_t0;
+CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
+ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
+ALTER TABLE tt7 NOT OF;
+\d tt7
+ Table "public.tt7"
+ Column | Type | Modifiers
+--------+--------------+-----------
+ x | integer |
+ y | numeric(8,2) |
+
+-- make sure we can drop a constraint on the parent but it remains on the child
+CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
+CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
+ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
+-- should fail
+INSERT INTO test_drop_constr_child (c) VALUES (NULL);
+ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
+DETAIL: Failing row contains (null).
+DROP TABLE test_drop_constr_parent CASCADE;
+NOTICE: drop cascades to table test_drop_constr_child
+--
+-- IF EXISTS test
+--
+ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
+NOTICE: relation "tt8" does not exist, skipping
+ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
+NOTICE: relation "tt8" does not exist, skipping
+ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
+NOTICE: relation "tt8" does not exist, skipping
+ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
+NOTICE: relation "tt8" does not exist, skipping
+ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
+NOTICE: relation "tt8" does not exist, skipping
+ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
+NOTICE: relation "tt8" does not exist, skipping
+CREATE TABLE tt8(a int);
+CREATE SCHEMA alter2;
+ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
+ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
+ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
+ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
+ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
+ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
+\d alter2.tt8
+ Table "alter2.tt8"
+ Column | Type | Modifiers
+--------+---------+--------------------
+ a | integer |
+ f1 | integer | not null default 0
+Indexes:
+ "xxx" PRIMARY KEY, btree (f1)
+Check constraints:
+ "tt8_f_check" CHECK (f1 >= 0 AND f1 <= 10)
+
+DROP TABLE alter2.tt8;
+DROP SCHEMA alter2;
+-- Check that comments on constraints and indexes are not lost at ALTER TABLE.
+CREATE TABLE comment_test (
+ id int,
+ positive_col int CHECK (positive_col > 0),
+ indexed_col int,
+ CONSTRAINT comment_test_pk PRIMARY KEY (id));
+CREATE INDEX comment_test_index ON comment_test(indexed_col);
+COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
+COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
+COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
+COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
+COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
+SELECT col_description('comment_test'::regclass, 1) as comment;
+ comment
+-----------------------------
+ Column 'id' on comment_test
+(1 row)
+
+SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
+ index | comment
+--------------------+-----------------------------------------------
+ comment_test_index | Simple index on comment_test
+ comment_test_pk | Index backing the PRIMARY KEY of comment_test
+(2 rows)
+
+SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
+ constraint | comment
+---------------------------------+-----------------------------------------------
+ comment_test_pk | PRIMARY KEY constraint of comment_test
+ comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
+(2 rows)
+
+-- Change the datatype of all the columns. ALTER TABLE is optimized to not
+-- rebuild an index if the new data type is binary compatible with the old
+-- one. Check do a dummy ALTER TABLE that doesn't change the datatype
+-- first, to test that no-op codepath, and another one that does.
+ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int;
+ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text;
+ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int;
+ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
+ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int;
+ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint;
+-- Check that the comments are intact.
+SELECT col_description('comment_test'::regclass, 1) as comment;
+ comment
+-----------------------------
+ Column 'id' on comment_test
+(1 row)
+
+SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
+ index | comment
+--------------------+-----------------------------------------------
+ comment_test_index | Simple index on comment_test
+ comment_test_pk | Index backing the PRIMARY KEY of comment_test
+(2 rows)
+
+SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
+ constraint | comment
+---------------------------------+-----------------------------------------------
+ comment_test_pk | PRIMARY KEY constraint of comment_test
+ comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
+(2 rows)
+
+-- Check that we map relation oids to filenodes and back correctly. Only
+-- display bad mappings so the test output doesn't change all the time. A
+-- filenode function call can return NULL for a relation dropped concurrently
+-- with the call's surrounding query, so ignore a NULL mapped_oid for
+-- relations that no longer exist after all calls finish.
+CREATE TEMP TABLE filenode_mapping AS
+SELECT
+ oid, mapped_oid, reltablespace, relfilenode, relname
+FROM pg_class,
+ pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid
+WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid;
+SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid
+WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
+ oid | mapped_oid | reltablespace | relfilenode | relname
+-----+------------+---------------+-------------+---------
+(0 rows)
+
+-- Checks on creating and manipulation of user defined relations in
+-- pg_catalog.
+--
+-- XXX: It would be useful to add checks around trying to manipulate
+-- catalog tables, but that might have ugly consequences when run
+-- against an existing server with allow_system_table_mods = on.
+SHOW allow_system_table_mods;
+ allow_system_table_mods
+-------------------------
+ off
+(1 row)
+
+-- disallowed because of search_path issues with pg_dump
+CREATE TABLE pg_catalog.new_system_table();
+ERROR: permission denied to create "pg_catalog.new_system_table"
+DETAIL: System catalog modifications are currently disallowed.
+-- instead create in public first, move to catalog
+CREATE TABLE new_system_table(id serial primary key, othercol text);
+ALTER TABLE new_system_table SET SCHEMA pg_catalog;
+-- XXX: it's currently impossible to move relations out of pg_catalog
+ALTER TABLE new_system_table SET SCHEMA public;
+ERROR: cannot remove dependency on schema pg_catalog because it is a system object
+-- move back, will currently error out, already there
+ALTER TABLE new_system_table SET SCHEMA pg_catalog;
+ERROR: table new_system_table is already in schema "pg_catalog"
+ALTER TABLE new_system_table RENAME TO old_system_table;
+CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
+INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
+UPDATE old_system_table SET id = -id;
+DELETE FROM old_system_table WHERE othercol = 'somedata';
+TRUNCATE old_system_table;
+ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
+ALTER TABLE old_system_table DROP COLUMN othercol;
+DROP TABLE old_system_table;
+-- set logged
+CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+-- check relpersistence of an unlogged table
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+------------------+---------+----------------
+ toast index | i | u
+ toast table | t | u
+ unlogged1 | r | u
+ unlogged1_f1_seq | S | p
+ unlogged1_pkey | i | u
+(5 rows)
+
+CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
+CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
+ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
+ERROR: cannot change status of table unlogged2 to logged
+DETAIL: Table unlogged2 references unlogged table unlogged1.
+ALTER TABLE unlogged1 SET LOGGED;
+-- check relpersistence of an unlogged table after changing to permament
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+------------------+---------+----------------
+ toast index | i | p
+ toast table | t | p
+ unlogged1 | r | p
+ unlogged1_f1_seq | S | p
+ unlogged1_pkey | i | p
+(5 rows)
+
+ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
+DROP TABLE unlogged3;
+DROP TABLE unlogged2;
+DROP TABLE unlogged1;
+-- set unlogged
+CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+-- check relpersistence of a permanent table
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+----------------+---------+----------------
+ logged1 | r | p
+ logged1_f1_seq | S | p
+ logged1_pkey | i | p
+ toast index | i | p
+ toast table | t | p
+(5 rows)
+
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
+ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
+ERROR: cannot change status of table logged1 to unlogged
+DETAIL: Logged table logged2 is referenced by table logged1.
+ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
+ALTER TABLE logged2 SET UNLOGGED;
+ALTER TABLE logged1 SET UNLOGGED;
+-- check relpersistence of a permanent table after changing to unlogged
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
+UNION ALL
+SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+UNION ALL
+SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1'
+ORDER BY relname;
+ relname | relkind | relpersistence
+----------------+---------+----------------
+ logged1 | r | u
+ logged1_f1_seq | S | p
+ logged1_pkey | i | u
+ toast index | i | u
+ toast table | t | u
+(5 rows)
+
+ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
+DROP TABLE logged3;
+DROP TABLE logged2;
+DROP TABLE logged1;
+-- test ADD COLUMN IF NOT EXISTS
+CREATE TABLE test_add_column(c1 integer);
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN c2 integer;
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN c2 integer; -- fail because c2 already exists
+ERROR: column "c2" of relation "test_add_column" already exists
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
+NOTICE: column "c2" of relation "test_add_column" already exists, skipping
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN c2 integer, -- fail because c2 already exists
+ ADD COLUMN c3 integer;
+ERROR: column "c2" of relation "test_add_column" already exists
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
+ ADD COLUMN c3 integer; -- fail because c3 already exists
+NOTICE: column "c2" of relation "test_add_column" already exists, skipping
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+ c3 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
+ ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists
+NOTICE: column "c2" of relation "test_add_column" already exists, skipping
+NOTICE: column "c3" of relation "test_add_column" already exists, skipping
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+ c3 | integer |
+
+ALTER TABLE test_add_column
+ ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
+ ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists
+ ADD COLUMN c4 integer;
+NOTICE: column "c2" of relation "test_add_column" already exists, skipping
+NOTICE: column "c3" of relation "test_add_column" already exists, skipping
+\d test_add_column
+Table "public.test_add_column"
+ Column | Type | Modifiers
+--------+---------+-----------
+ c1 | integer |
+ c2 | integer |
+ c3 | integer |
+ c4 | integer |
+
+DROP TABLE test_add_column;
projects / postgresql / blobdiff