| blob | 388e25e9dbad9dec8c293b2713c37e79ed87c0b5 |
1 --
2 -- ALTER_TABLE
3 -- add attribute
4 --
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,
37 v, w, x, y, z)
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}');
44 SELECT * FROM tmp;
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}
48 (1 row)
50 DROP TABLE tmp;
51 -- the wolf bug - schema mods caused inconsistent row descriptors
52 CREATE TABLE tmp (
53 initial int4
54 );
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,
82 v, w, x, y, z)
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}');
89 SELECT * FROM tmp;
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}
93 (1 row)
95 DROP TABLE tmp;
96 --
97 -- rename - check on both non-temp and temp tables
98 --
99 CREATE TABLE tmp (regtable int);
100 CREATE TEMP TABLE tmp (tmptable int);
101 ALTER TABLE tmp RENAME TO tmp_new;
102 SELECT * FROM tmp;
103 regtable
104 ----------
105 (0 rows)
107 SELECT * FROM tmp_new;
108 tmptable
109 ----------
110 (0 rows)
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;
116 ^
117 SELECT * FROM tmp_new;
118 tmptable
119 ----------
120 (0 rows)
122 SELECT * FROM tmp_new2;
123 regtable
124 ----------
125 (0 rows)
127 DROP TABLE tmp_new;
128 DROP TABLE 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;
133 -- renaming views
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
137 ANALYZE tenk1;
138 set enable_seqscan to off;
139 set enable_bitmapscan to off;
140 -- 5 values, sorted
141 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
142 unique1
143 ---------
144 0
145 1
146 2
147 3
148 4
149 (5 rows)
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_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;
185 -- Try (and succeed)
186 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
187 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
188 -- tmp4 is a,b
189 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
190 ERROR: there is no unique constraint matching given keys for referenced table "tmp4"
191 DROP TABLE tmp5;
192 DROP TABLE tmp4;
193 DROP TABLE tmp3;
194 DROP TABLE tmp2;
195 -- Foreign key adding test with mixed types
196 -- Note: these tables are TEMP to avoid name conflicts when this test
197 -- is run in parallel with foreign_key.sql.
198 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
199 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
200 INSERT INTO PKTABLE VALUES(42);
201 CREATE TEMP TABLE FKTABLE (ftest1 inet);
202 -- This next should fail, because int=inet does not exist
203 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
204 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
205 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
206 -- This should also fail for the same reason, but here we
207 -- give the column name
208 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
209 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
210 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
211 DROP TABLE FKTABLE;
212 -- This should succeed, even though they are different types,
213 -- because int=int8 exists and is a member of the integer opfamily
214 CREATE TEMP TABLE FKTABLE (ftest1 int8);
215 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
216 -- Check it actually works
217 INSERT INTO FKTABLE VALUES(42); -- should succeed
218 INSERT INTO FKTABLE VALUES(43); -- should fail
219 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
220 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
221 DROP TABLE FKTABLE;
222 -- This should fail, because we'd have to cast numeric to int which is
223 -- not an implicit coercion (or use numeric=numeric, but that's not part
224 -- of the integer opfamily)
225 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
226 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
227 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
228 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
229 DROP TABLE FKTABLE;
230 DROP TABLE PKTABLE;
231 -- On the other hand, this should work because int implicitly promotes to
232 -- numeric, and we allow promotion on the FK side
233 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
234 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
235 INSERT INTO PKTABLE VALUES(42);
236 CREATE TEMP TABLE FKTABLE (ftest1 int);
237 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
238 -- Check it actually works
239 INSERT INTO FKTABLE VALUES(42); -- should succeed
240 INSERT INTO FKTABLE VALUES(43); -- should fail
241 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
242 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
243 DROP TABLE FKTABLE;
244 DROP TABLE PKTABLE;
245 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
246 PRIMARY KEY(ptest1, ptest2));
247 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
248 -- This should fail, because we just chose really odd types
249 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
250 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
251 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
252 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
253 DROP TABLE FKTABLE;
254 -- Again, so should this...
255 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
256 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
257 references pktable(ptest1, ptest2);
258 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
259 DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
260 DROP TABLE FKTABLE;
261 -- This fails because we mixed up the column ordering
262 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
263 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
264 references pktable(ptest2, ptest1);
265 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
266 DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
267 -- As does this...
268 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
269 references pktable(ptest1, ptest2);
270 ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
271 DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
272 -- temp tables should go away by themselves, need not drop them.
273 -- test check constraint adding
274 create table atacc1 ( test int );
275 -- add a check constraint
276 alter table atacc1 add constraint atacc_test1 check (test>3);
277 -- should fail
278 insert into atacc1 (test) values (2);
279 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
280 -- should succeed
281 insert into atacc1 (test) values (4);
282 drop table atacc1;
283 -- let's do one where the check fails when added
284 create table atacc1 ( test int );
285 -- insert a soon to be failing row
286 insert into atacc1 (test) values (2);
287 -- add a check constraint (fails)
288 alter table atacc1 add constraint atacc_test1 check (test>3);
289 ERROR: check constraint "atacc_test1" is violated by some row
290 insert into atacc1 (test) values (4);
291 drop table atacc1;
292 -- let's do one where the check fails because the column doesn't exist
293 create table atacc1 ( test int );
294 -- add a check constraint (fails)
295 alter table atacc1 add constraint atacc_test1 check (test1>3);
296 ERROR: column "test1" does not exist
297 drop table atacc1;
298 -- something a little more complicated
299 create table atacc1 ( test int, test2 int, test3 int);
300 -- add a check constraint (fails)
301 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
302 -- should fail
303 insert into atacc1 (test,test2,test3) values (4,4,2);
304 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
305 -- should succeed
306 insert into atacc1 (test,test2,test3) values (4,4,5);
307 drop table atacc1;
308 -- lets do some naming tests
309 create table atacc1 (test int check (test>3), test2 int);
310 alter table atacc1 add check (test2>test);
311 -- should fail for $2
312 insert into atacc1 (test2, test) values (3, 4);
313 ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
314 drop table atacc1;
315 -- inheritance related tests
316 create table atacc1 (test int);
317 create table atacc2 (test2 int);
318 create table atacc3 (test3 int) inherits (atacc1, atacc2);
319 alter table atacc2 add constraint foo check (test2>0);
320 -- fail and then succeed on atacc2
321 insert into atacc2 (test2) values (-3);
322 ERROR: new row for relation "atacc2" violates check constraint "foo"
323 insert into atacc2 (test2) values (3);
324 -- fail and then succeed on atacc3
325 insert into atacc3 (test2) values (-3);
326 ERROR: new row for relation "atacc3" violates check constraint "foo"
327 insert into atacc3 (test2) values (3);
328 drop table atacc3;
329 drop table atacc2;
330 drop table atacc1;
331 -- same things with one created with INHERIT
332 create table atacc1 (test int);
333 create table atacc2 (test2 int);
334 create table atacc3 (test3 int) inherits (atacc1, atacc2);
335 alter table atacc3 no inherit atacc2;
336 -- fail
337 alter table atacc3 no inherit atacc2;
338 ERROR: relation "atacc2" is not a parent of relation "atacc3"
339 -- make sure it really isn't a child
340 insert into atacc3 (test2) values (3);
341 select test2 from atacc2;
342 test2
343 -------
344 (0 rows)
346 -- fail due to missing constraint
347 alter table atacc2 add constraint foo check (test2>0);
348 alter table atacc3 inherit atacc2;
349 ERROR: child table is missing constraint "foo"
350 -- fail due to missing column
351 alter table atacc3 rename test2 to testx;
352 alter table atacc3 inherit atacc2;
353 ERROR: child table is missing column "test2"
354 -- fail due to mismatched data type
355 alter table atacc3 add test2 bool;
356 alter table atacc3 inherit atacc2;
357 ERROR: child table "atacc3" has different type for column "test2"
358 alter table atacc3 drop test2;
359 -- succeed
360 alter table atacc3 add test2 int;
361 update atacc3 set test2 = 4 where test2 is null;
362 alter table atacc3 add constraint foo check (test2>0);
363 alter table atacc3 inherit atacc2;
364 -- fail due to duplicates and circular inheritance
365 alter table atacc3 inherit atacc2;
366 ERROR: relation "atacc2" would be inherited from more than once
367 alter table atacc2 inherit atacc3;
368 ERROR: circular inheritance not allowed
369 DETAIL: "atacc3" is already a child of "atacc2".
370 alter table atacc2 inherit atacc2;
371 ERROR: circular inheritance not allowed
372 DETAIL: "atacc2" is already a child of "atacc2".
373 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
374 select test2 from atacc2;
375 test2
376 -------
377 4
378 (1 row)
380 drop table atacc2 cascade;
381 NOTICE: drop cascades to table atacc3
382 drop table atacc1;
383 -- adding only to a parent is disallowed as of 8.4
384 create table atacc1 (test int);
385 create table atacc2 (test2 int) inherits (atacc1);
386 -- fail:
387 alter table only atacc1 add constraint foo check (test>0);
388 ERROR: constraint must be added to child tables too
389 -- ok:
390 alter table only atacc2 add constraint foo check (test>0);
391 -- check constraint not there on parent
392 insert into atacc1 (test) values (-3);
393 insert into atacc1 (test) values (3);
394 -- check constraint is there on child
395 insert into atacc2 (test) values (-3);
396 ERROR: new row for relation "atacc2" violates check constraint "foo"
397 insert into atacc2 (test) values (3);
398 drop table atacc2;
399 drop table atacc1;
400 -- test unique constraint adding
401 create table atacc1 ( test int ) with oids;
402 -- add a unique constraint
403 alter table atacc1 add constraint atacc_test1 unique (test);
404 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
405 -- insert first value
406 insert into atacc1 (test) values (2);
407 -- should fail
408 insert into atacc1 (test) values (2);
409 ERROR: duplicate key value violates unique constraint "atacc_test1"
410 -- should succeed
411 insert into atacc1 (test) values (4);
412 -- try adding a unique oid constraint
413 alter table atacc1 add constraint atacc_oid1 unique(oid);
414 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"
415 drop table atacc1;
416 -- let's do one where the unique constraint fails when added
417 create table atacc1 ( test int );
418 -- insert soon to be failing rows
419 insert into atacc1 (test) values (2);
420 insert into atacc1 (test) values (2);
421 -- add a unique constraint (fails)
422 alter table atacc1 add constraint atacc_test1 unique (test);
423 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
424 ERROR: could not create unique index "atacc_test1"
425 DETAIL: Table contains duplicated values.
426 insert into atacc1 (test) values (3);
427 drop table atacc1;
428 -- let's do one where the unique constraint fails
429 -- because the column doesn't exist
430 create table atacc1 ( test int );
431 -- add a unique constraint (fails)
432 alter table atacc1 add constraint atacc_test1 unique (test1);
433 ERROR: column "test1" named in key does not exist
434 drop table atacc1;
435 -- something a little more complicated
436 create table atacc1 ( test int, test2 int);
437 -- add a unique constraint
438 alter table atacc1 add constraint atacc_test1 unique (test, test2);
439 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
440 -- insert initial value
441 insert into atacc1 (test,test2) values (4,4);
442 -- should fail
443 insert into atacc1 (test,test2) values (4,4);
444 ERROR: duplicate key value violates unique constraint "atacc_test1"
445 -- should all succeed
446 insert into atacc1 (test,test2) values (4,5);
447 insert into atacc1 (test,test2) values (5,4);
448 insert into atacc1 (test,test2) values (5,5);
449 drop table atacc1;
450 -- lets do some naming tests
451 create table atacc1 (test int, test2 int, unique(test));
452 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
453 alter table atacc1 add unique (test2);
454 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
455 -- should fail for @@ second one @@
456 insert into atacc1 (test2, test) values (3, 3);
457 insert into atacc1 (test2, test) values (2, 3);
458 ERROR: duplicate key value violates unique constraint "atacc1_test_key"
459 drop table atacc1;
460 -- test primary key constraint adding
461 create table atacc1 ( test int ) with oids;
462 -- add a primary key constraint
463 alter table atacc1 add constraint atacc_test1 primary key (test);
464 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
465 -- insert first value
466 insert into atacc1 (test) values (2);
467 -- should fail
468 insert into atacc1 (test) values (2);
469 ERROR: duplicate key value violates unique constraint "atacc_test1"
470 -- should succeed
471 insert into atacc1 (test) values (4);
472 -- inserting NULL should fail
473 insert into atacc1 (test) values(NULL);
474 ERROR: null value in column "test" violates not-null constraint
475 -- try adding a second primary key (should fail)
476 alter table atacc1 add constraint atacc_oid1 primary key(oid);
477 ERROR: multiple primary keys for table "atacc1" are not allowed
478 -- drop first primary key constraint
479 alter table atacc1 drop constraint atacc_test1 restrict;
480 -- try adding a primary key on oid (should succeed)
481 alter table atacc1 add constraint atacc_oid1 primary key(oid);
482 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
483 drop table atacc1;
484 -- let's do one where the primary key constraint fails when added
485 create table atacc1 ( test int );
486 -- insert soon to be failing rows
487 insert into atacc1 (test) values (2);
488 insert into atacc1 (test) values (2);
489 -- add a primary key (fails)
490 alter table atacc1 add constraint atacc_test1 primary key (test);
491 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
492 ERROR: could not create unique index "atacc_test1"
493 DETAIL: Table contains duplicated values.
494 insert into atacc1 (test) values (3);
495 drop table atacc1;
496 -- let's do another one where the primary key constraint fails when added
497 create table atacc1 ( test int );
498 -- insert soon to be failing row
499 insert into atacc1 (test) values (NULL);
500 -- add a primary key (fails)
501 alter table atacc1 add constraint atacc_test1 primary key (test);
502 ERROR: column "test" contains null values
503 insert into atacc1 (test) values (3);
504 drop table atacc1;
505 -- let's do one where the primary key constraint fails
506 -- because the column doesn't exist
507 create table atacc1 ( test int );
508 -- add a primary key constraint (fails)
509 alter table atacc1 add constraint atacc_test1 primary key (test1);
510 ERROR: column "test1" named in key does not exist
511 drop table atacc1;
512 -- adding a new column as primary key to a non-empty table.
513 -- should fail unless the column has a non-null default value.
514 create table atacc1 ( test int );
515 insert into atacc1 (test) values (0);
516 -- add a primary key column without a default (fails).
517 alter table atacc1 add column test2 int primary key;
518 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
519 ERROR: column "test2" contains null values
520 -- now add a primary key column with a default (succeeds).
521 alter table atacc1 add column test2 int default 0 primary key;
522 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
523 drop table atacc1;
524 -- something a little more complicated
525 create table atacc1 ( test int, test2 int);
526 -- add a primary key constraint
527 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
528 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
529 -- try adding a second primary key - should fail
530 alter table atacc1 add constraint atacc_test2 primary key (test);
531 ERROR: multiple primary keys for table "atacc1" are not allowed
532 -- insert initial value
533 insert into atacc1 (test,test2) values (4,4);
534 -- should fail
535 insert into atacc1 (test,test2) values (4,4);
536 ERROR: duplicate key value violates unique constraint "atacc_test1"
537 insert into atacc1 (test,test2) values (NULL,3);
538 ERROR: null value in column "test" violates not-null constraint
539 insert into atacc1 (test,test2) values (3, NULL);
540 ERROR: null value in column "test2" violates not-null constraint
541 insert into atacc1 (test,test2) values (NULL,NULL);
542 ERROR: null value in column "test" violates not-null constraint
543 -- should all succeed
544 insert into atacc1 (test,test2) values (4,5);
545 insert into atacc1 (test,test2) values (5,4);
546 insert into atacc1 (test,test2) values (5,5);
547 drop table atacc1;
548 -- lets do some naming tests
549 create table atacc1 (test int, test2 int, primary key(test));
550 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
551 -- only first should succeed
552 insert into atacc1 (test2, test) values (3, 3);
553 insert into atacc1 (test2, test) values (2, 3);
554 ERROR: duplicate key value violates unique constraint "atacc1_pkey"
555 insert into atacc1 (test2, test) values (1, NULL);
556 ERROR: null value in column "test" violates not-null constraint
557 drop table atacc1;
558 -- alter table / alter column [set/drop] not null tests
559 -- try altering system catalogs, should fail
560 alter table pg_class alter column relname drop not null;
561 ERROR: permission denied: "pg_class" is a system catalog
562 alter table pg_class alter relname set not null;
563 ERROR: permission denied: "pg_class" is a system catalog
564 -- try altering non-existent table, should fail
565 alter table non_existent alter column bar set not null;
566 ERROR: relation "non_existent" does not exist
567 alter table non_existent alter column bar drop not null;
568 ERROR: relation "non_existent" does not exist
569 -- test setting columns to null and not null and vice versa
570 -- test checking for null values and primary key
571 create table atacc1 (test int not null) with oids;
572 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
573 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
574 alter table atacc1 alter column test drop not null;
575 ERROR: column "test" is in a primary key
576 alter table atacc1 drop constraint "atacc1_pkey";
577 alter table atacc1 alter column test drop not null;
578 insert into atacc1 values (null);
579 alter table atacc1 alter test set not null;
580 ERROR: column "test" contains null values
581 delete from atacc1;
582 alter table atacc1 alter test set not null;
583 -- try altering a non-existent column, should fail
584 alter table atacc1 alter bar set not null;
585 ERROR: column "bar" of relation "atacc1" does not exist
586 alter table atacc1 alter bar drop not null;
587 ERROR: column "bar" of relation "atacc1" does not exist
588 -- try altering the oid column, should fail
589 alter table atacc1 alter oid set not null;
590 ERROR: cannot alter system column "oid"
591 alter table atacc1 alter oid drop not null;
592 ERROR: cannot alter system column "oid"
593 -- try creating a view and altering that, should fail
594 create view myview as select * from atacc1;
595 alter table myview alter column test drop not null;
596 ERROR: "myview" is not a table
597 alter table myview alter column test set not null;
598 ERROR: "myview" is not a table
599 drop view myview;
600 drop table atacc1;
601 -- test inheritance
602 create table parent (a int);
603 create table child (b varchar(255)) inherits (parent);
604 alter table parent alter a set not null;
605 insert into parent values (NULL);
606 ERROR: null value in column "a" violates not-null constraint
607 insert into child (a, b) values (NULL, 'foo');
608 ERROR: null value in column "a" violates not-null constraint
609 alter table parent alter a drop not null;
610 insert into parent values (NULL);
611 insert into child (a, b) values (NULL, 'foo');
612 alter table only parent alter a set not null;
613 ERROR: column "a" contains null values
614 alter table child alter a set not null;
615 ERROR: column "a" contains null values
616 delete from parent;
617 alter table only parent alter a set not null;
618 insert into parent values (NULL);
619 ERROR: null value in column "a" violates not-null constraint
620 alter table child alter a set not null;
621 insert into child (a, b) values (NULL, 'foo');
622 ERROR: null value in column "a" violates not-null constraint
623 delete from child;
624 alter table child alter a set not null;
625 insert into child (a, b) values (NULL, 'foo');
626 ERROR: null value in column "a" violates not-null constraint
627 drop table child;
628 drop table parent;
629 -- test setting and removing default values
630 create table def_test (
631 c1 int4 default 5,
632 c2 text default 'initial_default'
633 );
634 insert into def_test default values;
635 alter table def_test alter column c1 drop default;
636 insert into def_test default values;
637 alter table def_test alter column c2 drop default;
638 insert into def_test default values;
639 alter table def_test alter column c1 set default 10;
640 alter table def_test alter column c2 set default 'new_default';
641 insert into def_test default values;
642 select * from def_test;
643 c1 | c2
644 ----+-----------------
645 5 | initial_default
646 | initial_default
647 |
648 10 | new_default
649 (4 rows)
651 -- set defaults to an incorrect type: this should fail
652 alter table def_test alter column c1 set default 'wrong_datatype';
653 ERROR: invalid input syntax for integer: "wrong_datatype"
654 alter table def_test alter column c2 set default 20;
655 -- set defaults on a non-existent column: this should fail
656 alter table def_test alter column c3 set default 30;
657 ERROR: column "c3" of relation "def_test" does not exist
658 -- set defaults on views: we need to create a view, add a rule
659 -- to allow insertions into it, and then alter the view to add
660 -- a default
661 create view def_view_test as select * from def_test;
662 create rule def_view_test_ins as
663 on insert to def_view_test
664 do instead insert into def_test select new.*;
665 insert into def_view_test default values;
666 alter table def_view_test alter column c1 set default 45;
667 insert into def_view_test default values;
668 alter table def_view_test alter column c2 set default 'view_default';
669 insert into def_view_test default values;
670 select * from def_view_test;
671 c1 | c2
672 ----+-----------------
673 5 | initial_default
674 | initial_default
675 |
676 10 | new_default
677 |
678 45 |
679 45 | view_default
680 (7 rows)
682 drop rule def_view_test_ins on def_view_test;
683 drop view def_view_test;
684 drop table def_test;
685 -- alter table / drop column tests
686 -- try altering system catalogs, should fail
687 alter table pg_class drop column relname;
688 ERROR: permission denied: "pg_class" is a system catalog
689 -- try altering non-existent table, should fail
690 alter table nosuchtable drop column bar;
691 ERROR: relation "nosuchtable" does not exist
692 -- test dropping columns
693 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
694 insert into atacc1 values (1, 2, 3, 4);
695 alter table atacc1 drop a;
696 alter table atacc1 drop a;
697 ERROR: column "a" of relation "atacc1" does not exist
698 -- SELECTs
699 select * from atacc1;
700 b | c | d
701 ---+---+---
702 2 | 3 | 4
703 (1 row)
705 select * from atacc1 order by a;
706 ERROR: column "a" does not exist
707 LINE 1: select * from atacc1 order by a;
708 ^
709 select * from atacc1 order by "........pg.dropped.1........";
710 ERROR: column "........pg.dropped.1........" does not exist
711 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
712 ^
713 select * from atacc1 group by a;
714 ERROR: column "a" does not exist
715 LINE 1: select * from atacc1 group by a;
716 ^
717 select * from atacc1 group by "........pg.dropped.1........";
718 ERROR: column "........pg.dropped.1........" does not exist
719 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
720 ^
721 select atacc1.* from atacc1;
722 b | c | d
723 ---+---+---
724 2 | 3 | 4
725 (1 row)
727 select a from atacc1;
728 ERROR: column "a" does not exist
729 LINE 1: select a from atacc1;
730 ^
731 select atacc1.a from atacc1;
732 ERROR: column atacc1.a does not exist
733 LINE 1: select atacc1.a from atacc1;
734 ^
735 select b,c,d from atacc1;
736 b | c | d
737 ---+---+---
738 2 | 3 | 4
739 (1 row)
741 select a,b,c,d from atacc1;
742 ERROR: column "a" does not exist
743 LINE 1: select a,b,c,d from atacc1;
744 ^
745 select * from atacc1 where a = 1;
746 ERROR: column "a" does not exist
747 LINE 1: select * from atacc1 where a = 1;
748 ^
749 select "........pg.dropped.1........" from atacc1;
750 ERROR: column "........pg.dropped.1........" does not exist
751 LINE 1: select "........pg.dropped.1........" from atacc1;
752 ^
753 select atacc1."........pg.dropped.1........" from atacc1;
754 ERROR: column atacc1.........pg.dropped.1........ does not exist
755 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
756 ^
757 select "........pg.dropped.1........",b,c,d from atacc1;
758 ERROR: column "........pg.dropped.1........" does not exist
759 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
760 ^
761 select * from atacc1 where "........pg.dropped.1........" = 1;
762 ERROR: column "........pg.dropped.1........" does not exist
763 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
764 ^
765 -- UPDATEs
766 update atacc1 set a = 3;
767 ERROR: column "a" of relation "atacc1" does not exist
768 LINE 1: update atacc1 set a = 3;
769 ^
770 update atacc1 set b = 2 where a = 3;
771 ERROR: column "a" does not exist
772 LINE 1: update atacc1 set b = 2 where a = 3;
773 ^
774 update atacc1 set "........pg.dropped.1........" = 3;
775 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
776 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
777 ^
778 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
779 ERROR: column "........pg.dropped.1........" does not exist
780 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
781 ^
782 -- INSERTs
783 insert into atacc1 values (10, 11, 12, 13);
784 ERROR: INSERT has more expressions than target columns
785 LINE 1: insert into atacc1 values (10, 11, 12, 13);
786 ^
787 insert into atacc1 values (default, 11, 12, 13);
788 ERROR: INSERT has more expressions than target columns
789 LINE 1: insert into atacc1 values (default, 11, 12, 13);
790 ^
791 insert into atacc1 values (11, 12, 13);
792 insert into atacc1 (a) values (10);
793 ERROR: column "a" of relation "atacc1" does not exist
794 LINE 1: insert into atacc1 (a) values (10);
795 ^
796 insert into atacc1 (a) values (default);
797 ERROR: column "a" of relation "atacc1" does not exist
798 LINE 1: insert into atacc1 (a) values (default);
799 ^
800 insert into atacc1 (a,b,c,d) values (10,11,12,13);
801 ERROR: column "a" of relation "atacc1" does not exist
802 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
803 ^
804 insert into atacc1 (a,b,c,d) values (default,11,12,13);
805 ERROR: column "a" of relation "atacc1" does not exist
806 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
807 ^
808 insert into atacc1 (b,c,d) values (11,12,13);
809 insert into atacc1 ("........pg.dropped.1........") values (10);
810 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
811 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
812 ^
813 insert into atacc1 ("........pg.dropped.1........") values (default);
814 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
815 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
816 ^
817 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
818 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
819 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
820 ^
821 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
822 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
823 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
824 ^
825 -- DELETEs
826 delete from atacc1 where a = 3;
827 ERROR: column "a" does not exist
828 LINE 1: delete from atacc1 where a = 3;
829 ^
830 delete from atacc1 where "........pg.dropped.1........" = 3;
831 ERROR: column "........pg.dropped.1........" does not exist
832 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
833 ^
834 delete from atacc1;
835 -- try dropping a non-existent column, should fail
836 alter table atacc1 drop bar;
837 ERROR: column "bar" of relation "atacc1" does not exist
838 -- try dropping the oid column, should succeed
839 alter table atacc1 drop oid;
840 -- try dropping the xmin column, should fail
841 alter table atacc1 drop xmin;
842 ERROR: cannot drop system column "xmin"
843 -- try creating a view and altering that, should fail
844 create view myview as select * from atacc1;
845 select * from myview;
846 b | c | d
847 ---+---+---
848 (0 rows)
850 alter table myview drop d;
851 ERROR: "myview" is not a table
852 drop view myview;
853 -- test some commands to make sure they fail on the dropped column
854 analyze atacc1(a);
855 ERROR: column "a" of relation "atacc1" does not exist
856 analyze atacc1("........pg.dropped.1........");
857 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
858 vacuum analyze atacc1(a);
859 ERROR: column "a" of relation "atacc1" does not exist
860 vacuum analyze atacc1("........pg.dropped.1........");
861 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
862 comment on column atacc1.a is 'testing';
863 ERROR: column "a" of relation "atacc1" does not exist
864 comment on column atacc1."........pg.dropped.1........" is 'testing';
865 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
866 alter table atacc1 alter a set storage plain;
867 ERROR: column "a" of relation "atacc1" does not exist
868 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
869 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
870 alter table atacc1 alter a set statistics 0;
871 ERROR: column "a" of relation "atacc1" does not exist
872 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
873 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
874 alter table atacc1 alter a set default 3;
875 ERROR: column "a" of relation "atacc1" does not exist
876 alter table atacc1 alter "........pg.dropped.1........" set default 3;
877 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
878 alter table atacc1 alter a drop default;
879 ERROR: column "a" of relation "atacc1" does not exist
880 alter table atacc1 alter "........pg.dropped.1........" drop default;
881 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
882 alter table atacc1 alter a set not null;
883 ERROR: column "a" of relation "atacc1" does not exist
884 alter table atacc1 alter "........pg.dropped.1........" set not null;
885 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
886 alter table atacc1 alter a drop not null;
887 ERROR: column "a" of relation "atacc1" does not exist
888 alter table atacc1 alter "........pg.dropped.1........" drop not null;
889 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
890 alter table atacc1 rename a to x;
891 ERROR: column "a" does not exist
892 alter table atacc1 rename "........pg.dropped.1........" to x;
893 ERROR: column "........pg.dropped.1........" does not exist
894 alter table atacc1 add primary key(a);
895 ERROR: column "a" named in key does not exist
896 alter table atacc1 add primary key("........pg.dropped.1........");
897 ERROR: column "........pg.dropped.1........" named in key does not exist
898 alter table atacc1 add unique(a);
899 ERROR: column "a" named in key does not exist
900 alter table atacc1 add unique("........pg.dropped.1........");
901 ERROR: column "........pg.dropped.1........" named in key does not exist
902 alter table atacc1 add check (a > 3);
903 ERROR: column "a" does not exist
904 alter table atacc1 add check ("........pg.dropped.1........" > 3);
905 ERROR: column "........pg.dropped.1........" does not exist
906 create table atacc2 (id int4 unique);
907 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
908 alter table atacc1 add foreign key (a) references atacc2(id);
909 ERROR: column "a" referenced in foreign key constraint does not exist
910 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
911 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
912 alter table atacc2 add foreign key (id) references atacc1(a);
913 ERROR: column "a" referenced in foreign key constraint does not exist
914 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
915 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
916 drop table atacc2;
917 create index "testing_idx" on atacc1(a);
918 ERROR: column "a" does not exist
919 create index "testing_idx" on atacc1("........pg.dropped.1........");
920 ERROR: column "........pg.dropped.1........" does not exist
921 -- test create as and select into
922 insert into atacc1 values (21, 22, 23);
923 create table test1 as select * from atacc1;
924 select * from test1;
925 b | c | d
926 ----+----+----
927 21 | 22 | 23
928 (1 row)
930 drop table test1;
931 select * into test2 from atacc1;
932 select * from test2;
933 b | c | d
934 ----+----+----
935 21 | 22 | 23
936 (1 row)
938 drop table test2;
939 -- try dropping all columns
940 alter table atacc1 drop c;
941 alter table atacc1 drop d;
942 alter table atacc1 drop b;
943 select * from atacc1;
944 --
945 (1 row)
947 drop table atacc1;
948 -- test inheritance
949 create table parent (a int, b int, c int);
950 insert into parent values (1, 2, 3);
951 alter table parent drop a;
952 create table child (d varchar(255)) inherits (parent);
953 insert into child values (12, 13, 'testing');
954 select * from parent;
955 b | c
956 ----+----
957 2 | 3
958 12 | 13
959 (2 rows)
961 select * from child;
962 b | c | d
963 ----+----+---------
964 12 | 13 | testing
965 (1 row)
967 alter table parent drop c;
968 select * from parent;
969 b
970 ----
971 2
972 12
973 (2 rows)
975 select * from child;
976 b | d
977 ----+---------
978 12 | testing
979 (1 row)
981 drop table child;
982 drop table parent;
983 -- test copy in/out
984 create table test (a int4, b int4, c int4);
985 insert into test values (1,2,3);
986 alter table test drop a;
987 copy test to stdout;
988 2 3
989 copy test(a) to stdout;
990 ERROR: column "a" of relation "test" does not exist
991 copy test("........pg.dropped.1........") to stdout;
992 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
993 copy test from stdin;
994 ERROR: extra data after last expected column
995 CONTEXT: COPY test, line 1: "10 11 12"
996 select * from test;
997 b | c
998 ---+---
999 2 | 3
1000 (1 row)
1002 copy test from stdin;
1003 select * from test;
1004 b | c
1005 ----+----
1006 2 | 3
1007 21 | 22
1008 (2 rows)
1010 copy test(a) from stdin;
1011 ERROR: column "a" of relation "test" does not exist
1012 copy test("........pg.dropped.1........") from stdin;
1013 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
1014 copy test(b,c) from stdin;
1015 select * from test;
1016 b | c
1017 ----+----
1018 2 | 3
1019 21 | 22
1020 31 | 32
1021 (3 rows)
1023 drop table test;
1024 -- test inheritance
1025 create table dropColumn (a int, b int, e int);
1026 create table dropColumnChild (c int) inherits (dropColumn);
1027 create table dropColumnAnother (d int) inherits (dropColumnChild);
1028 -- these two should fail
1029 alter table dropColumnchild drop column a;
1030 ERROR: cannot drop inherited column "a"
1031 alter table only dropColumnChild drop column b;
1032 ERROR: cannot drop inherited column "b"
1033 -- these three should work
1034 alter table only dropColumn drop column e;
1035 alter table dropColumnChild drop column c;
1036 alter table dropColumn drop column a;
1037 create table renameColumn (a int);
1038 create table renameColumnChild (b int) inherits (renameColumn);
1039 create table renameColumnAnother (c int) inherits (renameColumnChild);
1040 -- these three should fail
1041 alter table renameColumnChild rename column a to d;
1042 ERROR: cannot rename inherited column "a"
1043 alter table only renameColumnChild rename column a to d;
1044 ERROR: inherited column "a" must be renamed in child tables too
1045 alter table only renameColumn rename column a to d;
1046 ERROR: inherited column "a" must be renamed in child tables too
1047 -- these should work
1048 alter table renameColumn rename column a to d;
1049 alter table renameColumnChild rename column b to a;
1050 -- this should work
1051 alter table renameColumn add column w int;
1052 -- this should fail
1053 alter table only renameColumn add column x int;
1054 ERROR: column must be added to child tables too
1055 -- Test corner cases in dropping of inherited columns
1056 create table p1 (f1 int, f2 int);
1057 create table c1 (f1 int not null) inherits(p1);
1058 NOTICE: merging column "f1" with inherited definition
1059 -- should be rejected since c1.f1 is inherited
1060 alter table c1 drop column f1;
1061 ERROR: cannot drop inherited column "f1"
1062 -- should work
1063 alter table p1 drop column f1;
1064 -- c1.f1 is still there, but no longer inherited
1065 select f1 from c1;
1066 f1
1067 ----
1068 (0 rows)
1070 alter table c1 drop column f1;
1071 select f1 from c1;
1072 ERROR: column "f1" does not exist
1073 LINE 1: select f1 from c1;
1074 ^
1075 drop table p1 cascade;
1076 NOTICE: drop cascades to table c1
1077 create table p1 (f1 int, f2 int);
1078 create table c1 () inherits(p1);
1079 -- should be rejected since c1.f1 is inherited
1080 alter table c1 drop column f1;
1081 ERROR: cannot drop inherited column "f1"
1082 alter table p1 drop column f1;
1083 -- c1.f1 is dropped now, since there is no local definition for it
1084 select f1 from c1;
1085 ERROR: column "f1" does not exist
1086 LINE 1: select f1 from c1;
1087 ^
1088 drop table p1 cascade;
1089 NOTICE: drop cascades to table c1
1090 create table p1 (f1 int, f2 int);
1091 create table c1 () inherits(p1);
1092 -- should be rejected since c1.f1 is inherited
1093 alter table c1 drop column f1;
1094 ERROR: cannot drop inherited column "f1"
1095 alter table only p1 drop column f1;
1096 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1097 alter table c1 drop column f1;
1098 drop table p1 cascade;
1099 NOTICE: drop cascades to table c1
1100 create table p1 (f1 int, f2 int);
1101 create table c1 (f1 int not null) inherits(p1);
1102 NOTICE: merging column "f1" with inherited definition
1103 -- should be rejected since c1.f1 is inherited
1104 alter table c1 drop column f1;
1105 ERROR: cannot drop inherited column "f1"
1106 alter table only p1 drop column f1;
1107 -- c1.f1 is still there, but no longer inherited
1108 alter table c1 drop column f1;
1109 drop table p1 cascade;
1110 NOTICE: drop cascades to table c1
1111 create table p1(id int, name text);
1112 create table p2(id2 int, name text, height int);
1113 create table c1(age int) inherits(p1,p2);
1114 NOTICE: merging multiple inherited definitions of column "name"
1115 create table gc1() inherits (c1);
1116 select relname, attname, attinhcount, attislocal
1117 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1118 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1119 order by relname, attnum;
1120 relname | attname | attinhcount | attislocal
1121 ---------+---------+-------------+------------
1122 c1 | id | 1 | f
1123 c1 | name | 2 | f
1124 c1 | id2 | 1 | f
1125 c1 | height | 1 | f
1126 c1 | age | 0 | t
1127 gc1 | id | 1 | f
1128 gc1 | name | 1 | f
1129 gc1 | id2 | 1 | f
1130 gc1 | height | 1 | f
1131 gc1 | age | 1 | f
1132 p1 | id | 0 | t
1133 p1 | name | 0 | t
1134 p2 | id2 | 0 | t
1135 p2 | name | 0 | t
1136 p2 | height | 0 | t
1137 (15 rows)
1139 -- should work
1140 alter table only p1 drop column name;
1141 -- should work. Now c1.name is local and inhcount is 0.
1142 alter table p2 drop column name;
1143 -- should be rejected since its inherited
1144 alter table gc1 drop column name;
1145 ERROR: cannot drop inherited column "name"
1146 -- should work, and drop gc1.name along
1147 alter table c1 drop column name;
1148 -- should fail: column does not exist
1149 alter table gc1 drop column name;
1150 ERROR: column "name" of relation "gc1" does not exist
1151 -- should work and drop the attribute in all tables
1152 alter table p2 drop column height;
1153 select relname, attname, attinhcount, attislocal
1154 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1155 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1156 order by relname, attnum;
1157 relname | attname | attinhcount | attislocal
1158 ---------+---------+-------------+------------
1159 c1 | id | 1 | f
1160 c1 | id2 | 1 | f
1161 c1 | age | 0 | t
1162 gc1 | id | 1 | f
1163 gc1 | id2 | 1 | f
1164 gc1 | age | 1 | f
1165 p1 | id | 0 | t
1166 p2 | id2 | 0 | t
1167 (8 rows)
1169 drop table p1, p2 cascade;
1170 NOTICE: drop cascades to 2 other objects
1171 DETAIL: drop cascades to table c1
1172 drop cascades to table gc1
1173 --
1174 -- Test the ALTER TABLE WITHOUT OIDS command
1175 --
1176 create table altstartwith (col integer) with oids;
1177 insert into altstartwith values (1);
1178 select oid > 0, * from altstartwith;
1179 ?column? | col
1180 ----------+-----
1181 t | 1
1182 (1 row)
1184 alter table altstartwith set without oids;
1185 select oid > 0, * from altstartwith; -- fails
1186 ERROR: column "oid" does not exist
1187 LINE 1: select oid > 0, * from altstartwith;
1188 ^
1189 select * from altstartwith;
1190 col
1191 -----
1192 1
1193 (1 row)
1195 -- Run inheritance tests
1196 create table altwithoid (col integer) with oids;
1197 -- Inherits parents oid column
1198 create table altinhoid () inherits (altwithoid) without oids;
1199 insert into altinhoid values (1);
1200 select oid > 0, * from altwithoid;
1201 ?column? | col
1202 ----------+-----
1203 t | 1
1204 (1 row)
1206 select oid > 0, * from altinhoid;
1207 ?column? | col
1208 ----------+-----
1209 t | 1
1210 (1 row)
1212 alter table altwithoid set without oids;
1213 alter table altinhoid set without oids;
1214 select oid > 0, * from altwithoid; -- fails
1215 ERROR: column "oid" does not exist
1216 LINE 1: select oid > 0, * from altwithoid;
1217 ^
1218 select oid > 0, * from altinhoid; -- fails
1219 ERROR: column "oid" does not exist
1220 LINE 1: select oid > 0, * from altinhoid;
1221 ^
1222 select * from altwithoid;
1223 col
1224 -----
1225 1
1226 (1 row)
1228 select * from altinhoid;
1229 col
1230 -----
1231 1
1232 (1 row)
1234 -- test renumbering of child-table columns in inherited operations
1235 create table p1 (f1 int);
1236 create table c1 (f2 text, f3 int) inherits (p1);
1237 alter table p1 add column a1 int check (a1 > 0);
1238 alter table p1 add column f2 text;
1239 NOTICE: merging definition of column "f2" for child "c1"
1240 insert into p1 values (1,2,'abc');
1241 insert into c1 values(11,'xyz',33,0); -- should fail
1242 ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1243 insert into c1 values(11,'xyz',33,22);
1244 select * from p1;
1245 f1 | a1 | f2
1246 ----+----+-----
1247 1 | 2 | abc
1248 11 | 22 | xyz
1249 (2 rows)
1251 update p1 set a1 = a1 + 1, f2 = upper(f2);
1252 select * from p1;
1253 f1 | a1 | f2
1254 ----+----+-----
1255 1 | 3 | ABC
1256 11 | 23 | XYZ
1257 (2 rows)
1259 drop table p1 cascade;
1260 NOTICE: drop cascades to table c1
1261 -- test that operations with a dropped column do not try to reference
1262 -- its datatype
1263 create domain mytype as text;
1264 create temp table foo (f1 text, f2 mytype, f3 text);
1265 insert into foo values('aa','bb','cc');
1266 select * from foo;
1267 f1 | f2 | f3
1268 ----+----+----
1269 aa | bb | cc
1270 (1 row)
1272 drop domain mytype cascade;
1273 NOTICE: drop cascades to table foo column f2
1274 select * from foo;
1275 f1 | f3
1276 ----+----
1277 aa | cc
1278 (1 row)
1280 insert into foo values('qq','rr');
1281 select * from foo;
1282 f1 | f3
1283 ----+----
1284 aa | cc
1285 qq | rr
1286 (2 rows)
1288 update foo set f3 = 'zz';
1289 select * from foo;
1290 f1 | f3
1291 ----+----
1292 aa | zz
1293 qq | zz
1294 (2 rows)
1296 select f3,max(f1) from foo group by f3;
1297 f3 | max
1298 ----+-----
1299 zz | qq
1300 (1 row)
1302 -- Simple tests for alter table column type
1303 alter table foo alter f1 TYPE integer; -- fails
1304 ERROR: column "f1" cannot be cast to type integer
1305 alter table foo alter f1 TYPE varchar(10);
1306 create table anothertab (atcol1 serial8, atcol2 boolean,
1307 constraint anothertab_chk check (atcol1 <= 3));
1308 NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1"
1309 insert into anothertab (atcol1, atcol2) values (default, true);
1310 insert into anothertab (atcol1, atcol2) values (default, false);
1311 select * from anothertab;
1312 atcol1 | atcol2
1313 --------+--------
1314 1 | t
1315 2 | f
1316 (2 rows)
1318 alter table anothertab alter column atcol1 type boolean; -- fails
1319 ERROR: column "atcol1" cannot be cast to type boolean
1320 alter table anothertab alter column atcol1 type integer;
1321 select * from anothertab;
1322 atcol1 | atcol2
1323 --------+--------
1324 1 | t
1325 2 | f
1326 (2 rows)
1328 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1329 ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1330 insert into anothertab (atcol1, atcol2) values (default, null);
1331 select * from anothertab;
1332 atcol1 | atcol2
1333 --------+--------
1334 1 | t
1335 2 | f
1336 3 |
1337 (3 rows)
1339 alter table anothertab alter column atcol2 type text
1340 using case when atcol2 is true then 'IT WAS TRUE'
1341 when atcol2 is false then 'IT WAS FALSE'
1342 else 'IT WAS NULL!' end;
1343 select * from anothertab;
1344 atcol1 | atcol2
1345 --------+--------------
1346 1 | IT WAS TRUE
1347 2 | IT WAS FALSE
1348 3 | IT WAS NULL!
1349 (3 rows)
1351 alter table anothertab alter column atcol1 type boolean
1352 using case when atcol1 % 2 = 0 then true else false end; -- fails
1353 ERROR: default for column "atcol1" cannot be cast to type boolean
1354 alter table anothertab alter column atcol1 drop default;
1355 alter table anothertab alter column atcol1 type boolean
1356 using case when atcol1 % 2 = 0 then true else false end; -- fails
1357 ERROR: operator does not exist: boolean <= integer
1358 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
1359 alter table anothertab drop constraint anothertab_chk;
1360 alter table anothertab alter column atcol1 type boolean
1361 using case when atcol1 % 2 = 0 then true else false end;
1362 select * from anothertab;
1363 atcol1 | atcol2
1364 --------+--------------
1365 f | IT WAS TRUE
1366 t | IT WAS FALSE
1367 f | IT WAS NULL!
1368 (3 rows)
1370 drop table anothertab;
1371 create table another (f1 int, f2 text);
1372 insert into another values(1, 'one');
1373 insert into another values(2, 'two');
1374 insert into another values(3, 'three');
1375 select * from another;
1376 f1 | f2
1377 ----+-------
1378 1 | one
1379 2 | two
1380 3 | three
1381 (3 rows)
1383 alter table another
1384 alter f1 type text using f2 || ' more',
1385 alter f2 type bigint using f1 * 10;
1386 select * from another;
1387 f1 | f2
1388 ------------+----
1389 one more | 10
1390 two more | 20
1391 three more | 30
1392 (3 rows)
1394 drop table another;
1395 --
1396 -- alter function
1397 --
1398 create function test_strict(text) returns text as
1399 'select coalesce($1, ''got passed a null'');'
1400 language sql returns null on null input;
1401 select test_strict(NULL);
1402 test_strict
1403 -------------
1405 (1 row)
1407 alter function test_strict(text) called on null input;
1408 select test_strict(NULL);
1409 test_strict
1410 -------------------
1411 got passed a null
1412 (1 row)
1414 create function non_strict(text) returns text as
1415 'select coalesce($1, ''got passed a null'');'
1416 language sql called on null input;
1417 select non_strict(NULL);
1418 non_strict
1419 -------------------
1420 got passed a null
1421 (1 row)
1423 alter function non_strict(text) returns null on null input;
1424 select non_strict(NULL);
1425 non_strict
1426 ------------
1428 (1 row)
1430 --
1431 -- alter object set schema
1432 --
1433 create schema alter1;
1434 create schema alter2;
1435 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1436 NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
1437 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
1438 create view alter1.v1 as select * from alter1.t1;
1439 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1440 create domain alter1.posint integer check (value > 0);
1441 create type alter1.ctype as (f1 int, f2 text);
1442 insert into alter1.t1(f2) values(11);
1443 insert into alter1.t1(f2) values(12);
1444 alter table alter1.t1 set schema alter2;
1445 alter table alter1.v1 set schema alter2;
1446 alter function alter1.plus1(int) set schema alter2;
1447 alter domain alter1.posint set schema alter2;
1448 alter type alter1.ctype set schema alter2;
1449 -- this should succeed because nothing is left in alter1
1450 drop schema alter1;
1451 insert into alter2.t1(f2) values(13);
1452 insert into alter2.t1(f2) values(14);
1453 select * from alter2.t1;
1454 f1 | f2
1455 ----+----
1456 1 | 11
1457 2 | 12
1458 3 | 13
1459 4 | 14
1460 (4 rows)
1462 select * from alter2.v1;
1463 f1 | f2
1464 ----+----
1465 1 | 11
1466 2 | 12
1467 3 | 13
1468 4 | 14
1469 (4 rows)
1471 select alter2.plus1(41);
1472 plus1
1473 -------
1474 42
1475 (1 row)
1477 -- clean up
1478 drop schema alter2 cascade;
1479 NOTICE: drop cascades to 5 other objects
1480 DETAIL: drop cascades to table alter2.t1
1481 drop cascades to view alter2.v1
1482 drop cascades to function alter2.plus1(integer)
1483 drop cascades to type alter2.posint
1484 drop cascades to type alter2.ctype