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 (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
201 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
202 ERROR: there is no unique constraint matching given keys for referenced table "tmp4"
207 -- Foreign key adding test with mixed types
208 -- Note: these tables are TEMP to avoid name conflicts when this test
209 -- is run in parallel with foreign_key.sql.
210 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
211 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
212 INSERT INTO PKTABLE VALUES(42);
213 CREATE TEMP TABLE FKTABLE (ftest1 inet);
214 -- This next should fail, because int=inet does not exist
215 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
216 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
217 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
218 -- This should also fail for the same reason, but here we
219 -- give the column name
220 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
221 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
222 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
224 -- This should succeed, even though they are different types,
225 -- because int=int8 exists and is a member of the integer opfamily
226 CREATE TEMP TABLE FKTABLE (ftest1 int8);
227 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
228 -- Check it actually works
229 INSERT INTO FKTABLE VALUES(42); -- should succeed
230 INSERT INTO FKTABLE VALUES(43); -- should fail
231 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
232 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
234 -- This should fail, because we'd have to cast numeric to int which is
235 -- not an implicit coercion (or use numeric=numeric, but that's not part
236 -- of the integer opfamily)
237 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
238 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
239 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
240 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
243 -- On the other hand, this should work because int implicitly promotes to
244 -- numeric, and we allow promotion on the FK side
245 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
246 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
247 INSERT INTO PKTABLE VALUES(42);
248 CREATE TEMP TABLE FKTABLE (ftest1 int);
249 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
250 -- Check it actually works
251 INSERT INTO FKTABLE VALUES(42); -- should succeed
252 INSERT INTO FKTABLE VALUES(43); -- should fail
253 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
254 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
257 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
258 PRIMARY KEY(ptest1, ptest2));
259 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
260 -- This should fail, because we just chose really odd types
261 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
262 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
263 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
264 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
266 -- Again, so should this...
267 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
268 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
269 references pktable(ptest1, ptest2);
270 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
271 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
273 -- This fails because we mixed up the column ordering
274 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
275 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
276 references pktable(ptest2, ptest1);
277 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
278 DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
280 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
281 references pktable(ptest1, ptest2);
282 ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
283 DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
284 -- temp tables should go away by themselves, need not drop them.
285 -- test check constraint adding
286 create table atacc1 ( test int );
287 -- add a check constraint
288 alter table atacc1 add constraint atacc_test1 check (test>3);
290 insert into atacc1 (test) values (2);
291 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
293 insert into atacc1 (test) values (4);
295 -- let's do one where the check fails when added
296 create table atacc1 ( test int );
297 -- insert a soon to be failing row
298 insert into atacc1 (test) values (2);
299 -- add a check constraint (fails)
300 alter table atacc1 add constraint atacc_test1 check (test>3);
301 ERROR: check constraint "atacc_test1" is violated by some row
302 insert into atacc1 (test) values (4);
304 -- let's do one where the check fails because the column doesn't exist
305 create table atacc1 ( test int );
306 -- add a check constraint (fails)
307 alter table atacc1 add constraint atacc_test1 check (test1>3);
308 ERROR: column "test1" does not exist
310 -- something a little more complicated
311 create table atacc1 ( test int, test2 int, test3 int);
312 -- add a check constraint (fails)
313 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
315 insert into atacc1 (test,test2,test3) values (4,4,2);
316 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
318 insert into atacc1 (test,test2,test3) values (4,4,5);
320 -- lets do some naming tests
321 create table atacc1 (test int check (test>3), test2 int);
322 alter table atacc1 add check (test2>test);
323 -- should fail for $2
324 insert into atacc1 (test2, test) values (3, 4);
325 ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
327 -- inheritance related tests
328 create table atacc1 (test int);
329 create table atacc2 (test2 int);
330 create table atacc3 (test3 int) inherits (atacc1, atacc2);
331 alter table atacc2 add constraint foo check (test2>0);
332 -- fail and then succeed on atacc2
333 insert into atacc2 (test2) values (-3);
334 ERROR: new row for relation "atacc2" violates check constraint "foo"
335 insert into atacc2 (test2) values (3);
336 -- fail and then succeed on atacc3
337 insert into atacc3 (test2) values (-3);
338 ERROR: new row for relation "atacc3" violates check constraint "foo"
339 insert into atacc3 (test2) values (3);
343 -- same things with one created with INHERIT
344 create table atacc1 (test int);
345 create table atacc2 (test2 int);
346 create table atacc3 (test3 int) inherits (atacc1, atacc2);
347 alter table atacc3 no inherit atacc2;
349 alter table atacc3 no inherit atacc2;
350 ERROR: relation "atacc2" is not a parent of relation "atacc3"
351 -- make sure it really isn't a child
352 insert into atacc3 (test2) values (3);
353 select test2 from atacc2;
358 -- fail due to missing constraint
359 alter table atacc2 add constraint foo check (test2>0);
360 alter table atacc3 inherit atacc2;
361 ERROR: child table is missing constraint "foo"
362 -- fail due to missing column
363 alter table atacc3 rename test2 to testx;
364 alter table atacc3 inherit atacc2;
365 ERROR: child table is missing column "test2"
366 -- fail due to mismatched data type
367 alter table atacc3 add test2 bool;
368 alter table atacc3 inherit atacc2;
369 ERROR: child table "atacc3" has different type for column "test2"
370 alter table atacc3 drop test2;
372 alter table atacc3 add test2 int;
373 update atacc3 set test2 = 4 where test2 is null;
374 alter table atacc3 add constraint foo check (test2>0);
375 alter table atacc3 inherit atacc2;
376 -- fail due to duplicates and circular inheritance
377 alter table atacc3 inherit atacc2;
378 ERROR: relation "atacc2" would be inherited from more than once
379 alter table atacc2 inherit atacc3;
380 ERROR: circular inheritance not allowed
381 DETAIL: "atacc3" is already a child of "atacc2".
382 alter table atacc2 inherit atacc2;
383 ERROR: circular inheritance not allowed
384 DETAIL: "atacc2" is already a child of "atacc2".
385 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
386 select test2 from atacc2;
392 drop table atacc2 cascade;
393 NOTICE: drop cascades to table atacc3
395 -- adding only to a parent is disallowed as of 8.4
396 create table atacc1 (test int);
397 create table atacc2 (test2 int) inherits (atacc1);
399 alter table only atacc1 add constraint foo check (test>0);
400 ERROR: constraint must be added to child tables too
402 alter table only atacc2 add constraint foo check (test>0);
403 -- check constraint not there on parent
404 insert into atacc1 (test) values (-3);
405 insert into atacc1 (test) values (3);
406 -- check constraint is there on child
407 insert into atacc2 (test) values (-3);
408 ERROR: new row for relation "atacc2" violates check constraint "foo"
409 insert into atacc2 (test) values (3);
412 -- test unique constraint adding
413 create table atacc1 ( test int ) with oids;
414 -- add a unique constraint
415 alter table atacc1 add constraint atacc_test1 unique (test);
416 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
417 -- insert first value
418 insert into atacc1 (test) values (2);
420 insert into atacc1 (test) values (2);
421 ERROR: duplicate key value violates unique constraint "atacc_test1"
422 DETAIL: Key (test)=(2) already exists.
424 insert into atacc1 (test) values (4);
425 -- try adding a unique oid constraint
426 alter table atacc1 add constraint atacc_oid1 unique(oid);
427 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"
428 -- try to create duplicates via alter table using - should fail
429 alter table atacc1 alter column test type integer using 0;
430 ERROR: could not create unique index "atacc_test1"
431 DETAIL: Key (test)=(0) is duplicated.
433 -- let's do one where the unique constraint fails when added
434 create table atacc1 ( test int );
435 -- insert soon to be failing rows
436 insert into atacc1 (test) values (2);
437 insert into atacc1 (test) values (2);
438 -- add a unique constraint (fails)
439 alter table atacc1 add constraint atacc_test1 unique (test);
440 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
441 ERROR: could not create unique index "atacc_test1"
442 DETAIL: Key (test)=(2) is duplicated.
443 insert into atacc1 (test) values (3);
445 -- let's do one where the unique constraint fails
446 -- because the column doesn't exist
447 create table atacc1 ( test int );
448 -- add a unique constraint (fails)
449 alter table atacc1 add constraint atacc_test1 unique (test1);
450 ERROR: column "test1" named in key does not exist
452 -- something a little more complicated
453 create table atacc1 ( test int, test2 int);
454 -- add a unique constraint
455 alter table atacc1 add constraint atacc_test1 unique (test, test2);
456 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
457 -- insert initial value
458 insert into atacc1 (test,test2) values (4,4);
460 insert into atacc1 (test,test2) values (4,4);
461 ERROR: duplicate key value violates unique constraint "atacc_test1"
462 DETAIL: Key (test, test2)=(4, 4) already exists.
463 -- should all succeed
464 insert into atacc1 (test,test2) values (4,5);
465 insert into atacc1 (test,test2) values (5,4);
466 insert into atacc1 (test,test2) values (5,5);
468 -- lets do some naming tests
469 create table atacc1 (test int, test2 int, unique(test));
470 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
471 alter table atacc1 add unique (test2);
472 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
473 -- should fail for @@ second one @@
474 insert into atacc1 (test2, test) values (3, 3);
475 insert into atacc1 (test2, test) values (2, 3);
476 ERROR: duplicate key value violates unique constraint "atacc1_test_key"
477 DETAIL: Key (test)=(3) already exists.
479 -- test primary key constraint adding
480 create table atacc1 ( test int ) with oids;
481 -- add a primary key constraint
482 alter table atacc1 add constraint atacc_test1 primary key (test);
483 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
484 -- insert first value
485 insert into atacc1 (test) values (2);
487 insert into atacc1 (test) values (2);
488 ERROR: duplicate key value violates unique constraint "atacc_test1"
489 DETAIL: Key (test)=(2) already exists.
491 insert into atacc1 (test) values (4);
492 -- inserting NULL should fail
493 insert into atacc1 (test) values(NULL);
494 ERROR: null value in column "test" violates not-null constraint
495 -- try adding a second primary key (should fail)
496 alter table atacc1 add constraint atacc_oid1 primary key(oid);
497 ERROR: multiple primary keys for table "atacc1" are not allowed
498 -- drop first primary key constraint
499 alter table atacc1 drop constraint atacc_test1 restrict;
500 -- try adding a primary key on oid (should succeed)
501 alter table atacc1 add constraint atacc_oid1 primary key(oid);
502 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
504 -- let's do one where the primary key constraint fails when added
505 create table atacc1 ( test int );
506 -- insert soon to be failing rows
507 insert into atacc1 (test) values (2);
508 insert into atacc1 (test) values (2);
509 -- add a primary key (fails)
510 alter table atacc1 add constraint atacc_test1 primary key (test);
511 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
512 ERROR: could not create unique index "atacc_test1"
513 DETAIL: Key (test)=(2) is duplicated.
514 insert into atacc1 (test) values (3);
516 -- let's do another one where the primary key constraint fails when added
517 create table atacc1 ( test int );
518 -- insert soon to be failing row
519 insert into atacc1 (test) values (NULL);
520 -- add a primary key (fails)
521 alter table atacc1 add constraint atacc_test1 primary key (test);
522 ERROR: column "test" contains null values
523 insert into atacc1 (test) values (3);
525 -- let's do one where the primary key constraint fails
526 -- because the column doesn't exist
527 create table atacc1 ( test int );
528 -- add a primary key constraint (fails)
529 alter table atacc1 add constraint atacc_test1 primary key (test1);
530 ERROR: column "test1" named in key does not exist
532 -- adding a new column as primary key to a non-empty table.
533 -- should fail unless the column has a non-null default value.
534 create table atacc1 ( test int );
535 insert into atacc1 (test) values (0);
536 -- add a primary key column without a default (fails).
537 alter table atacc1 add column test2 int primary key;
538 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
539 ERROR: column "test2" contains null values
540 -- now add a primary key column with a default (succeeds).
541 alter table atacc1 add column test2 int default 0 primary key;
542 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
544 -- something a little more complicated
545 create table atacc1 ( test int, test2 int);
546 -- add a primary key constraint
547 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
548 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
549 -- try adding a second primary key - should fail
550 alter table atacc1 add constraint atacc_test2 primary key (test);
551 ERROR: multiple primary keys for table "atacc1" are not allowed
552 -- insert initial value
553 insert into atacc1 (test,test2) values (4,4);
555 insert into atacc1 (test,test2) values (4,4);
556 ERROR: duplicate key value violates unique constraint "atacc_test1"
557 DETAIL: Key (test, test2)=(4, 4) already exists.
558 insert into atacc1 (test,test2) values (NULL,3);
559 ERROR: null value in column "test" violates not-null constraint
560 insert into atacc1 (test,test2) values (3, NULL);
561 ERROR: null value in column "test2" violates not-null constraint
562 insert into atacc1 (test,test2) values (NULL,NULL);
563 ERROR: null value in column "test" violates not-null constraint
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, primary key(test));
571 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
572 -- only first should succeed
573 insert into atacc1 (test2, test) values (3, 3);
574 insert into atacc1 (test2, test) values (2, 3);
575 ERROR: duplicate key value violates unique constraint "atacc1_pkey"
576 DETAIL: Key (test)=(3) already exists.
577 insert into atacc1 (test2, test) values (1, NULL);
578 ERROR: null value in column "test" violates not-null constraint
580 -- alter table / alter column [set/drop] not null tests
581 -- try altering system catalogs, should fail
582 alter table pg_class alter column relname drop not null;
583 ERROR: permission denied: "pg_class" is a system catalog
584 alter table pg_class alter relname set not null;
585 ERROR: permission denied: "pg_class" is a system catalog
586 -- try altering non-existent table, should fail
587 alter table non_existent alter column bar set not null;
588 ERROR: relation "non_existent" does not exist
589 alter table non_existent alter column bar drop not null;
590 ERROR: relation "non_existent" does not exist
591 -- test setting columns to null and not null and vice versa
592 -- test checking for null values and primary key
593 create table atacc1 (test int not null) with oids;
594 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
595 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
596 alter table atacc1 alter column test drop not null;
597 ERROR: column "test" is in a primary key
598 alter table atacc1 drop constraint "atacc1_pkey";
599 alter table atacc1 alter column test drop not null;
600 insert into atacc1 values (null);
601 alter table atacc1 alter test set not null;
602 ERROR: column "test" contains null values
604 alter table atacc1 alter test set not null;
605 -- try altering a non-existent column, should fail
606 alter table atacc1 alter bar set not null;
607 ERROR: column "bar" of relation "atacc1" does not exist
608 alter table atacc1 alter bar drop not null;
609 ERROR: column "bar" of relation "atacc1" does not exist
610 -- try altering the oid column, should fail
611 alter table atacc1 alter oid set not null;
612 ERROR: cannot alter system column "oid"
613 alter table atacc1 alter oid drop not null;
614 ERROR: cannot alter system column "oid"
615 -- try creating a view and altering that, should fail
616 create view myview as select * from atacc1;
617 alter table myview alter column test drop not null;
618 ERROR: "myview" is not a table or foreign table
619 alter table myview alter column test set not null;
620 ERROR: "myview" is not a table or foreign table
624 create table parent (a int);
625 create table child (b varchar(255)) inherits (parent);
626 alter table parent alter a set not null;
627 insert into parent values (NULL);
628 ERROR: null value in column "a" violates not-null constraint
629 insert into child (a, b) values (NULL, 'foo');
630 ERROR: null value in column "a" violates not-null constraint
631 alter table parent alter a drop not null;
632 insert into parent values (NULL);
633 insert into child (a, b) values (NULL, 'foo');
634 alter table only parent alter a set not null;
635 ERROR: column "a" contains null values
636 alter table child alter a set not null;
637 ERROR: column "a" contains null values
639 alter table only parent alter a set not null;
640 insert into parent values (NULL);
641 ERROR: null value in column "a" violates not-null constraint
642 alter table child alter a set not null;
643 insert into child (a, b) values (NULL, 'foo');
644 ERROR: null value in column "a" violates not-null constraint
646 alter table child alter a set not null;
647 insert into child (a, b) values (NULL, 'foo');
648 ERROR: null value in column "a" violates not-null constraint
651 -- test setting and removing default values
652 create table def_test (
654 c2 text default 'initial_default'
656 insert into def_test default values;
657 alter table def_test alter column c1 drop default;
658 insert into def_test default values;
659 alter table def_test alter column c2 drop default;
660 insert into def_test default values;
661 alter table def_test alter column c1 set default 10;
662 alter table def_test alter column c2 set default 'new_default';
663 insert into def_test default values;
664 select * from def_test;
666 ----+-----------------
673 -- set defaults to an incorrect type: this should fail
674 alter table def_test alter column c1 set default 'wrong_datatype';
675 ERROR: invalid input syntax for integer: "wrong_datatype"
676 alter table def_test alter column c2 set default 20;
677 -- set defaults on a non-existent column: this should fail
678 alter table def_test alter column c3 set default 30;
679 ERROR: column "c3" of relation "def_test" does not exist
680 -- set defaults on views: we need to create a view, add a rule
681 -- to allow insertions into it, and then alter the view to add
683 create view def_view_test as select * from def_test;
684 create rule def_view_test_ins as
685 on insert to def_view_test
686 do instead insert into def_test select new.*;
687 insert into def_view_test default values;
688 alter table def_view_test alter column c1 set default 45;
689 insert into def_view_test default values;
690 alter table def_view_test alter column c2 set default 'view_default';
691 insert into def_view_test default values;
692 select * from def_view_test;
694 ----+-----------------
704 drop rule def_view_test_ins on def_view_test;
705 drop view def_view_test;
707 -- alter table / drop column tests
708 -- try altering system catalogs, should fail
709 alter table pg_class drop column relname;
710 ERROR: permission denied: "pg_class" is a system catalog
711 -- try altering non-existent table, should fail
712 alter table nosuchtable drop column bar;
713 ERROR: relation "nosuchtable" does not exist
714 -- test dropping columns
715 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
716 insert into atacc1 values (1, 2, 3, 4);
717 alter table atacc1 drop a;
718 alter table atacc1 drop a;
719 ERROR: column "a" of relation "atacc1" does not exist
721 select * from atacc1;
727 select * from atacc1 order by a;
728 ERROR: column "a" does not exist
729 LINE 1: select * from atacc1 order by a;
731 select * from atacc1 order by "........pg.dropped.1........";
732 ERROR: column "........pg.dropped.1........" does not exist
733 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
735 select * from atacc1 group by a;
736 ERROR: column "a" does not exist
737 LINE 1: select * from atacc1 group by a;
739 select * from atacc1 group by "........pg.dropped.1........";
740 ERROR: column "........pg.dropped.1........" does not exist
741 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
743 select atacc1.* from atacc1;
749 select a from atacc1;
750 ERROR: column "a" does not exist
751 LINE 1: select a from atacc1;
753 select atacc1.a from atacc1;
754 ERROR: column atacc1.a does not exist
755 LINE 1: select atacc1.a from atacc1;
757 select b,c,d from atacc1;
763 select a,b,c,d from atacc1;
764 ERROR: column "a" does not exist
765 LINE 1: select a,b,c,d from atacc1;
767 select * from atacc1 where a = 1;
768 ERROR: column "a" does not exist
769 LINE 1: select * from atacc1 where a = 1;
771 select "........pg.dropped.1........" from atacc1;
772 ERROR: column "........pg.dropped.1........" does not exist
773 LINE 1: select "........pg.dropped.1........" from atacc1;
775 select atacc1."........pg.dropped.1........" from atacc1;
776 ERROR: column atacc1.........pg.dropped.1........ does not exist
777 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
779 select "........pg.dropped.1........",b,c,d from atacc1;
780 ERROR: column "........pg.dropped.1........" does not exist
781 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
783 select * from atacc1 where "........pg.dropped.1........" = 1;
784 ERROR: column "........pg.dropped.1........" does not exist
785 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
788 update atacc1 set a = 3;
789 ERROR: column "a" of relation "atacc1" does not exist
790 LINE 1: update atacc1 set a = 3;
792 update atacc1 set b = 2 where a = 3;
793 ERROR: column "a" does not exist
794 LINE 1: update atacc1 set b = 2 where a = 3;
796 update atacc1 set "........pg.dropped.1........" = 3;
797 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
798 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
800 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
801 ERROR: column "........pg.dropped.1........" does not exist
802 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
805 insert into atacc1 values (10, 11, 12, 13);
806 ERROR: INSERT has more expressions than target columns
807 LINE 1: insert into atacc1 values (10, 11, 12, 13);
809 insert into atacc1 values (default, 11, 12, 13);
810 ERROR: INSERT has more expressions than target columns
811 LINE 1: insert into atacc1 values (default, 11, 12, 13);
813 insert into atacc1 values (11, 12, 13);
814 insert into atacc1 (a) values (10);
815 ERROR: column "a" of relation "atacc1" does not exist
816 LINE 1: insert into atacc1 (a) values (10);
818 insert into atacc1 (a) values (default);
819 ERROR: column "a" of relation "atacc1" does not exist
820 LINE 1: insert into atacc1 (a) values (default);
822 insert into atacc1 (a,b,c,d) values (10,11,12,13);
823 ERROR: column "a" of relation "atacc1" does not exist
824 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
826 insert into atacc1 (a,b,c,d) values (default,11,12,13);
827 ERROR: column "a" of relation "atacc1" does not exist
828 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
830 insert into atacc1 (b,c,d) values (11,12,13);
831 insert into atacc1 ("........pg.dropped.1........") values (10);
832 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
833 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
835 insert into atacc1 ("........pg.dropped.1........") values (default);
836 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
837 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
839 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
840 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
841 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
843 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
844 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
845 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
848 delete from atacc1 where a = 3;
849 ERROR: column "a" does not exist
850 LINE 1: delete from atacc1 where a = 3;
852 delete from atacc1 where "........pg.dropped.1........" = 3;
853 ERROR: column "........pg.dropped.1........" does not exist
854 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
857 -- try dropping a non-existent column, should fail
858 alter table atacc1 drop bar;
859 ERROR: column "bar" of relation "atacc1" does not exist
860 -- try dropping the oid column, should succeed
861 alter table atacc1 drop oid;
862 -- try dropping the xmin column, should fail
863 alter table atacc1 drop xmin;
864 ERROR: cannot drop system column "xmin"
865 -- try creating a view and altering that, should fail
866 create view myview as select * from atacc1;
867 select * from myview;
872 alter table myview drop d;
873 ERROR: "myview" is not a table, composite type, or foreign table
875 -- test some commands to make sure they fail on the dropped column
877 ERROR: column "a" of relation "atacc1" does not exist
878 analyze atacc1("........pg.dropped.1........");
879 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
880 vacuum analyze atacc1(a);
881 ERROR: column "a" of relation "atacc1" does not exist
882 vacuum analyze atacc1("........pg.dropped.1........");
883 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
884 comment on column atacc1.a is 'testing';
885 ERROR: column "a" of relation "atacc1" does not exist
886 comment on column atacc1."........pg.dropped.1........" is 'testing';
887 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
888 alter table atacc1 alter a set storage plain;
889 ERROR: column "a" of relation "atacc1" does not exist
890 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
891 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
892 alter table atacc1 alter a set statistics 0;
893 ERROR: column "a" of relation "atacc1" does not exist
894 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
895 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
896 alter table atacc1 alter a set default 3;
897 ERROR: column "a" of relation "atacc1" does not exist
898 alter table atacc1 alter "........pg.dropped.1........" set default 3;
899 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
900 alter table atacc1 alter a drop default;
901 ERROR: column "a" of relation "atacc1" does not exist
902 alter table atacc1 alter "........pg.dropped.1........" drop default;
903 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
904 alter table atacc1 alter a set not null;
905 ERROR: column "a" of relation "atacc1" does not exist
906 alter table atacc1 alter "........pg.dropped.1........" set not null;
907 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
908 alter table atacc1 alter a drop not null;
909 ERROR: column "a" of relation "atacc1" does not exist
910 alter table atacc1 alter "........pg.dropped.1........" drop not null;
911 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
912 alter table atacc1 rename a to x;
913 ERROR: column "a" does not exist
914 alter table atacc1 rename "........pg.dropped.1........" to x;
915 ERROR: column "........pg.dropped.1........" does not exist
916 alter table atacc1 add primary key(a);
917 ERROR: column "a" named in key does not exist
918 alter table atacc1 add primary key("........pg.dropped.1........");
919 ERROR: column "........pg.dropped.1........" named in key does not exist
920 alter table atacc1 add unique(a);
921 ERROR: column "a" named in key does not exist
922 alter table atacc1 add unique("........pg.dropped.1........");
923 ERROR: column "........pg.dropped.1........" named in key does not exist
924 alter table atacc1 add check (a > 3);
925 ERROR: column "a" does not exist
926 alter table atacc1 add check ("........pg.dropped.1........" > 3);
927 ERROR: column "........pg.dropped.1........" does not exist
928 create table atacc2 (id int4 unique);
929 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
930 alter table atacc1 add foreign key (a) references atacc2(id);
931 ERROR: column "a" referenced in foreign key constraint does not exist
932 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
933 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
934 alter table atacc2 add foreign key (id) references atacc1(a);
935 ERROR: column "a" referenced in foreign key constraint does not exist
936 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
937 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
939 create index "testing_idx" on atacc1(a);
940 ERROR: column "a" does not exist
941 create index "testing_idx" on atacc1("........pg.dropped.1........");
942 ERROR: column "........pg.dropped.1........" does not exist
943 -- test create as and select into
944 insert into atacc1 values (21, 22, 23);
945 create table test1 as select * from atacc1;
953 select * into test2 from atacc1;
961 -- try dropping all columns
962 alter table atacc1 drop c;
963 alter table atacc1 drop d;
964 alter table atacc1 drop b;
965 select * from atacc1;
971 create table parent (a int, b int, c int);
972 insert into parent values (1, 2, 3);
973 alter table parent drop a;
974 create table child (d varchar(255)) inherits (parent);
975 insert into child values (12, 13, 'testing');
976 select * from parent;
989 alter table parent drop c;
990 select * from parent;
1006 create table test (a int4, b int4, c int4);
1007 insert into test values (1,2,3);
1008 alter table test drop a;
1009 copy test to stdout;
1011 copy test(a) to stdout;
1012 ERROR: column "a" of relation "test" does not exist
1013 copy test("........pg.dropped.1........") to stdout;
1014 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
1015 copy test from stdin;
1016 ERROR: extra data after last expected column
1017 CONTEXT: COPY test, line 1: "10 11 12"
1024 copy test from stdin;
1032 copy test(a) from stdin;
1033 ERROR: column "a" of relation "test" does not exist
1034 copy test("........pg.dropped.1........") from stdin;
1035 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
1036 copy test(b,c) from stdin;
1047 create table dropColumn (a int, b int, e int);
1048 create table dropColumnChild (c int) inherits (dropColumn);
1049 create table dropColumnAnother (d int) inherits (dropColumnChild);
1050 -- these two should fail
1051 alter table dropColumnchild drop column a;
1052 ERROR: cannot drop inherited column "a"
1053 alter table only dropColumnChild drop column b;
1054 ERROR: cannot drop inherited column "b"
1055 -- these three should work
1056 alter table only dropColumn drop column e;
1057 alter table dropColumnChild drop column c;
1058 alter table dropColumn drop column a;
1059 create table renameColumn (a int);
1060 create table renameColumnChild (b int) inherits (renameColumn);
1061 create table renameColumnAnother (c int) inherits (renameColumnChild);
1062 -- these three should fail
1063 alter table renameColumnChild rename column a to d;
1064 ERROR: cannot rename inherited column "a"
1065 alter table only renameColumnChild rename column a to d;
1066 ERROR: inherited column "a" must be renamed in child tables too
1067 alter table only renameColumn rename column a to d;
1068 ERROR: inherited column "a" must be renamed in child tables too
1069 -- these should work
1070 alter table renameColumn rename column a to d;
1071 alter table renameColumnChild rename column b to a;
1073 alter table renameColumn add column w int;
1075 alter table only renameColumn add column x int;
1076 ERROR: column must be added to child tables too
1077 -- Test corner cases in dropping of inherited columns
1078 create table p1 (f1 int, f2 int);
1079 create table c1 (f1 int not null) inherits(p1);
1080 NOTICE: merging column "f1" with inherited definition
1081 -- should be rejected since c1.f1 is inherited
1082 alter table c1 drop column f1;
1083 ERROR: cannot drop inherited column "f1"
1085 alter table p1 drop column f1;
1086 -- c1.f1 is still there, but no longer inherited
1092 alter table c1 drop column f1;
1094 ERROR: column "f1" does not exist
1095 LINE 1: select f1 from c1;
1097 drop table p1 cascade;
1098 NOTICE: drop cascades to table c1
1099 create table p1 (f1 int, f2 int);
1100 create table c1 () inherits(p1);
1101 -- should be rejected since c1.f1 is inherited
1102 alter table c1 drop column f1;
1103 ERROR: cannot drop inherited column "f1"
1104 alter table p1 drop column f1;
1105 -- c1.f1 is dropped now, since there is no local definition for it
1107 ERROR: column "f1" does not exist
1108 LINE 1: select f1 from c1;
1110 drop table p1 cascade;
1111 NOTICE: drop cascades to table c1
1112 create table p1 (f1 int, f2 int);
1113 create table c1 () inherits(p1);
1114 -- should be rejected since c1.f1 is inherited
1115 alter table c1 drop column f1;
1116 ERROR: cannot drop inherited column "f1"
1117 alter table only p1 drop column f1;
1118 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1119 alter table c1 drop column f1;
1120 drop table p1 cascade;
1121 NOTICE: drop cascades to table c1
1122 create table p1 (f1 int, f2 int);
1123 create table c1 (f1 int not null) inherits(p1);
1124 NOTICE: merging column "f1" with inherited definition
1125 -- should be rejected since c1.f1 is inherited
1126 alter table c1 drop column f1;
1127 ERROR: cannot drop inherited column "f1"
1128 alter table only p1 drop column f1;
1129 -- c1.f1 is still there, but no longer inherited
1130 alter table c1 drop column f1;
1131 drop table p1 cascade;
1132 NOTICE: drop cascades to table c1
1133 create table p1(id int, name text);
1134 create table p2(id2 int, name text, height int);
1135 create table c1(age int) inherits(p1,p2);
1136 NOTICE: merging multiple inherited definitions of column "name"
1137 create table gc1() inherits (c1);
1138 select relname, attname, attinhcount, attislocal
1139 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1140 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1141 order by relname, attnum;
1142 relname | attname | attinhcount | attislocal
1143 ---------+---------+-------------+------------
1152 gc1 | height | 1 | f
1162 alter table only p1 drop column name;
1163 -- should work. Now c1.name is local and inhcount is 0.
1164 alter table p2 drop column name;
1165 -- should be rejected since its inherited
1166 alter table gc1 drop column name;
1167 ERROR: cannot drop inherited column "name"
1168 -- should work, and drop gc1.name along
1169 alter table c1 drop column name;
1170 -- should fail: column does not exist
1171 alter table gc1 drop column name;
1172 ERROR: column "name" of relation "gc1" does not exist
1173 -- should work and drop the attribute in all tables
1174 alter table p2 drop column height;
1176 create table dropColumnExists ();
1177 alter table dropColumnExists drop column non_existing; --fail
1178 ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
1179 alter table dropColumnExists drop column if exists non_existing; --succeed
1180 NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
1181 select relname, attname, attinhcount, attislocal
1182 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1183 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1184 order by relname, attnum;
1185 relname | attname | attinhcount | attislocal
1186 ---------+---------+-------------+------------
1197 drop table p1, p2 cascade;
1198 NOTICE: drop cascades to 2 other objects
1199 DETAIL: drop cascades to table c1
1200 drop cascades to table gc1
1201 -- test attinhcount tracking with merged columns
1202 create table depth0();
1203 create table depth1(c text) inherits (depth0);
1204 create table depth2() inherits (depth1);
1205 alter table depth0 add c text;
1206 NOTICE: merging definition of column "c" for child "depth1"
1207 select attrelid::regclass, attname, attinhcount, attislocal
1209 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1210 order by attrelid::regclass::text, attnum;
1211 attrelid | attname | attinhcount | attislocal
1212 ----------+---------+-------------+------------
1219 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
1221 create table altstartwith (col integer) with oids;
1222 insert into altstartwith values (1);
1223 select oid > 0, * from altstartwith;
1229 alter table altstartwith set without oids;
1230 select oid > 0, * from altstartwith; -- fails
1231 ERROR: column "oid" does not exist
1232 LINE 1: select oid > 0, * from altstartwith;
1234 select * from altstartwith;
1240 alter table altstartwith set with oids;
1241 select oid > 0, * from altstartwith;
1247 drop table altstartwith;
1248 -- Check inheritance cases
1249 create table altwithoid (col integer) with oids;
1250 -- Inherits parents oid column anyway
1251 create table altinhoid () inherits (altwithoid) without oids;
1252 insert into altinhoid values (1);
1253 select oid > 0, * from altwithoid;
1259 select oid > 0, * from altinhoid;
1265 alter table altwithoid set without oids;
1266 select oid > 0, * from altwithoid; -- fails
1267 ERROR: column "oid" does not exist
1268 LINE 1: select oid > 0, * from altwithoid;
1270 select oid > 0, * from altinhoid; -- fails
1271 ERROR: column "oid" does not exist
1272 LINE 1: select oid > 0, * from altinhoid;
1274 select * from altwithoid;
1280 select * from altinhoid;
1286 alter table altwithoid set with oids;
1287 select oid > 0, * from altwithoid;
1293 select oid > 0, * from altinhoid;
1299 drop table altwithoid cascade;
1300 NOTICE: drop cascades to table altinhoid
1301 create table altwithoid (col integer) without oids;
1302 -- child can have local oid column
1303 create table altinhoid () inherits (altwithoid) with oids;
1304 insert into altinhoid values (1);
1305 select oid > 0, * from altwithoid; -- fails
1306 ERROR: column "oid" does not exist
1307 LINE 1: select oid > 0, * from altwithoid;
1309 select oid > 0, * from altinhoid;
1315 alter table altwithoid set with oids;
1316 NOTICE: merging definition of column "oid" for child "altinhoid"
1317 select oid > 0, * from altwithoid;
1323 select oid > 0, * from altinhoid;
1329 -- the child's local definition should remain
1330 alter table altwithoid set without oids;
1331 select oid > 0, * from altwithoid; -- fails
1332 ERROR: column "oid" does not exist
1333 LINE 1: select oid > 0, * from altwithoid;
1335 select oid > 0, * from altinhoid;
1341 drop table altwithoid cascade;
1342 NOTICE: drop cascades to table altinhoid
1343 -- test renumbering of child-table columns in inherited operations
1344 create table p1 (f1 int);
1345 create table c1 (f2 text, f3 int) inherits (p1);
1346 alter table p1 add column a1 int check (a1 > 0);
1347 alter table p1 add column f2 text;
1348 NOTICE: merging definition of column "f2" for child "c1"
1349 insert into p1 values (1,2,'abc');
1350 insert into c1 values(11,'xyz',33,0); -- should fail
1351 ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1352 insert into c1 values(11,'xyz',33,22);
1360 update p1 set a1 = a1 + 1, f2 = upper(f2);
1368 drop table p1 cascade;
1369 NOTICE: drop cascades to table c1
1370 -- test that operations with a dropped column do not try to reference
1372 create domain mytype as text;
1373 create temp table foo (f1 text, f2 mytype, f3 text);
1374 insert into foo values('bb','cc','dd');
1381 drop domain mytype cascade;
1382 NOTICE: drop cascades to table foo column f2
1389 insert into foo values('qq','rr');
1397 update foo set f3 = 'zz';
1405 select f3,max(f1) from foo group by f3;
1411 -- Simple tests for alter table column type
1412 alter table foo alter f1 TYPE integer; -- fails
1413 ERROR: column "f1" cannot be cast to type integer
1414 alter table foo alter f1 TYPE varchar(10);
1415 create table anothertab (atcol1 serial8, atcol2 boolean,
1416 constraint anothertab_chk check (atcol1 <= 3));
1417 NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1"
1418 insert into anothertab (atcol1, atcol2) values (default, true);
1419 insert into anothertab (atcol1, atcol2) values (default, false);
1420 select * from anothertab;
1427 alter table anothertab alter column atcol1 type boolean; -- fails
1428 ERROR: column "atcol1" cannot be cast to type boolean
1429 alter table anothertab alter column atcol1 type integer;
1430 select * from anothertab;
1437 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1438 ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1439 insert into anothertab (atcol1, atcol2) values (default, null);
1440 select * from anothertab;
1448 alter table anothertab alter column atcol2 type text
1449 using case when atcol2 is true then 'IT WAS TRUE'
1450 when atcol2 is false then 'IT WAS FALSE'
1451 else 'IT WAS NULL!' end;
1452 select * from anothertab;
1454 --------+--------------
1460 alter table anothertab alter column atcol1 type boolean
1461 using case when atcol1 % 2 = 0 then true else false end; -- fails
1462 ERROR: default for column "atcol1" cannot be cast to type boolean
1463 alter table anothertab alter column atcol1 drop default;
1464 alter table anothertab alter column atcol1 type boolean
1465 using case when atcol1 % 2 = 0 then true else false end; -- fails
1466 ERROR: operator does not exist: boolean <= integer
1467 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
1468 alter table anothertab drop constraint anothertab_chk;
1469 alter table anothertab drop constraint anothertab_chk; -- fails
1470 ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
1471 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1472 NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
1473 alter table anothertab alter column atcol1 type boolean
1474 using case when atcol1 % 2 = 0 then true else false end;
1475 select * from anothertab;
1477 --------+--------------
1483 drop table anothertab;
1484 create table another (f1 int, f2 text);
1485 insert into another values(1, 'one');
1486 insert into another values(2, 'two');
1487 insert into another values(3, 'three');
1488 select * from another;
1497 alter f1 type text using f2 || ' more',
1498 alter f2 type bigint using f1 * 10;
1499 select * from another;
1509 create table tab1 (a int, b text);
1510 create table tab2 (x int, y tab1);
1511 alter table tab1 alter column b type varchar; -- fails
1512 ERROR: cannot alter table "tab1" because column "tab2"."y" uses its rowtype
1513 -- disallow recursive containment of row types
1514 create temp table recur1 (f1 int);
1515 alter table recur1 add column f2 recur1; -- fails
1516 ERROR: composite type recur1 cannot be made a member of itself
1517 alter table recur1 add column f2 recur1[]; -- fails
1518 ERROR: composite type recur1 cannot be made a member of itself
1519 create temp table recur2 (f1 int, f2 recur1);
1520 alter table recur1 add column f2 recur2; -- fails
1521 ERROR: composite type recur1 cannot be made a member of itself
1522 alter table recur1 add column f2 int;
1523 alter table recur1 alter column f2 type recur2; -- fails
1524 ERROR: composite type recur1 cannot be made a member of itself
1525 -- SET STORAGE may need to add a TOAST table
1526 create table test_storage (a text);
1527 alter table test_storage alter a set storage plain;
1528 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1529 alter table test_storage alter a set storage extended; -- re-add TOAST table
1530 select reltoastrelid <> 0 as has_toast_table
1532 where oid = 'test_storage'::regclass;
1541 drop type lockmodes;
1542 ERROR: type "lockmodes" does not exist
1543 create type lockmodes as enum (
1547 ,'ShareUpdateExclusiveLock'
1549 ,'ShareRowExclusiveLock'
1551 ,'AccessExclusiveLock'
1554 ERROR: view "my_locks" does not exist
1555 create or replace view my_locks as
1556 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1557 from pg_locks l join pg_class c on l.relation = c.oid
1558 where virtualtransaction = (
1559 select virtualtransaction
1561 where transactionid = txid_current()::integer)
1562 and locktype = 'relation'
1563 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1564 and c.relname != 'my_locks'
1566 create table alterlock (f1 int primary key, f2 text);
1567 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "alterlock_pkey" for table "alterlock"
1568 -- share update exclusive
1569 begin; alter table alterlock alter column f2 set statistics 150;
1570 select * from my_locks order by 1;
1571 relname | max_lockmode
1572 -----------+--------------------------
1573 alterlock | ShareUpdateExclusiveLock
1577 begin; alter table alterlock cluster on alterlock_pkey;
1578 select * from my_locks order by 1;
1579 relname | max_lockmode
1580 ----------------+--------------------------
1581 alterlock | ShareUpdateExclusiveLock
1582 alterlock_pkey | ShareUpdateExclusiveLock
1586 begin; alter table alterlock set without cluster;
1587 select * from my_locks order by 1;
1588 relname | max_lockmode
1589 -----------+--------------------------
1590 alterlock | ShareUpdateExclusiveLock
1594 begin; alter table alterlock set (fillfactor = 100);
1595 select * from my_locks order by 1;
1596 relname | max_lockmode
1597 -----------+--------------------------
1598 alterlock | ShareUpdateExclusiveLock
1599 pg_toast | ShareUpdateExclusiveLock
1603 begin; alter table alterlock reset (fillfactor);
1604 select * from my_locks order by 1;
1605 relname | max_lockmode
1606 -----------+--------------------------
1607 alterlock | ShareUpdateExclusiveLock
1608 pg_toast | ShareUpdateExclusiveLock
1612 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1613 select * from my_locks order by 1;
1614 relname | max_lockmode
1615 -----------+--------------------------
1616 alterlock | ShareUpdateExclusiveLock
1617 pg_toast | ShareUpdateExclusiveLock
1621 begin; alter table alterlock set (autovacuum_enabled = off);
1622 select * from my_locks order by 1;
1623 relname | max_lockmode
1624 -----------+--------------------------
1625 alterlock | ShareUpdateExclusiveLock
1626 pg_toast | ShareUpdateExclusiveLock
1630 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1631 select * from my_locks order by 1;
1632 relname | max_lockmode
1633 -----------+--------------------------
1634 alterlock | ShareUpdateExclusiveLock
1638 begin; alter table alterlock alter column f2 set storage extended;
1639 select * from my_locks order by 1;
1640 relname | max_lockmode
1641 -----------+--------------------------
1642 alterlock | ShareUpdateExclusiveLock
1646 -- share row exclusive
1647 begin; alter table alterlock alter column f2 set default 'x';
1648 select * from my_locks order by 1;
1649 relname | max_lockmode
1650 -----------+-----------------------
1651 alterlock | ShareRowExclusiveLock
1656 drop table alterlock;
1658 drop type lockmodes;
1662 create function test_strict(text) returns text as
1663 'select coalesce($1, ''got passed a null'');'
1664 language sql returns null on null input;
1665 select test_strict(NULL);
1671 alter function test_strict(text) called on null input;
1672 select test_strict(NULL);
1678 create function non_strict(text) returns text as
1679 'select coalesce($1, ''got passed a null'');'
1680 language sql called on null input;
1681 select non_strict(NULL);
1687 alter function non_strict(text) returns null on null input;
1688 select non_strict(NULL);
1695 -- alter object set schema
1697 create schema alter1;
1698 create schema alter2;
1699 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1700 NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
1701 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
1702 create view alter1.v1 as select * from alter1.t1;
1703 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1704 create domain alter1.posint integer check (value > 0);
1705 create type alter1.ctype as (f1 int, f2 text);
1706 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1707 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1708 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
1709 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1710 operator 1 alter1.=(alter1.ctype, alter1.ctype);
1711 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1712 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1713 create text search configuration alter1.cfg(parser = alter1.prs);
1714 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1715 create text search dictionary alter1.dict(template = alter1.tmpl);
1716 insert into alter1.t1(f2) values(11);
1717 insert into alter1.t1(f2) values(12);
1718 alter table alter1.t1 set schema alter2;
1719 alter table alter1.v1 set schema alter2;
1720 alter function alter1.plus1(int) set schema alter2;
1721 alter domain alter1.posint set schema alter2;
1722 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1723 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1724 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1725 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1726 alter type alter1.ctype set schema alter2;
1727 alter conversion alter1.ascii_to_utf8 set schema alter2;
1728 alter text search parser alter1.prs set schema alter2;
1729 alter text search configuration alter1.cfg set schema alter2;
1730 alter text search template alter1.tmpl set schema alter2;
1731 alter text search dictionary alter1.dict set schema alter2;
1732 -- this should succeed because nothing is left in alter1
1734 insert into alter2.t1(f2) values(13);
1735 insert into alter2.t1(f2) values(14);
1736 select * from alter2.t1;
1745 select * from alter2.v1;
1754 select alter2.plus1(41);
1761 drop schema alter2 cascade;
1762 NOTICE: drop cascades to 13 other objects
1763 DETAIL: drop cascades to table alter2.t1
1764 drop cascades to view alter2.v1
1765 drop cascades to function alter2.plus1(integer)
1766 drop cascades to type alter2.posint
1767 drop cascades to operator family alter2.ctype_hash_ops for access method hash
1768 drop cascades to type alter2.ctype
1769 drop cascades to function alter2.same(alter2.ctype,alter2.ctype)
1770 drop cascades to operator alter2.=(alter2.ctype,alter2.ctype)
1771 drop cascades to conversion ascii_to_utf8
1772 drop cascades to text search parser prs
1773 drop cascades to text search configuration cfg
1774 drop cascades to text search template tmpl
1775 drop cascades to text search dictionary dict
1779 CREATE TYPE test_type AS (a int);
1781 Composite type "public.test_type"
1782 Column | Type | Modifiers
1783 --------+---------+-----------
1786 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1787 ERROR: relation "nosuchtype" does not exist
1788 ALTER TYPE test_type ADD ATTRIBUTE b text;
1790 Composite type "public.test_type"
1791 Column | Type | Modifiers
1792 --------+---------+-----------
1796 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1797 ERROR: column "b" of relation "test_type" already exists
1798 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1800 Composite type "public.test_type"
1801 Column | Type | Modifiers
1802 --------+-------------------+-----------
1804 b | character varying |
1806 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1808 Composite type "public.test_type"
1809 Column | Type | Modifiers
1810 --------+---------+-----------
1814 ALTER TYPE test_type DROP ATTRIBUTE b;
1816 Composite type "public.test_type"
1817 Column | Type | Modifiers
1818 --------+---------+-----------
1821 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1822 ERROR: column "c" of relation "test_type" does not exist
1823 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1824 NOTICE: column "c" of relation "test_type" does not exist, skipping
1825 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1827 Composite type "public.test_type"
1828 Column | Type | Modifiers
1829 --------+---------+-----------
1832 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1833 ERROR: column "a" does not exist
1834 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1836 Composite type "public.test_type"
1837 Column | Type | Modifiers
1838 --------+---------+-----------
1841 DROP TYPE test_type;
1842 CREATE TYPE test_type1 AS (a int, b text);
1843 CREATE TABLE test_tbl1 (x int, y test_type1);
1844 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1845 ERROR: cannot alter type "test_type1" because column "test_tbl1"."y" uses it
1846 CREATE TYPE test_type2 AS (a int, b text);
1847 CREATE TABLE test_tbl2 OF test_type2;
1848 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
1850 Composite type "public.test_type2"
1851 Column | Type | Modifiers
1852 --------+---------+-----------
1857 Table "public.test_tbl2"
1858 Column | Type | Modifiers
1859 --------+---------+-----------
1862 Number of child tables: 1 (Use \d+ to list them.)
1863 Typed table of type: test_type2
1865 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1866 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1867 HINT: Use ALTER ... CASCADE to alter the typed tables too.
1868 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1870 Composite type "public.test_type2"
1871 Column | Type | Modifiers
1872 --------+---------+-----------
1878 Table "public.test_tbl2"
1879 Column | Type | Modifiers
1880 --------+---------+-----------
1884 Number of child tables: 1 (Use \d+ to list them.)
1885 Typed table of type: test_type2
1887 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1888 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1889 HINT: Use ALTER ... CASCADE to alter the typed tables too.
1890 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1892 Composite type "public.test_type2"
1893 Column | Type | Modifiers
1894 --------+-------------------+-----------
1896 b | character varying |
1900 Table "public.test_tbl2"
1901 Column | Type | Modifiers
1902 --------+-------------------+-----------
1904 b | character varying |
1906 Number of child tables: 1 (Use \d+ to list them.)
1907 Typed table of type: test_type2
1909 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1910 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1911 HINT: Use ALTER ... CASCADE to alter the typed tables too.
1912 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
1914 Composite type "public.test_type2"
1915 Column | Type | Modifiers
1916 --------+---------+-----------
1921 Table "public.test_tbl2"
1922 Column | Type | Modifiers
1923 --------+---------+-----------
1926 Number of child tables: 1 (Use \d+ to list them.)
1927 Typed table of type: test_type2
1929 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
1930 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1931 HINT: Use ALTER ... CASCADE to alter the typed tables too.
1932 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
1934 Composite type "public.test_type2"
1935 Column | Type | Modifiers
1936 --------+---------+-----------
1941 Table "public.test_tbl2"
1942 Column | Type | Modifiers
1943 --------+---------+-----------
1946 Number of child tables: 1 (Use \d+ to list them.)
1947 Typed table of type: test_type2
1949 \d test_tbl2_subclass
1950 Table "public.test_tbl2_subclass"
1951 Column | Type | Modifiers
1952 --------+---------+-----------
1957 DROP TABLE test_tbl2_subclass;
1958 CREATE TYPE test_type_empty AS ();
1959 DROP TYPE test_type_empty;
1961 -- typed tables: OF / NOT OF
1963 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
1964 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
1965 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
1966 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
1967 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
1968 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
1969 CREATE TABLE tt4 (x int); -- too few columns
1970 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
1971 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
1972 CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
1973 ALTER TABLE tt7 DROP q; -- OK
1974 ALTER TABLE tt0 OF tt_t0;
1975 ALTER TABLE tt1 OF tt_t0;
1976 ERROR: table "tt1" has different type for column "y"
1977 ALTER TABLE tt2 OF tt_t0;
1978 ERROR: table "tt2" has different type for column "y"
1979 ALTER TABLE tt3 OF tt_t0;
1980 ERROR: table has column "y" where type requires "x"
1981 ALTER TABLE tt4 OF tt_t0;
1982 ERROR: table is missing column "y"
1983 ALTER TABLE tt5 OF tt_t0;
1984 ERROR: table has extra column "z"
1985 ALTER TABLE tt6 OF tt_t0;
1986 ERROR: typed tables cannot inherit
1987 ALTER TABLE tt7 OF tt_t0;
1988 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
1989 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
1990 ALTER TABLE tt7 NOT OF;
1993 Column | Type | Modifiers
1994 --------+--------------+-----------