5 CREATE TABLE tmp (initial int4);
6 COMMENT ON TABLE tmp_wrong IS 'table comment';
7 ERROR: relation "tmp_wrong" does not exist
8 COMMENT ON TABLE tmp IS 'table comment';
9 COMMENT ON TABLE tmp IS NULL;
10 ALTER TABLE tmp ADD COLUMN a int4 default 3;
11 ALTER TABLE tmp ADD COLUMN b name;
12 ALTER TABLE tmp ADD COLUMN c text;
13 ALTER TABLE tmp ADD COLUMN d float8;
14 ALTER TABLE tmp ADD COLUMN e float4;
15 ALTER TABLE tmp ADD COLUMN f int2;
16 ALTER TABLE tmp ADD COLUMN g polygon;
17 ALTER TABLE tmp ADD COLUMN h abstime;
18 ALTER TABLE tmp ADD COLUMN i char;
19 ALTER TABLE tmp ADD COLUMN j abstime[];
20 ALTER TABLE tmp ADD COLUMN k int4;
21 ALTER TABLE tmp ADD COLUMN l tid;
22 ALTER TABLE tmp ADD COLUMN m xid;
23 ALTER TABLE tmp ADD COLUMN n oidvector;
24 --ALTER TABLE tmp ADD COLUMN o lock;
25 ALTER TABLE tmp ADD COLUMN p smgr;
26 ALTER TABLE tmp ADD COLUMN q point;
27 ALTER TABLE tmp ADD COLUMN r lseg;
28 ALTER TABLE tmp ADD COLUMN s path;
29 ALTER TABLE tmp ADD COLUMN t box;
30 ALTER TABLE tmp ADD COLUMN u tinterval;
31 ALTER TABLE tmp ADD COLUMN v timestamp;
32 ALTER TABLE tmp ADD COLUMN w interval;
33 ALTER TABLE tmp ADD COLUMN x float8[];
34 ALTER TABLE tmp ADD COLUMN y float4[];
35 ALTER TABLE tmp ADD COLUMN z int2[];
36 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
38 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
39 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
40 314159, '(1,1)', '512',
41 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
42 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
43 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
45 initial | 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
46 ---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
47 | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 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) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
51 -- the wolf bug - schema mods caused inconsistent row descriptors
55 ALTER TABLE tmp ADD COLUMN a int4;
56 ALTER TABLE tmp ADD COLUMN b name;
57 ALTER TABLE tmp ADD COLUMN c text;
58 ALTER TABLE tmp ADD COLUMN d float8;
59 ALTER TABLE tmp ADD COLUMN e float4;
60 ALTER TABLE tmp ADD COLUMN f int2;
61 ALTER TABLE tmp ADD COLUMN g polygon;
62 ALTER TABLE tmp ADD COLUMN h abstime;
63 ALTER TABLE tmp ADD COLUMN i char;
64 ALTER TABLE tmp ADD COLUMN j abstime[];
65 ALTER TABLE tmp ADD COLUMN k int4;
66 ALTER TABLE tmp ADD COLUMN l tid;
67 ALTER TABLE tmp ADD COLUMN m xid;
68 ALTER TABLE tmp ADD COLUMN n oidvector;
69 --ALTER TABLE tmp ADD COLUMN o lock;
70 ALTER TABLE tmp ADD COLUMN p smgr;
71 ALTER TABLE tmp ADD COLUMN q point;
72 ALTER TABLE tmp ADD COLUMN r lseg;
73 ALTER TABLE tmp ADD COLUMN s path;
74 ALTER TABLE tmp ADD COLUMN t box;
75 ALTER TABLE tmp ADD COLUMN u tinterval;
76 ALTER TABLE tmp ADD COLUMN v timestamp;
77 ALTER TABLE tmp ADD COLUMN w interval;
78 ALTER TABLE tmp ADD COLUMN x float8[];
79 ALTER TABLE tmp ADD COLUMN y float4[];
80 ALTER TABLE tmp ADD COLUMN z int2[];
81 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
83 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
84 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
85 314159, '(1,1)', '512',
86 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
87 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
88 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
90 initial | 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
91 ---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
92 | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 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) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
97 -- rename - check on both non-temp and temp tables
99 CREATE TABLE tmp (regtable int);
100 CREATE TEMP TABLE tmp (tmptable int);
101 ALTER TABLE tmp RENAME TO tmp_new;
107 SELECT * FROM tmp_new;
112 ALTER TABLE tmp RENAME TO tmp_new2;
113 SELECT * FROM tmp; -- should fail
114 ERROR: relation "tmp" does not exist
115 LINE 1: SELECT * FROM tmp;
117 SELECT * FROM tmp_new;
122 SELECT * FROM tmp_new2;
129 -- ALTER TABLE ... RENAME on non-table relations
130 -- renaming indexes (FIXME: this should probably test the index's functionality)
131 ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
132 ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
134 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
135 ALTER TABLE tmp_view RENAME TO tmp_view_new;
136 -- hack to ensure we get an indexscan here
138 set enable_seqscan to off;
139 set enable_bitmapscan to off;
141 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
151 reset enable_seqscan;
152 reset enable_bitmapscan;
153 DROP VIEW tmp_view_new;
154 -- toast-like relation name
155 alter table stud_emp rename to pg_toast_stud_emp;
156 alter table pg_toast_stud_emp rename to stud_emp;
157 -- FOREIGN KEY CONSTRAINT adding TEST
158 CREATE TABLE tmp2 (a int primary key);
159 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2"
160 CREATE TABLE tmp3 (a int, b int);
161 CREATE TABLE tmp4 (a int, b int, unique(a,b));
162 NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_b_key" for table "tmp4"
163 CREATE TABLE tmp5 (a int, b int);
164 -- Insert rows into tmp2 (pktable)
165 INSERT INTO tmp2 values (1);
166 INSERT INTO tmp2 values (2);
167 INSERT INTO tmp2 values (3);
168 INSERT INTO tmp2 values (4);
169 -- Insert rows into tmp3
170 INSERT INTO tmp3 values (1,10);
171 INSERT INTO tmp3 values (1,20);
172 INSERT INTO tmp3 values (5,50);
173 -- Try (and fail) to add constraint due to invalid source columns
174 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
175 ERROR: column "c" referenced in foreign key constraint does not exist
176 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
177 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
178 ERROR: column "b" referenced in foreign key constraint does not exist
179 -- Try (and fail) to add constraint due to invalid data
180 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
181 ERROR: insert or update on table "tmp3" violates foreign key constraint "tmpconstr"
182 DETAIL: Key (a)=(5) is not present in table "tmp2".
183 -- Delete failing row
184 DELETE FROM tmp3 where a=5;
186 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
187 ALTER TABLE tmp3 drop constraint tmpconstr;
188 INSERT INTO tmp3 values (5,50);
189 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
190 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
191 ALTER TABLE tmp3 validate constraint tmpconstr;
192 ERROR: insert or update on table "tmp3" violates foreign key constraint "tmpconstr"
193 DETAIL: Key (a)=(5) is not present in table "tmp2".
194 -- Delete failing row
195 DELETE FROM tmp3 where a=5;
196 -- Try (and succeed) and repeat to show it works on already valid constraint
197 ALTER TABLE tmp3 validate constraint tmpconstr;
198 ALTER TABLE tmp3 validate constraint tmpconstr;
199 -- Try a non-verified CHECK constraint
200 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
201 ERROR: check constraint "b_greater_than_ten" is violated by some row
202 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
203 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
204 ERROR: check constraint "b_greater_than_ten" is violated by some row
205 DELETE FROM tmp3 WHERE NOT b > 10;
206 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
207 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
208 -- Test inherited NOT VALID CHECK constraints
215 CREATE TABLE tmp6 () INHERITS (tmp3);
216 CREATE TABLE tmp7 () INHERITS (tmp3);
217 INSERT INTO tmp6 VALUES (6, 30), (7, 16);
218 ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
219 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- fails
220 ERROR: check constraint "b_le_20" is violated by some row
221 DELETE FROM tmp6 WHERE b > 20;
222 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
223 -- An already validated constraint must not be revalidated
224 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
225 INSERT INTO tmp7 VALUES (8, 18);
226 ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
228 ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
229 NOTICE: merging constraint "identity" with inherited definition
230 ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
233 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
235 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
236 ERROR: there is no unique constraint matching given keys for referenced table "tmp4"
243 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
244 -- exclusion until validated
245 set constraint_exclusion TO 'partition';
246 create table nv_parent (d date);
247 create table nv_child_2010 () inherits (nv_parent);
248 create table nv_child_2011 () inherits (nv_parent);
249 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
250 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
251 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
253 ---------------------------------------------------------------------------------
256 -> Seq Scan on nv_parent
257 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
258 -> Seq Scan on nv_child_2010 nv_parent
259 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
260 -> Seq Scan on nv_child_2011 nv_parent
261 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
264 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
265 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
267 ---------------------------------------------------------------------------------
270 -> Seq Scan on nv_parent
271 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
272 -> Seq Scan on nv_child_2010 nv_parent
273 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
274 -> Seq Scan on nv_child_2011 nv_parent
275 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
278 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
280 ---------------------------------------------------------------------------------
283 -> Seq Scan on nv_parent
284 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
285 -> Seq Scan on nv_child_2010 nv_parent
286 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
287 -> Seq Scan on nv_child_2011 nv_parent
288 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
289 -> Seq Scan on nv_child_2009 nv_parent
290 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
293 -- after validation, the constraint should be used
294 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
295 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
297 ---------------------------------------------------------------------------------
300 -> Seq Scan on nv_parent
301 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
302 -> Seq Scan on nv_child_2010 nv_parent
303 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
304 -> Seq Scan on nv_child_2009 nv_parent
305 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
308 -- Foreign key adding test with mixed types
309 -- Note: these tables are TEMP to avoid name conflicts when this test
310 -- is run in parallel with foreign_key.sql.
311 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
312 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
313 INSERT INTO PKTABLE VALUES(42);
314 CREATE TEMP TABLE FKTABLE (ftest1 inet);
315 -- This next should fail, because int=inet does not exist
316 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
317 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
318 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
319 -- This should also fail for the same reason, but here we
320 -- give the column name
321 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
322 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
323 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
325 -- This should succeed, even though they are different types,
326 -- because int=int8 exists and is a member of the integer opfamily
327 CREATE TEMP TABLE FKTABLE (ftest1 int8);
328 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
329 -- Check it actually works
330 INSERT INTO FKTABLE VALUES(42); -- should succeed
331 INSERT INTO FKTABLE VALUES(43); -- should fail
332 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
333 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
335 -- This should fail, because we'd have to cast numeric to int which is
336 -- not an implicit coercion (or use numeric=numeric, but that's not part
337 -- of the integer opfamily)
338 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
339 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
340 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
341 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
344 -- On the other hand, this should work because int implicitly promotes to
345 -- numeric, and we allow promotion on the FK side
346 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
347 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
348 INSERT INTO PKTABLE VALUES(42);
349 CREATE TEMP TABLE FKTABLE (ftest1 int);
350 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
351 -- Check it actually works
352 INSERT INTO FKTABLE VALUES(42); -- should succeed
353 INSERT INTO FKTABLE VALUES(43); -- should fail
354 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
355 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
358 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
359 PRIMARY KEY(ptest1, ptest2));
360 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
361 -- This should fail, because we just chose really odd types
362 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
363 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
364 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
365 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
367 -- Again, so should this...
368 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
369 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
370 references pktable(ptest1, ptest2);
371 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
372 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
374 -- This fails because we mixed up the column ordering
375 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
376 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
377 references pktable(ptest2, ptest1);
378 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
379 DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
381 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
382 references pktable(ptest1, ptest2);
383 ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
384 DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
385 -- temp tables should go away by themselves, need not drop them.
386 -- test check constraint adding
387 create table atacc1 ( test int );
388 -- add a check constraint
389 alter table atacc1 add constraint atacc_test1 check (test>3);
391 insert into atacc1 (test) values (2);
392 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
394 insert into atacc1 (test) values (4);
396 -- let's do one where the check fails when added
397 create table atacc1 ( test int );
398 -- insert a soon to be failing row
399 insert into atacc1 (test) values (2);
400 -- add a check constraint (fails)
401 alter table atacc1 add constraint atacc_test1 check (test>3);
402 ERROR: check constraint "atacc_test1" is violated by some row
403 insert into atacc1 (test) values (4);
405 -- let's do one where the check fails because the column doesn't exist
406 create table atacc1 ( test int );
407 -- add a check constraint (fails)
408 alter table atacc1 add constraint atacc_test1 check (test1>3);
409 ERROR: column "test1" does not exist
411 -- something a little more complicated
412 create table atacc1 ( test int, test2 int, test3 int);
413 -- add a check constraint (fails)
414 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
416 insert into atacc1 (test,test2,test3) values (4,4,2);
417 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
419 insert into atacc1 (test,test2,test3) values (4,4,5);
421 -- lets do some naming tests
422 create table atacc1 (test int check (test>3), test2 int);
423 alter table atacc1 add check (test2>test);
424 -- should fail for $2
425 insert into atacc1 (test2, test) values (3, 4);
426 ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
428 -- inheritance related tests
429 create table atacc1 (test int);
430 create table atacc2 (test2 int);
431 create table atacc3 (test3 int) inherits (atacc1, atacc2);
432 alter table atacc2 add constraint foo check (test2>0);
433 -- fail and then succeed on atacc2
434 insert into atacc2 (test2) values (-3);
435 ERROR: new row for relation "atacc2" violates check constraint "foo"
436 insert into atacc2 (test2) values (3);
437 -- fail and then succeed on atacc3
438 insert into atacc3 (test2) values (-3);
439 ERROR: new row for relation "atacc3" violates check constraint "foo"
440 insert into atacc3 (test2) values (3);
444 -- same things with one created with INHERIT
445 create table atacc1 (test int);
446 create table atacc2 (test2 int);
447 create table atacc3 (test3 int) inherits (atacc1, atacc2);
448 alter table atacc3 no inherit atacc2;
450 alter table atacc3 no inherit atacc2;
451 ERROR: relation "atacc2" is not a parent of relation "atacc3"
452 -- make sure it really isn't a child
453 insert into atacc3 (test2) values (3);
454 select test2 from atacc2;
459 -- fail due to missing constraint
460 alter table atacc2 add constraint foo check (test2>0);
461 alter table atacc3 inherit atacc2;
462 ERROR: child table is missing constraint "foo"
463 -- fail due to missing column
464 alter table atacc3 rename test2 to testx;
465 alter table atacc3 inherit atacc2;
466 ERROR: child table is missing column "test2"
467 -- fail due to mismatched data type
468 alter table atacc3 add test2 bool;
469 alter table atacc3 inherit atacc2;
470 ERROR: child table "atacc3" has different type for column "test2"
471 alter table atacc3 drop test2;
473 alter table atacc3 add test2 int;
474 update atacc3 set test2 = 4 where test2 is null;
475 alter table atacc3 add constraint foo check (test2>0);
476 alter table atacc3 inherit atacc2;
477 -- fail due to duplicates and circular inheritance
478 alter table atacc3 inherit atacc2;
479 ERROR: relation "atacc2" would be inherited from more than once
480 alter table atacc2 inherit atacc3;
481 ERROR: circular inheritance not allowed
482 DETAIL: "atacc3" is already a child of "atacc2".
483 alter table atacc2 inherit atacc2;
484 ERROR: circular inheritance not allowed
485 DETAIL: "atacc2" is already a child of "atacc2".
486 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
487 select test2 from atacc2;
493 drop table atacc2 cascade;
494 NOTICE: drop cascades to table atacc3
496 -- adding only to a parent is disallowed as of 8.4
497 create table atacc1 (test int);
498 create table atacc2 (test2 int) inherits (atacc1);
500 alter table only atacc1 add constraint foo check (test>0);
501 ERROR: constraint must be added to child tables too
503 alter table only atacc2 add constraint foo check (test>0);
504 -- check constraint not there on parent
505 insert into atacc1 (test) values (-3);
506 insert into atacc1 (test) values (3);
507 -- check constraint is there on child
508 insert into atacc2 (test) values (-3);
509 ERROR: new row for relation "atacc2" violates check constraint "foo"
510 insert into atacc2 (test) values (3);
513 -- test unique constraint adding
514 create table atacc1 ( test int ) with oids;
515 -- add a unique constraint
516 alter table atacc1 add constraint atacc_test1 unique (test);
517 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
518 -- insert first value
519 insert into atacc1 (test) values (2);
521 insert into atacc1 (test) values (2);
522 ERROR: duplicate key value violates unique constraint "atacc_test1"
523 DETAIL: Key (test)=(2) already exists.
525 insert into atacc1 (test) values (4);
526 -- try adding a unique oid constraint
527 alter table atacc1 add constraint atacc_oid1 unique(oid);
528 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"
529 -- try to create duplicates via alter table using - should fail
530 alter table atacc1 alter column test type integer using 0;
531 ERROR: could not create unique index "atacc_test1"
532 DETAIL: Key (test)=(0) is duplicated.
534 -- let's do one where the unique constraint fails when added
535 create table atacc1 ( test int );
536 -- insert soon to be failing rows
537 insert into atacc1 (test) values (2);
538 insert into atacc1 (test) values (2);
539 -- add a unique constraint (fails)
540 alter table atacc1 add constraint atacc_test1 unique (test);
541 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
542 ERROR: could not create unique index "atacc_test1"
543 DETAIL: Key (test)=(2) is duplicated.
544 insert into atacc1 (test) values (3);
546 -- let's do one where the unique constraint fails
547 -- because the column doesn't exist
548 create table atacc1 ( test int );
549 -- add a unique constraint (fails)
550 alter table atacc1 add constraint atacc_test1 unique (test1);
551 ERROR: column "test1" named in key does not exist
553 -- something a little more complicated
554 create table atacc1 ( test int, test2 int);
555 -- add a unique constraint
556 alter table atacc1 add constraint atacc_test1 unique (test, test2);
557 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
558 -- insert initial value
559 insert into atacc1 (test,test2) values (4,4);
561 insert into atacc1 (test,test2) values (4,4);
562 ERROR: duplicate key value violates unique constraint "atacc_test1"
563 DETAIL: Key (test, test2)=(4, 4) already exists.
564 -- should all succeed
565 insert into atacc1 (test,test2) values (4,5);
566 insert into atacc1 (test,test2) values (5,4);
567 insert into atacc1 (test,test2) values (5,5);
569 -- lets do some naming tests
570 create table atacc1 (test int, test2 int, unique(test));
571 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
572 alter table atacc1 add unique (test2);
573 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
574 -- should fail for @@ second one @@
575 insert into atacc1 (test2, test) values (3, 3);
576 insert into atacc1 (test2, test) values (2, 3);
577 ERROR: duplicate key value violates unique constraint "atacc1_test_key"
578 DETAIL: Key (test)=(3) already exists.
580 -- test primary key constraint adding
581 create table atacc1 ( test int ) with oids;
582 -- add a primary key constraint
583 alter table atacc1 add constraint atacc_test1 primary key (test);
584 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
585 -- insert first value
586 insert into atacc1 (test) values (2);
588 insert into atacc1 (test) values (2);
589 ERROR: duplicate key value violates unique constraint "atacc_test1"
590 DETAIL: Key (test)=(2) already exists.
592 insert into atacc1 (test) values (4);
593 -- inserting NULL should fail
594 insert into atacc1 (test) values(NULL);
595 ERROR: null value in column "test" violates not-null constraint
596 -- try adding a second primary key (should fail)
597 alter table atacc1 add constraint atacc_oid1 primary key(oid);
598 ERROR: multiple primary keys for table "atacc1" are not allowed
599 -- drop first primary key constraint
600 alter table atacc1 drop constraint atacc_test1 restrict;
601 -- try adding a primary key on oid (should succeed)
602 alter table atacc1 add constraint atacc_oid1 primary key(oid);
603 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
605 -- let's do one where the primary key constraint fails when added
606 create table atacc1 ( test int );
607 -- insert soon to be failing rows
608 insert into atacc1 (test) values (2);
609 insert into atacc1 (test) values (2);
610 -- add a primary key (fails)
611 alter table atacc1 add constraint atacc_test1 primary key (test);
612 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
613 ERROR: could not create unique index "atacc_test1"
614 DETAIL: Key (test)=(2) is duplicated.
615 insert into atacc1 (test) values (3);
617 -- let's do another one where the primary key constraint fails when added
618 create table atacc1 ( test int );
619 -- insert soon to be failing row
620 insert into atacc1 (test) values (NULL);
621 -- add a primary key (fails)
622 alter table atacc1 add constraint atacc_test1 primary key (test);
623 ERROR: column "test" contains null values
624 insert into atacc1 (test) values (3);
626 -- let's do one where the primary key constraint fails
627 -- because the column doesn't exist
628 create table atacc1 ( test int );
629 -- add a primary key constraint (fails)
630 alter table atacc1 add constraint atacc_test1 primary key (test1);
631 ERROR: column "test1" named in key does not exist
633 -- adding a new column as primary key to a non-empty table.
634 -- should fail unless the column has a non-null default value.
635 create table atacc1 ( test int );
636 insert into atacc1 (test) values (0);
637 -- add a primary key column without a default (fails).
638 alter table atacc1 add column test2 int primary key;
639 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
640 ERROR: column "test2" contains null values
641 -- now add a primary key column with a default (succeeds).
642 alter table atacc1 add column test2 int default 0 primary key;
643 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
645 -- something a little more complicated
646 create table atacc1 ( test int, test2 int);
647 -- add a primary key constraint
648 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
649 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
650 -- try adding a second primary key - should fail
651 alter table atacc1 add constraint atacc_test2 primary key (test);
652 ERROR: multiple primary keys for table "atacc1" are not allowed
653 -- insert initial value
654 insert into atacc1 (test,test2) values (4,4);
656 insert into atacc1 (test,test2) values (4,4);
657 ERROR: duplicate key value violates unique constraint "atacc_test1"
658 DETAIL: Key (test, test2)=(4, 4) already exists.
659 insert into atacc1 (test,test2) values (NULL,3);
660 ERROR: null value in column "test" violates not-null constraint
661 insert into atacc1 (test,test2) values (3, NULL);
662 ERROR: null value in column "test2" violates not-null constraint
663 insert into atacc1 (test,test2) values (NULL,NULL);
664 ERROR: null value in column "test" violates not-null constraint
665 -- should all succeed
666 insert into atacc1 (test,test2) values (4,5);
667 insert into atacc1 (test,test2) values (5,4);
668 insert into atacc1 (test,test2) values (5,5);
670 -- lets do some naming tests
671 create table atacc1 (test int, test2 int, primary key(test));
672 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
673 -- only first should succeed
674 insert into atacc1 (test2, test) values (3, 3);
675 insert into atacc1 (test2, test) values (2, 3);
676 ERROR: duplicate key value violates unique constraint "atacc1_pkey"
677 DETAIL: Key (test)=(3) already exists.
678 insert into atacc1 (test2, test) values (1, NULL);
679 ERROR: null value in column "test" violates not-null constraint
681 -- alter table / alter column [set/drop] not null tests
682 -- try altering system catalogs, should fail
683 alter table pg_class alter column relname drop not null;
684 ERROR: permission denied: "pg_class" is a system catalog
685 alter table pg_class alter relname set not null;
686 ERROR: permission denied: "pg_class" is a system catalog
687 -- try altering non-existent table, should fail
688 alter table non_existent alter column bar set not null;
689 ERROR: relation "non_existent" does not exist
690 alter table non_existent alter column bar drop not null;
691 ERROR: relation "non_existent" does not exist
692 -- test setting columns to null and not null and vice versa
693 -- test checking for null values and primary key
694 create table atacc1 (test int not null) with oids;
695 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
696 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
697 alter table atacc1 alter column test drop not null;
698 ERROR: column "test" is in a primary key
699 alter table atacc1 drop constraint "atacc1_pkey";
700 alter table atacc1 alter column test drop not null;
701 insert into atacc1 values (null);
702 alter table atacc1 alter test set not null;
703 ERROR: column "test" contains null values
705 alter table atacc1 alter test set not null;
706 -- try altering a non-existent column, should fail
707 alter table atacc1 alter bar set not null;
708 ERROR: column "bar" of relation "atacc1" does not exist
709 alter table atacc1 alter bar drop not null;
710 ERROR: column "bar" of relation "atacc1" does not exist
711 -- try altering the oid column, should fail
712 alter table atacc1 alter oid set not null;
713 ERROR: cannot alter system column "oid"
714 alter table atacc1 alter oid drop not null;
715 ERROR: cannot alter system column "oid"
716 -- try creating a view and altering that, should fail
717 create view myview as select * from atacc1;
718 alter table myview alter column test drop not null;
719 ERROR: "myview" is not a table or foreign table
720 alter table myview alter column test set not null;
721 ERROR: "myview" is not a table or foreign table
725 create table parent (a int);
726 create table child (b varchar(255)) inherits (parent);
727 alter table parent alter a set not null;
728 insert into parent values (NULL);
729 ERROR: null value in column "a" violates not-null constraint
730 insert into child (a, b) values (NULL, 'foo');
731 ERROR: null value in column "a" violates not-null constraint
732 alter table parent alter a drop not null;
733 insert into parent values (NULL);
734 insert into child (a, b) values (NULL, 'foo');
735 alter table only parent alter a set not null;
736 ERROR: column "a" contains null values
737 alter table child alter a set not null;
738 ERROR: column "a" contains null values
740 alter table only parent alter a set not null;
741 insert into parent values (NULL);
742 ERROR: null value in column "a" violates not-null constraint
743 alter table child alter a set not null;
744 insert into child (a, b) values (NULL, 'foo');
745 ERROR: null value in column "a" violates not-null constraint
747 alter table child alter a set not null;
748 insert into child (a, b) values (NULL, 'foo');
749 ERROR: null value in column "a" violates not-null constraint
752 -- test setting and removing default values
753 create table def_test (
755 c2 text default 'initial_default'
757 insert into def_test default values;
758 alter table def_test alter column c1 drop default;
759 insert into def_test default values;
760 alter table def_test alter column c2 drop default;
761 insert into def_test default values;
762 alter table def_test alter column c1 set default 10;
763 alter table def_test alter column c2 set default 'new_default';
764 insert into def_test default values;
765 select * from def_test;
767 ----+-----------------
774 -- set defaults to an incorrect type: this should fail
775 alter table def_test alter column c1 set default 'wrong_datatype';
776 ERROR: invalid input syntax for integer: "wrong_datatype"
777 alter table def_test alter column c2 set default 20;
778 -- set defaults on a non-existent column: this should fail
779 alter table def_test alter column c3 set default 30;
780 ERROR: column "c3" of relation "def_test" does not exist
781 -- set defaults on views: we need to create a view, add a rule
782 -- to allow insertions into it, and then alter the view to add
784 create view def_view_test as select * from def_test;
785 create rule def_view_test_ins as
786 on insert to def_view_test
787 do instead insert into def_test select new.*;
788 insert into def_view_test default values;
789 alter table def_view_test alter column c1 set default 45;
790 insert into def_view_test default values;
791 alter table def_view_test alter column c2 set default 'view_default';
792 insert into def_view_test default values;
793 select * from def_view_test;
795 ----+-----------------
805 drop rule def_view_test_ins on def_view_test;
806 drop view def_view_test;
808 -- alter table / drop column tests
809 -- try altering system catalogs, should fail
810 alter table pg_class drop column relname;
811 ERROR: permission denied: "pg_class" is a system catalog
812 -- try altering non-existent table, should fail
813 alter table nosuchtable drop column bar;
814 ERROR: relation "nosuchtable" does not exist
815 -- test dropping columns
816 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
817 insert into atacc1 values (1, 2, 3, 4);
818 alter table atacc1 drop a;
819 alter table atacc1 drop a;
820 ERROR: column "a" of relation "atacc1" does not exist
822 select * from atacc1;
828 select * from atacc1 order by a;
829 ERROR: column "a" does not exist
830 LINE 1: select * from atacc1 order by a;
832 select * from atacc1 order by "........pg.dropped.1........";
833 ERROR: column "........pg.dropped.1........" does not exist
834 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
836 select * from atacc1 group by a;
837 ERROR: column "a" does not exist
838 LINE 1: select * from atacc1 group by a;
840 select * from atacc1 group by "........pg.dropped.1........";
841 ERROR: column "........pg.dropped.1........" does not exist
842 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
844 select atacc1.* from atacc1;
850 select a from atacc1;
851 ERROR: column "a" does not exist
852 LINE 1: select a from atacc1;
854 select atacc1.a from atacc1;
855 ERROR: column atacc1.a does not exist
856 LINE 1: select atacc1.a from atacc1;
858 select b,c,d from atacc1;
864 select a,b,c,d from atacc1;
865 ERROR: column "a" does not exist
866 LINE 1: select a,b,c,d from atacc1;
868 select * from atacc1 where a = 1;
869 ERROR: column "a" does not exist
870 LINE 1: select * from atacc1 where a = 1;
872 select "........pg.dropped.1........" from atacc1;
873 ERROR: column "........pg.dropped.1........" does not exist
874 LINE 1: select "........pg.dropped.1........" from atacc1;
876 select atacc1."........pg.dropped.1........" from atacc1;
877 ERROR: column atacc1.........pg.dropped.1........ does not exist
878 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
880 select "........pg.dropped.1........",b,c,d from atacc1;
881 ERROR: column "........pg.dropped.1........" does not exist
882 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
884 select * from atacc1 where "........pg.dropped.1........" = 1;
885 ERROR: column "........pg.dropped.1........" does not exist
886 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
889 update atacc1 set a = 3;
890 ERROR: column "a" of relation "atacc1" does not exist
891 LINE 1: update atacc1 set a = 3;
893 update atacc1 set b = 2 where a = 3;
894 ERROR: column "a" does not exist
895 LINE 1: update atacc1 set b = 2 where a = 3;
897 update atacc1 set "........pg.dropped.1........" = 3;
898 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
899 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
901 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
902 ERROR: column "........pg.dropped.1........" does not exist
903 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
906 insert into atacc1 values (10, 11, 12, 13);
907 ERROR: INSERT has more expressions than target columns
908 LINE 1: insert into atacc1 values (10, 11, 12, 13);
910 insert into atacc1 values (default, 11, 12, 13);
911 ERROR: INSERT has more expressions than target columns
912 LINE 1: insert into atacc1 values (default, 11, 12, 13);
914 insert into atacc1 values (11, 12, 13);
915 insert into atacc1 (a) values (10);
916 ERROR: column "a" of relation "atacc1" does not exist
917 LINE 1: insert into atacc1 (a) values (10);
919 insert into atacc1 (a) values (default);
920 ERROR: column "a" of relation "atacc1" does not exist
921 LINE 1: insert into atacc1 (a) values (default);
923 insert into atacc1 (a,b,c,d) values (10,11,12,13);
924 ERROR: column "a" of relation "atacc1" does not exist
925 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
927 insert into atacc1 (a,b,c,d) values (default,11,12,13);
928 ERROR: column "a" of relation "atacc1" does not exist
929 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
931 insert into atacc1 (b,c,d) values (11,12,13);
932 insert into atacc1 ("........pg.dropped.1........") values (10);
933 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
934 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
936 insert into atacc1 ("........pg.dropped.1........") values (default);
937 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
938 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
940 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
941 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
942 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
944 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
945 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
946 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
949 delete from atacc1 where a = 3;
950 ERROR: column "a" does not exist
951 LINE 1: delete from atacc1 where a = 3;
953 delete from atacc1 where "........pg.dropped.1........" = 3;
954 ERROR: column "........pg.dropped.1........" does not exist
955 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
958 -- try dropping a non-existent column, should fail
959 alter table atacc1 drop bar;
960 ERROR: column "bar" of relation "atacc1" does not exist
961 -- try dropping the oid column, should succeed
962 alter table atacc1 drop oid;
963 -- try dropping the xmin column, should fail
964 alter table atacc1 drop xmin;
965 ERROR: cannot drop system column "xmin"
966 -- try creating a view and altering that, should fail
967 create view myview as select * from atacc1;
968 select * from myview;
973 alter table myview drop d;
974 ERROR: "myview" is not a table, composite type, or foreign table
976 -- test some commands to make sure they fail on the dropped column
978 ERROR: column "a" of relation "atacc1" does not exist
979 analyze atacc1("........pg.dropped.1........");
980 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
981 vacuum analyze atacc1(a);
982 ERROR: column "a" of relation "atacc1" does not exist
983 vacuum analyze atacc1("........pg.dropped.1........");
984 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
985 comment on column atacc1.a is 'testing';
986 ERROR: column "a" of relation "atacc1" does not exist
987 comment on column atacc1."........pg.dropped.1........" is 'testing';
988 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
989 alter table atacc1 alter a set storage plain;
990 ERROR: column "a" of relation "atacc1" does not exist
991 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
992 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
993 alter table atacc1 alter a set statistics 0;
994 ERROR: column "a" of relation "atacc1" does not exist
995 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
996 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
997 alter table atacc1 alter a set default 3;
998 ERROR: column "a" of relation "atacc1" does not exist
999 alter table atacc1 alter "........pg.dropped.1........" set default 3;
1000 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1001 alter table atacc1 alter a drop default;
1002 ERROR: column "a" of relation "atacc1" does not exist
1003 alter table atacc1 alter "........pg.dropped.1........" drop default;
1004 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1005 alter table atacc1 alter a set not null;
1006 ERROR: column "a" of relation "atacc1" does not exist
1007 alter table atacc1 alter "........pg.dropped.1........" set not null;
1008 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1009 alter table atacc1 alter a drop not null;
1010 ERROR: column "a" of relation "atacc1" does not exist
1011 alter table atacc1 alter "........pg.dropped.1........" drop not null;
1012 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
1013 alter table atacc1 rename a to x;
1014 ERROR: column "a" does not exist
1015 alter table atacc1 rename "........pg.dropped.1........" to x;
1016 ERROR: column "........pg.dropped.1........" does not exist
1017 alter table atacc1 add primary key(a);
1018 ERROR: column "a" named in key does not exist
1019 alter table atacc1 add primary key("........pg.dropped.1........");
1020 ERROR: column "........pg.dropped.1........" named in key does not exist
1021 alter table atacc1 add unique(a);
1022 ERROR: column "a" named in key does not exist
1023 alter table atacc1 add unique("........pg.dropped.1........");
1024 ERROR: column "........pg.dropped.1........" named in key does not exist
1025 alter table atacc1 add check (a > 3);
1026 ERROR: column "a" does not exist
1027 alter table atacc1 add check ("........pg.dropped.1........" > 3);
1028 ERROR: column "........pg.dropped.1........" does not exist
1029 create table atacc2 (id int4 unique);
1030 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
1031 alter table atacc1 add foreign key (a) references atacc2(id);
1032 ERROR: column "a" referenced in foreign key constraint does not exist
1033 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
1034 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1035 alter table atacc2 add foreign key (id) references atacc1(a);
1036 ERROR: column "a" referenced in foreign key constraint does not exist
1037 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
1038 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1040 create index "testing_idx" on atacc1(a);
1041 ERROR: column "a" does not exist
1042 create index "testing_idx" on atacc1("........pg.dropped.1........");
1043 ERROR: column "........pg.dropped.1........" does not exist
1044 -- test create as and select into
1045 insert into atacc1 values (21, 22, 23);
1046 create table test1 as select * from atacc1;
1047 select * from test1;
1054 select * into test2 from atacc1;
1055 select * from test2;
1062 -- try dropping all columns
1063 alter table atacc1 drop c;
1064 alter table atacc1 drop d;
1065 alter table atacc1 drop b;
1066 select * from atacc1;
1072 create table parent (a int, b int, c int);
1073 insert into parent values (1, 2, 3);
1074 alter table parent drop a;
1075 create table child (d varchar(255)) inherits (parent);
1076 insert into child values (12, 13, 'testing');
1077 select * from parent;
1084 select * from child;
1090 alter table parent drop c;
1091 select * from parent;
1098 select * from child;
1107 create table test (a int4, b int4, c int4);
1108 insert into test values (1,2,3);
1109 alter table test drop a;
1110 copy test to stdout;
1112 copy test(a) to stdout;
1113 ERROR: column "a" of relation "test" does not exist
1114 copy test("........pg.dropped.1........") to stdout;
1115 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
1116 copy test from stdin;
1117 ERROR: extra data after last expected column
1118 CONTEXT: COPY test, line 1: "10 11 12"
1125 copy test from stdin;
1133 copy test(a) from stdin;
1134 ERROR: column "a" of relation "test" does not exist
1135 copy test("........pg.dropped.1........") from stdin;
1136 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
1137 copy test(b,c) from stdin;
1148 create table dropColumn (a int, b int, e int);
1149 create table dropColumnChild (c int) inherits (dropColumn);
1150 create table dropColumnAnother (d int) inherits (dropColumnChild);
1151 -- these two should fail
1152 alter table dropColumnchild drop column a;
1153 ERROR: cannot drop inherited column "a"
1154 alter table only dropColumnChild drop column b;
1155 ERROR: cannot drop inherited column "b"
1156 -- these three should work
1157 alter table only dropColumn drop column e;
1158 alter table dropColumnChild drop column c;
1159 alter table dropColumn drop column a;
1160 create table renameColumn (a int);
1161 create table renameColumnChild (b int) inherits (renameColumn);
1162 create table renameColumnAnother (c int) inherits (renameColumnChild);
1163 -- these three should fail
1164 alter table renameColumnChild rename column a to d;
1165 ERROR: cannot rename inherited column "a"
1166 alter table only renameColumnChild rename column a to d;
1167 ERROR: inherited column "a" must be renamed in child tables too
1168 alter table only renameColumn rename column a to d;
1169 ERROR: inherited column "a" must be renamed in child tables too
1170 -- these should work
1171 alter table renameColumn rename column a to d;
1172 alter table renameColumnChild rename column b to a;
1174 alter table renameColumn add column w int;
1176 alter table only renameColumn add column x int;
1177 ERROR: column must be added to child tables too
1178 -- Test corner cases in dropping of inherited columns
1179 create table p1 (f1 int, f2 int);
1180 create table c1 (f1 int not null) inherits(p1);
1181 NOTICE: merging column "f1" with inherited definition
1182 -- should be rejected since c1.f1 is inherited
1183 alter table c1 drop column f1;
1184 ERROR: cannot drop inherited column "f1"
1186 alter table p1 drop column f1;
1187 -- c1.f1 is still there, but no longer inherited
1193 alter table c1 drop column f1;
1195 ERROR: column "f1" does not exist
1196 LINE 1: select f1 from c1;
1198 drop table p1 cascade;
1199 NOTICE: drop cascades to table c1
1200 create table p1 (f1 int, f2 int);
1201 create table c1 () inherits(p1);
1202 -- should be rejected since c1.f1 is inherited
1203 alter table c1 drop column f1;
1204 ERROR: cannot drop inherited column "f1"
1205 alter table p1 drop column f1;
1206 -- c1.f1 is dropped now, since there is no local definition for it
1208 ERROR: column "f1" does not exist
1209 LINE 1: select f1 from c1;
1211 drop table p1 cascade;
1212 NOTICE: drop cascades to table c1
1213 create table p1 (f1 int, f2 int);
1214 create table c1 () inherits(p1);
1215 -- should be rejected since c1.f1 is inherited
1216 alter table c1 drop column f1;
1217 ERROR: cannot drop inherited column "f1"
1218 alter table only p1 drop column f1;
1219 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1220 alter table c1 drop column f1;
1221 drop table p1 cascade;
1222 NOTICE: drop cascades to table c1
1223 create table p1 (f1 int, f2 int);
1224 create table c1 (f1 int not null) inherits(p1);
1225 NOTICE: merging column "f1" with inherited definition
1226 -- should be rejected since c1.f1 is inherited
1227 alter table c1 drop column f1;
1228 ERROR: cannot drop inherited column "f1"
1229 alter table only p1 drop column f1;
1230 -- c1.f1 is still there, but no longer inherited
1231 alter table c1 drop column f1;
1232 drop table p1 cascade;
1233 NOTICE: drop cascades to table c1
1234 create table p1(id int, name text);
1235 create table p2(id2 int, name text, height int);
1236 create table c1(age int) inherits(p1,p2);
1237 NOTICE: merging multiple inherited definitions of column "name"
1238 create table gc1() inherits (c1);
1239 select relname, attname, attinhcount, attislocal
1240 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1241 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1242 order by relname, attnum;
1243 relname | attname | attinhcount | attislocal
1244 ---------+---------+-------------+------------
1253 gc1 | height | 1 | f
1263 alter table only p1 drop column name;
1264 -- should work. Now c1.name is local and inhcount is 0.
1265 alter table p2 drop column name;
1266 -- should be rejected since its inherited
1267 alter table gc1 drop column name;
1268 ERROR: cannot drop inherited column "name"
1269 -- should work, and drop gc1.name along
1270 alter table c1 drop column name;
1271 -- should fail: column does not exist
1272 alter table gc1 drop column name;
1273 ERROR: column "name" of relation "gc1" does not exist
1274 -- should work and drop the attribute in all tables
1275 alter table p2 drop column height;
1277 create table dropColumnExists ();
1278 alter table dropColumnExists drop column non_existing; --fail
1279 ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
1280 alter table dropColumnExists drop column if exists non_existing; --succeed
1281 NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
1282 select relname, attname, attinhcount, attislocal
1283 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1284 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1285 order by relname, attnum;
1286 relname | attname | attinhcount | attislocal
1287 ---------+---------+-------------+------------
1298 drop table p1, p2 cascade;
1299 NOTICE: drop cascades to 2 other objects
1300 DETAIL: drop cascades to table c1
1301 drop cascades to table gc1
1302 -- test attinhcount tracking with merged columns
1303 create table depth0();
1304 create table depth1(c text) inherits (depth0);
1305 create table depth2() inherits (depth1);
1306 alter table depth0 add c text;
1307 NOTICE: merging definition of column "c" for child "depth1"
1308 select attrelid::regclass, attname, attinhcount, attislocal
1310 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1311 order by attrelid::regclass::text, attnum;
1312 attrelid | attname | attinhcount | attislocal
1313 ----------+---------+-------------+------------
1320 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
1322 create table altstartwith (col integer) with oids;
1323 insert into altstartwith values (1);
1324 select oid > 0, * from altstartwith;
1330 alter table altstartwith set without oids;
1331 select oid > 0, * from altstartwith; -- fails
1332 ERROR: column "oid" does not exist
1333 LINE 1: select oid > 0, * from altstartwith;
1335 select * from altstartwith;
1341 alter table altstartwith set with oids;
1342 select oid > 0, * from altstartwith;
1348 drop table altstartwith;
1349 -- Check inheritance cases
1350 create table altwithoid (col integer) with oids;
1351 -- Inherits parents oid column anyway
1352 create table altinhoid () inherits (altwithoid) without oids;
1353 insert into altinhoid values (1);
1354 select oid > 0, * from altwithoid;
1360 select oid > 0, * from altinhoid;
1366 alter table altwithoid set without oids;
1367 select oid > 0, * from altwithoid; -- fails
1368 ERROR: column "oid" does not exist
1369 LINE 1: select oid > 0, * from altwithoid;
1371 select oid > 0, * from altinhoid; -- fails
1372 ERROR: column "oid" does not exist
1373 LINE 1: select oid > 0, * from altinhoid;
1375 select * from altwithoid;
1381 select * from altinhoid;
1387 alter table altwithoid set with oids;
1388 select oid > 0, * from altwithoid;
1394 select oid > 0, * from altinhoid;
1400 drop table altwithoid cascade;
1401 NOTICE: drop cascades to table altinhoid
1402 create table altwithoid (col integer) without oids;
1403 -- child can have local oid column
1404 create table altinhoid () inherits (altwithoid) with oids;
1405 insert into altinhoid values (1);
1406 select oid > 0, * from altwithoid; -- fails
1407 ERROR: column "oid" does not exist
1408 LINE 1: select oid > 0, * from altwithoid;
1410 select oid > 0, * from altinhoid;
1416 alter table altwithoid set with oids;
1417 NOTICE: merging definition of column "oid" for child "altinhoid"
1418 select oid > 0, * from altwithoid;
1424 select oid > 0, * from altinhoid;
1430 -- the child's local definition should remain
1431 alter table altwithoid set without oids;
1432 select oid > 0, * from altwithoid; -- fails
1433 ERROR: column "oid" does not exist
1434 LINE 1: select oid > 0, * from altwithoid;
1436 select oid > 0, * from altinhoid;
1442 drop table altwithoid cascade;
1443 NOTICE: drop cascades to table altinhoid
1444 -- test renumbering of child-table columns in inherited operations
1445 create table p1 (f1 int);
1446 create table c1 (f2 text, f3 int) inherits (p1);
1447 alter table p1 add column a1 int check (a1 > 0);
1448 alter table p1 add column f2 text;
1449 NOTICE: merging definition of column "f2" for child "c1"
1450 insert into p1 values (1,2,'abc');
1451 insert into c1 values(11,'xyz',33,0); -- should fail
1452 ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1453 insert into c1 values(11,'xyz',33,22);
1461 update p1 set a1 = a1 + 1, f2 = upper(f2);
1469 drop table p1 cascade;
1470 NOTICE: drop cascades to table c1
1471 -- test that operations with a dropped column do not try to reference
1473 create domain mytype as text;
1474 create temp table foo (f1 text, f2 mytype, f3 text);
1475 insert into foo values('bb','cc','dd');
1482 drop domain mytype cascade;
1483 NOTICE: drop cascades to table foo column f2
1490 insert into foo values('qq','rr');
1498 update foo set f3 = 'zz';
1506 select f3,max(f1) from foo group by f3;
1512 -- Simple tests for alter table column type
1513 alter table foo alter f1 TYPE integer; -- fails
1514 ERROR: column "f1" cannot be cast to type integer
1515 alter table foo alter f1 TYPE varchar(10);
1516 create table anothertab (atcol1 serial8, atcol2 boolean,
1517 constraint anothertab_chk check (atcol1 <= 3));
1518 NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1"
1519 insert into anothertab (atcol1, atcol2) values (default, true);
1520 insert into anothertab (atcol1, atcol2) values (default, false);
1521 select * from anothertab;
1528 alter table anothertab alter column atcol1 type boolean; -- fails
1529 ERROR: column "atcol1" cannot be cast to type boolean
1530 alter table anothertab alter column atcol1 type integer;
1531 select * from anothertab;
1538 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1539 ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1540 insert into anothertab (atcol1, atcol2) values (default, null);
1541 select * from anothertab;
1549 alter table anothertab alter column atcol2 type text
1550 using case when atcol2 is true then 'IT WAS TRUE'
1551 when atcol2 is false then 'IT WAS FALSE'
1552 else 'IT WAS NULL!' end;
1553 select * from anothertab;
1555 --------+--------------
1561 alter table anothertab alter column atcol1 type boolean
1562 using case when atcol1 % 2 = 0 then true else false end; -- fails
1563 ERROR: default for column "atcol1" cannot be cast to type boolean
1564 alter table anothertab alter column atcol1 drop default;
1565 alter table anothertab alter column atcol1 type boolean
1566 using case when atcol1 % 2 = 0 then true else false end; -- fails
1567 ERROR: operator does not exist: boolean <= integer
1568 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
1569 alter table anothertab drop constraint anothertab_chk;
1570 alter table anothertab drop constraint anothertab_chk; -- fails
1571 ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
1572 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1573 NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
1574 alter table anothertab alter column atcol1 type boolean
1575 using case when atcol1 % 2 = 0 then true else false end;
1576 select * from anothertab;
1578 --------+--------------
1584 drop table anothertab;
1585 create table another (f1 int, f2 text);
1586 insert into another values(1, 'one');
1587 insert into another values(2, 'two');
1588 insert into another values(3, 'three');
1589 select * from another;
1598 alter f1 type text using f2 || ' more',
1599 alter f2 type bigint using f1 * 10;
1600 select * from another;
1610 create table tab1 (a int, b text);
1611 create table tab2 (x int, y tab1);
1612 alter table tab1 alter column b type varchar; -- fails
1613 ERROR: cannot alter table "tab1" because column "tab2.y" uses its row type
1614 -- disallow recursive containment of row types
1615 create temp table recur1 (f1 int);
1616 alter table recur1 add column f2 recur1; -- fails
1617 ERROR: composite type recur1 cannot be made a member of itself
1618 alter table recur1 add column f2 recur1[]; -- fails
1619 ERROR: composite type recur1 cannot be made a member of itself
1620 create domain array_of_recur1 as recur1[];
1621 alter table recur1 add column f2 array_of_recur1; -- fails
1622 ERROR: composite type recur1 cannot be made a member of itself
1623 create temp table recur2 (f1 int, f2 recur1);
1624 alter table recur1 add column f2 recur2; -- fails
1625 ERROR: composite type recur1 cannot be made a member of itself
1626 alter table recur1 add column f2 int;
1627 alter table recur1 alter column f2 type recur2; -- fails
1628 ERROR: composite type recur1 cannot be made a member of itself
1629 -- SET STORAGE may need to add a TOAST table
1630 create table test_storage (a text);
1631 alter table test_storage alter a set storage plain;
1632 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1633 alter table test_storage alter a set storage extended; -- re-add TOAST table
1634 select reltoastrelid <> 0 as has_toast_table
1636 where oid = 'test_storage'::regclass;
1645 drop type lockmodes;
1646 ERROR: type "lockmodes" does not exist
1647 create type lockmodes as enum (
1651 ,'ShareUpdateExclusiveLock'
1653 ,'ShareRowExclusiveLock'
1655 ,'AccessExclusiveLock'
1658 ERROR: view "my_locks" does not exist
1659 create or replace view my_locks as
1660 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1661 from pg_locks l join pg_class c on l.relation = c.oid
1662 where virtualtransaction = (
1663 select virtualtransaction
1665 where transactionid = txid_current()::integer)
1666 and locktype = 'relation'
1667 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1668 and c.relname != 'my_locks'
1670 create table alterlock (f1 int primary key, f2 text);
1671 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "alterlock_pkey" for table "alterlock"
1672 begin; alter table alterlock alter column f2 set statistics 150;
1673 select * from my_locks order by 1;
1674 relname | max_lockmode
1675 -----------+---------------------
1676 alterlock | AccessExclusiveLock
1680 begin; alter table alterlock cluster on alterlock_pkey;
1681 select * from my_locks order by 1;
1682 relname | max_lockmode
1683 ----------------+---------------------
1684 alterlock | AccessExclusiveLock
1685 alterlock_pkey | AccessExclusiveLock
1689 begin; alter table alterlock set without cluster;
1690 select * from my_locks order by 1;
1691 relname | max_lockmode
1692 -----------+---------------------
1693 alterlock | AccessExclusiveLock
1697 begin; alter table alterlock set (fillfactor = 100);
1698 select * from my_locks order by 1;
1699 relname | max_lockmode
1700 -----------+---------------------
1701 alterlock | AccessExclusiveLock
1702 pg_toast | AccessExclusiveLock
1706 begin; alter table alterlock reset (fillfactor);
1707 select * from my_locks order by 1;
1708 relname | max_lockmode
1709 -----------+---------------------
1710 alterlock | AccessExclusiveLock
1711 pg_toast | AccessExclusiveLock
1715 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1716 select * from my_locks order by 1;
1717 relname | max_lockmode
1718 -----------+---------------------
1719 alterlock | AccessExclusiveLock
1720 pg_toast | AccessExclusiveLock
1724 begin; alter table alterlock set (autovacuum_enabled = off);
1725 select * from my_locks order by 1;
1726 relname | max_lockmode
1727 -----------+---------------------
1728 alterlock | AccessExclusiveLock
1729 pg_toast | AccessExclusiveLock
1733 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1734 select * from my_locks order by 1;
1735 relname | max_lockmode
1736 -----------+---------------------
1737 alterlock | AccessExclusiveLock
1741 begin; alter table alterlock alter column f2 set storage extended;
1742 select * from my_locks order by 1;
1743 relname | max_lockmode
1744 -----------+---------------------
1745 alterlock | AccessExclusiveLock
1749 begin; alter table alterlock alter column f2 set default 'x';
1750 select * from my_locks order by 1;
1751 relname | max_lockmode
1752 -----------+---------------------
1753 alterlock | AccessExclusiveLock
1758 drop table alterlock;
1760 drop type lockmodes;
1764 create function test_strict(text) returns text as
1765 'select coalesce($1, ''got passed a null'');'
1766 language sql returns null on null input;
1767 select test_strict(NULL);
1773 alter function test_strict(text) called on null input;
1774 select test_strict(NULL);
1780 create function non_strict(text) returns text as
1781 'select coalesce($1, ''got passed a null'');'
1782 language sql called on null input;
1783 select non_strict(NULL);
1789 alter function non_strict(text) returns null on null input;
1790 select non_strict(NULL);
1797 -- alter object set schema
1799 create schema alter1;
1800 create schema alter2;
1801 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1802 NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
1803 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
1804 create view alter1.v1 as select * from alter1.t1;
1805 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1806 create domain alter1.posint integer check (value > 0);
1807 create type alter1.ctype as (f1 int, f2 text);
1808 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1809 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1810 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
1811 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1812 operator 1 alter1.=(alter1.ctype, alter1.ctype);
1813 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1814 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1815 create text search configuration alter1.cfg(parser = alter1.prs);
1816 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1817 create text search dictionary alter1.dict(template = alter1.tmpl);
1818 insert into alter1.t1(f2) values(11);
1819 insert into alter1.t1(f2) values(12);
1820 alter table alter1.t1 set schema alter2;
1821 alter table alter1.v1 set schema alter2;
1822 alter function alter1.plus1(int) set schema alter2;
1823 alter domain alter1.posint set schema alter2;
1824 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1825 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1826 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1827 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1828 alter type alter1.ctype set schema alter2;
1829 alter conversion alter1.ascii_to_utf8 set schema alter2;
1830 alter text search parser alter1.prs set schema alter2;
1831 alter text search configuration alter1.cfg set schema alter2;
1832 alter text search template alter1.tmpl set schema alter2;
1833 alter text search dictionary alter1.dict set schema alter2;
1834 -- this should succeed because nothing is left in alter1
1836 insert into alter2.t1(f2) values(13);
1837 insert into alter2.t1(f2) values(14);
1838 select * from alter2.t1;
1847 select * from alter2.v1;
1856 select alter2.plus1(41);
1863 drop schema alter2 cascade;
1864 NOTICE: drop cascades to 13 other objects
1865 DETAIL: drop cascades to table alter2.t1
1866 drop cascades to view alter2.v1
1867 drop cascades to function alter2.plus1(integer)
1868 drop cascades to type alter2.posint
1869 drop cascades to operator family alter2.ctype_hash_ops for access method hash
1870 drop cascades to type alter2.ctype
1871 drop cascades to function alter2.same(alter2.ctype,alter2.ctype)
1872 drop cascades to operator alter2.=(alter2.ctype,alter2.ctype)
1873 drop cascades to conversion ascii_to_utf8
1874 drop cascades to text search parser prs
1875 drop cascades to text search configuration cfg
1876 drop cascades to text search template tmpl
1877 drop cascades to text search dictionary dict
1881 CREATE TYPE test_type AS (a int);
1883 Composite type "public.test_type"
1884 Column | Type | Modifiers
1885 --------+---------+-----------
1888 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1889 ERROR: relation "nosuchtype" does not exist
1890 ALTER TYPE test_type ADD ATTRIBUTE b text;
1892 Composite type "public.test_type"
1893 Column | Type | Modifiers
1894 --------+---------+-----------
1898 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1899 ERROR: column "b" of relation "test_type" already exists
1900 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1902 Composite type "public.test_type"
1903 Column | Type | Modifiers
1904 --------+-------------------+-----------
1906 b | character varying |
1908 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1910 Composite type "public.test_type"
1911 Column | Type | Modifiers
1912 --------+---------+-----------
1916 ALTER TYPE test_type DROP ATTRIBUTE b;
1918 Composite type "public.test_type"
1919 Column | Type | Modifiers
1920 --------+---------+-----------
1923 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1924 ERROR: column "c" of relation "test_type" does not exist
1925 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1926 NOTICE: column "c" of relation "test_type" does not exist, skipping
1927 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1929 Composite type "public.test_type"
1930 Column | Type | Modifiers
1931 --------+---------+-----------
1934 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1935 ERROR: column "a" does not exist
1936 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1938 Composite type "public.test_type"
1939 Column | Type | Modifiers
1940 --------+---------+-----------
1943 DROP TYPE test_type;
1944 CREATE TYPE test_type1 AS (a int, b text);
1945 CREATE TABLE test_tbl1 (x int, y test_type1);
1946 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1947 ERROR: cannot alter type "test_type1" because column "test_tbl1.y" uses it
1948 CREATE TYPE test_type2 AS (a int, b text);
1949 CREATE TABLE test_tbl2 OF test_type2;
1950 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
1952 Composite type "public.test_type2"
1953 Column | Type | Modifiers
1954 --------+---------+-----------
1959 Table "public.test_tbl2"
1960 Column | Type | Modifiers
1961 --------+---------+-----------
1964 Number of child tables: 1 (Use \d+ to list them.)
1965 Typed table of type: test_type2
1967 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1968 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1969 HINT: Use ALTER ... CASCADE to alter the typed tables too.
1970 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1972 Composite type "public.test_type2"
1973 Column | Type | Modifiers
1974 --------+---------+-----------
1980 Table "public.test_tbl2"
1981 Column | Type | Modifiers
1982 --------+---------+-----------
1986 Number of child tables: 1 (Use \d+ to list them.)
1987 Typed table of type: test_type2
1989 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1990 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1991 HINT: Use ALTER ... CASCADE to alter the typed tables too.
1992 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1994 Composite type "public.test_type2"
1995 Column | Type | Modifiers
1996 --------+-------------------+-----------
1998 b | character varying |
2002 Table "public.test_tbl2"
2003 Column | Type | Modifiers
2004 --------+-------------------+-----------
2006 b | character varying |
2008 Number of child tables: 1 (Use \d+ to list them.)
2009 Typed table of type: test_type2
2011 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
2012 ERROR: cannot alter type "test_type2" because it is the type of a typed table
2013 HINT: Use ALTER ... CASCADE to alter the typed tables too.
2014 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
2016 Composite type "public.test_type2"
2017 Column | Type | Modifiers
2018 --------+---------+-----------
2023 Table "public.test_tbl2"
2024 Column | Type | Modifiers
2025 --------+---------+-----------
2028 Number of child tables: 1 (Use \d+ to list them.)
2029 Typed table of type: test_type2
2031 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
2032 ERROR: cannot alter type "test_type2" because it is the type of a typed table
2033 HINT: Use ALTER ... CASCADE to alter the typed tables too.
2034 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
2036 Composite type "public.test_type2"
2037 Column | Type | Modifiers
2038 --------+---------+-----------
2043 Table "public.test_tbl2"
2044 Column | Type | Modifiers
2045 --------+---------+-----------
2048 Number of child tables: 1 (Use \d+ to list them.)
2049 Typed table of type: test_type2
2051 \d test_tbl2_subclass
2052 Table "public.test_tbl2_subclass"
2053 Column | Type | Modifiers
2054 --------+---------+-----------
2059 DROP TABLE test_tbl2_subclass;
2060 -- This test isn't that interesting on its own, but the purpose is to leave
2061 -- behind a table to test pg_upgrade with. The table has a composite type
2062 -- column in it, and the composite type has a dropped attribute.
2063 CREATE TYPE test_type3 AS (a int);
2064 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
2065 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
2066 CREATE TYPE test_type_empty AS ();
2067 DROP TYPE test_type_empty;
2069 -- typed tables: OF / NOT OF
2071 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
2072 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
2073 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
2074 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
2075 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
2076 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
2077 CREATE TABLE tt4 (x int); -- too few columns
2078 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
2079 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
2080 CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
2081 ALTER TABLE tt7 DROP q; -- OK
2082 ALTER TABLE tt0 OF tt_t0;
2083 ALTER TABLE tt1 OF tt_t0;
2084 ERROR: table "tt1" has different type for column "y"
2085 ALTER TABLE tt2 OF tt_t0;
2086 ERROR: table "tt2" has different type for column "y"
2087 ALTER TABLE tt3 OF tt_t0;
2088 ERROR: table has column "y" where type requires "x"
2089 ALTER TABLE tt4 OF tt_t0;
2090 ERROR: table is missing column "y"
2091 ALTER TABLE tt5 OF tt_t0;
2092 ERROR: table has extra column "z"
2093 ALTER TABLE tt6 OF tt_t0;
2094 ERROR: typed tables cannot inherit
2095 ALTER TABLE tt7 OF tt_t0;
2096 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
2097 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
2098 ALTER TABLE tt7 NOT OF;
2101 Column | Type | Modifiers
2102 --------+--------------+-----------