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 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
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"
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.
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".
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.
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".
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.
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.
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.
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);
278 insert into atacc1 (test) values (2);
279 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
281 insert into atacc1 (test) values (4);
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);
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
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);
303 insert into atacc1 (test,test2,test3) values (4,4,2);
304 ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
306 insert into atacc1 (test,test2,test3) values (4,4,5);
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"
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);
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;
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;
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;
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;
380 drop table atacc2 cascade;
381 NOTICE: drop cascades to table atacc3
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);
387 alter table only atacc1 add constraint foo check (test>0);
388 ERROR: constraint must be added to child tables too
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);
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);
408 insert into atacc1 (test) values (2);
409 ERROR: duplicate key value violates unique constraint "atacc_test1"
410 DETAIL: Key (test)=(2) already exists.
412 insert into atacc1 (test) values (4);
413 -- try adding a unique oid constraint
414 alter table atacc1 add constraint atacc_oid1 unique(oid);
415 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"
417 -- let's do one where the unique constraint fails when added
418 create table atacc1 ( test int );
419 -- insert soon to be failing rows
420 insert into atacc1 (test) values (2);
421 insert into atacc1 (test) values (2);
422 -- add a unique constraint (fails)
423 alter table atacc1 add constraint atacc_test1 unique (test);
424 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
425 ERROR: could not create unique index "atacc_test1"
426 DETAIL: Key (test)=(2) is duplicated.
427 insert into atacc1 (test) values (3);
429 -- let's do one where the unique constraint fails
430 -- because the column doesn't exist
431 create table atacc1 ( test int );
432 -- add a unique constraint (fails)
433 alter table atacc1 add constraint atacc_test1 unique (test1);
434 ERROR: column "test1" named in key does not exist
436 -- something a little more complicated
437 create table atacc1 ( test int, test2 int);
438 -- add a unique constraint
439 alter table atacc1 add constraint atacc_test1 unique (test, test2);
440 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
441 -- insert initial value
442 insert into atacc1 (test,test2) values (4,4);
444 insert into atacc1 (test,test2) values (4,4);
445 ERROR: duplicate key value violates unique constraint "atacc_test1"
446 DETAIL: Key (test, test2)=(4, 4) already exists.
447 -- should all succeed
448 insert into atacc1 (test,test2) values (4,5);
449 insert into atacc1 (test,test2) values (5,4);
450 insert into atacc1 (test,test2) values (5,5);
452 -- lets do some naming tests
453 create table atacc1 (test int, test2 int, unique(test));
454 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
455 alter table atacc1 add unique (test2);
456 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
457 -- should fail for @@ second one @@
458 insert into atacc1 (test2, test) values (3, 3);
459 insert into atacc1 (test2, test) values (2, 3);
460 ERROR: duplicate key value violates unique constraint "atacc1_test_key"
461 DETAIL: Key (test)=(3) already exists.
463 -- test primary key constraint adding
464 create table atacc1 ( test int ) with oids;
465 -- add a primary key constraint
466 alter table atacc1 add constraint atacc_test1 primary key (test);
467 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
468 -- insert first value
469 insert into atacc1 (test) values (2);
471 insert into atacc1 (test) values (2);
472 ERROR: duplicate key value violates unique constraint "atacc_test1"
473 DETAIL: Key (test)=(2) already exists.
475 insert into atacc1 (test) values (4);
476 -- inserting NULL should fail
477 insert into atacc1 (test) values(NULL);
478 ERROR: null value in column "test" violates not-null constraint
479 -- try adding a second primary key (should fail)
480 alter table atacc1 add constraint atacc_oid1 primary key(oid);
481 ERROR: multiple primary keys for table "atacc1" are not allowed
482 -- drop first primary key constraint
483 alter table atacc1 drop constraint atacc_test1 restrict;
484 -- try adding a primary key on oid (should succeed)
485 alter table atacc1 add constraint atacc_oid1 primary key(oid);
486 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
488 -- let's do one where the primary key constraint fails when added
489 create table atacc1 ( test int );
490 -- insert soon to be failing rows
491 insert into atacc1 (test) values (2);
492 insert into atacc1 (test) values (2);
493 -- add a primary key (fails)
494 alter table atacc1 add constraint atacc_test1 primary key (test);
495 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
496 ERROR: could not create unique index "atacc_test1"
497 DETAIL: Key (test)=(2) is duplicated.
498 insert into atacc1 (test) values (3);
500 -- let's do another one where the primary key constraint fails when added
501 create table atacc1 ( test int );
502 -- insert soon to be failing row
503 insert into atacc1 (test) values (NULL);
504 -- add a primary key (fails)
505 alter table atacc1 add constraint atacc_test1 primary key (test);
506 ERROR: column "test" contains null values
507 insert into atacc1 (test) values (3);
509 -- let's do one where the primary key constraint fails
510 -- because the column doesn't exist
511 create table atacc1 ( test int );
512 -- add a primary key constraint (fails)
513 alter table atacc1 add constraint atacc_test1 primary key (test1);
514 ERROR: column "test1" named in key does not exist
516 -- adding a new column as primary key to a non-empty table.
517 -- should fail unless the column has a non-null default value.
518 create table atacc1 ( test int );
519 insert into atacc1 (test) values (0);
520 -- add a primary key column without a default (fails).
521 alter table atacc1 add column test2 int primary key;
522 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
523 ERROR: column "test2" contains null values
524 -- now add a primary key column with a default (succeeds).
525 alter table atacc1 add column test2 int default 0 primary key;
526 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
528 -- something a little more complicated
529 create table atacc1 ( test int, test2 int);
530 -- add a primary key constraint
531 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
532 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
533 -- try adding a second primary key - should fail
534 alter table atacc1 add constraint atacc_test2 primary key (test);
535 ERROR: multiple primary keys for table "atacc1" are not allowed
536 -- insert initial value
537 insert into atacc1 (test,test2) values (4,4);
539 insert into atacc1 (test,test2) values (4,4);
540 ERROR: duplicate key value violates unique constraint "atacc_test1"
541 DETAIL: Key (test, test2)=(4, 4) already exists.
542 insert into atacc1 (test,test2) values (NULL,3);
543 ERROR: null value in column "test" violates not-null constraint
544 insert into atacc1 (test,test2) values (3, NULL);
545 ERROR: null value in column "test2" violates not-null constraint
546 insert into atacc1 (test,test2) values (NULL,NULL);
547 ERROR: null value in column "test" violates not-null constraint
548 -- should all succeed
549 insert into atacc1 (test,test2) values (4,5);
550 insert into atacc1 (test,test2) values (5,4);
551 insert into atacc1 (test,test2) values (5,5);
553 -- lets do some naming tests
554 create table atacc1 (test int, test2 int, primary key(test));
555 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
556 -- only first should succeed
557 insert into atacc1 (test2, test) values (3, 3);
558 insert into atacc1 (test2, test) values (2, 3);
559 ERROR: duplicate key value violates unique constraint "atacc1_pkey"
560 DETAIL: Key (test)=(3) already exists.
561 insert into atacc1 (test2, test) values (1, NULL);
562 ERROR: null value in column "test" violates not-null constraint
564 -- alter table / alter column [set/drop] not null tests
565 -- try altering system catalogs, should fail
566 alter table pg_class alter column relname drop not null;
567 ERROR: permission denied: "pg_class" is a system catalog
568 alter table pg_class alter relname set not null;
569 ERROR: permission denied: "pg_class" is a system catalog
570 -- try altering non-existent table, should fail
571 alter table non_existent alter column bar set not null;
572 ERROR: relation "non_existent" does not exist
573 alter table non_existent alter column bar drop not null;
574 ERROR: relation "non_existent" does not exist
575 -- test setting columns to null and not null and vice versa
576 -- test checking for null values and primary key
577 create table atacc1 (test int not null) with oids;
578 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
579 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
580 alter table atacc1 alter column test drop not null;
581 ERROR: column "test" is in a primary key
582 alter table atacc1 drop constraint "atacc1_pkey";
583 alter table atacc1 alter column test drop not null;
584 insert into atacc1 values (null);
585 alter table atacc1 alter test set not null;
586 ERROR: column "test" contains null values
588 alter table atacc1 alter test set not null;
589 -- try altering a non-existent column, should fail
590 alter table atacc1 alter bar set not null;
591 ERROR: column "bar" of relation "atacc1" does not exist
592 alter table atacc1 alter bar drop not null;
593 ERROR: column "bar" of relation "atacc1" does not exist
594 -- try altering the oid column, should fail
595 alter table atacc1 alter oid set not null;
596 ERROR: cannot alter system column "oid"
597 alter table atacc1 alter oid drop not null;
598 ERROR: cannot alter system column "oid"
599 -- try creating a view and altering that, should fail
600 create view myview as select * from atacc1;
601 alter table myview alter column test drop not null;
602 ERROR: "myview" is not a table
603 alter table myview alter column test set not null;
604 ERROR: "myview" is not a table
608 create table parent (a int);
609 create table child (b varchar(255)) inherits (parent);
610 alter table parent alter a set not null;
611 insert into parent values (NULL);
612 ERROR: null value in column "a" violates not-null constraint
613 insert into child (a, b) values (NULL, 'foo');
614 ERROR: null value in column "a" violates not-null constraint
615 alter table parent alter a drop not null;
616 insert into parent values (NULL);
617 insert into child (a, b) values (NULL, 'foo');
618 alter table only parent alter a set not null;
619 ERROR: column "a" contains null values
620 alter table child alter a set not null;
621 ERROR: column "a" contains null values
623 alter table only parent alter a set not null;
624 insert into parent values (NULL);
625 ERROR: null value in column "a" violates not-null constraint
626 alter table child alter a set not null;
627 insert into child (a, b) values (NULL, 'foo');
628 ERROR: null value in column "a" violates not-null constraint
630 alter table child alter a set not null;
631 insert into child (a, b) values (NULL, 'foo');
632 ERROR: null value in column "a" violates not-null constraint
635 -- test setting and removing default values
636 create table def_test (
638 c2 text default 'initial_default'
640 insert into def_test default values;
641 alter table def_test alter column c1 drop default;
642 insert into def_test default values;
643 alter table def_test alter column c2 drop default;
644 insert into def_test default values;
645 alter table def_test alter column c1 set default 10;
646 alter table def_test alter column c2 set default 'new_default';
647 insert into def_test default values;
648 select * from def_test;
650 ----+-----------------
657 -- set defaults to an incorrect type: this should fail
658 alter table def_test alter column c1 set default 'wrong_datatype';
659 ERROR: invalid input syntax for integer: "wrong_datatype"
660 alter table def_test alter column c2 set default 20;
661 -- set defaults on a non-existent column: this should fail
662 alter table def_test alter column c3 set default 30;
663 ERROR: column "c3" of relation "def_test" does not exist
664 -- set defaults on views: we need to create a view, add a rule
665 -- to allow insertions into it, and then alter the view to add
667 create view def_view_test as select * from def_test;
668 create rule def_view_test_ins as
669 on insert to def_view_test
670 do instead insert into def_test select new.*;
671 insert into def_view_test default values;
672 alter table def_view_test alter column c1 set default 45;
673 insert into def_view_test default values;
674 alter table def_view_test alter column c2 set default 'view_default';
675 insert into def_view_test default values;
676 select * from def_view_test;
678 ----+-----------------
688 drop rule def_view_test_ins on def_view_test;
689 drop view def_view_test;
691 -- alter table / drop column tests
692 -- try altering system catalogs, should fail
693 alter table pg_class drop column relname;
694 ERROR: permission denied: "pg_class" is a system catalog
695 -- try altering non-existent table, should fail
696 alter table nosuchtable drop column bar;
697 ERROR: relation "nosuchtable" does not exist
698 -- test dropping columns
699 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
700 insert into atacc1 values (1, 2, 3, 4);
701 alter table atacc1 drop a;
702 alter table atacc1 drop a;
703 ERROR: column "a" of relation "atacc1" does not exist
705 select * from atacc1;
711 select * from atacc1 order by a;
712 ERROR: column "a" does not exist
713 LINE 1: select * from atacc1 order by a;
715 select * from atacc1 order by "........pg.dropped.1........";
716 ERROR: column "........pg.dropped.1........" does not exist
717 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
719 select * from atacc1 group by a;
720 ERROR: column "a" does not exist
721 LINE 1: select * from atacc1 group by a;
723 select * from atacc1 group by "........pg.dropped.1........";
724 ERROR: column "........pg.dropped.1........" does not exist
725 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
727 select atacc1.* from atacc1;
733 select a from atacc1;
734 ERROR: column "a" does not exist
735 LINE 1: select a from atacc1;
737 select atacc1.a from atacc1;
738 ERROR: column atacc1.a does not exist
739 LINE 1: select atacc1.a from atacc1;
741 select b,c,d from atacc1;
747 select a,b,c,d from atacc1;
748 ERROR: column "a" does not exist
749 LINE 1: select a,b,c,d from atacc1;
751 select * from atacc1 where a = 1;
752 ERROR: column "a" does not exist
753 LINE 1: select * from atacc1 where a = 1;
755 select "........pg.dropped.1........" from atacc1;
756 ERROR: column "........pg.dropped.1........" does not exist
757 LINE 1: select "........pg.dropped.1........" from atacc1;
759 select atacc1."........pg.dropped.1........" from atacc1;
760 ERROR: column atacc1.........pg.dropped.1........ does not exist
761 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
763 select "........pg.dropped.1........",b,c,d from atacc1;
764 ERROR: column "........pg.dropped.1........" does not exist
765 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
767 select * from atacc1 where "........pg.dropped.1........" = 1;
768 ERROR: column "........pg.dropped.1........" does not exist
769 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
772 update atacc1 set a = 3;
773 ERROR: column "a" of relation "atacc1" does not exist
774 LINE 1: update atacc1 set a = 3;
776 update atacc1 set b = 2 where a = 3;
777 ERROR: column "a" does not exist
778 LINE 1: update atacc1 set b = 2 where a = 3;
780 update atacc1 set "........pg.dropped.1........" = 3;
781 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
782 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
784 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
785 ERROR: column "........pg.dropped.1........" does not exist
786 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
789 insert into atacc1 values (10, 11, 12, 13);
790 ERROR: INSERT has more expressions than target columns
791 LINE 1: insert into atacc1 values (10, 11, 12, 13);
793 insert into atacc1 values (default, 11, 12, 13);
794 ERROR: INSERT has more expressions than target columns
795 LINE 1: insert into atacc1 values (default, 11, 12, 13);
797 insert into atacc1 values (11, 12, 13);
798 insert into atacc1 (a) values (10);
799 ERROR: column "a" of relation "atacc1" does not exist
800 LINE 1: insert into atacc1 (a) values (10);
802 insert into atacc1 (a) values (default);
803 ERROR: column "a" of relation "atacc1" does not exist
804 LINE 1: insert into atacc1 (a) values (default);
806 insert into atacc1 (a,b,c,d) values (10,11,12,13);
807 ERROR: column "a" of relation "atacc1" does not exist
808 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
810 insert into atacc1 (a,b,c,d) values (default,11,12,13);
811 ERROR: column "a" of relation "atacc1" does not exist
812 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
814 insert into atacc1 (b,c,d) values (11,12,13);
815 insert into atacc1 ("........pg.dropped.1........") values (10);
816 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
817 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
819 insert into atacc1 ("........pg.dropped.1........") values (default);
820 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
821 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
823 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
824 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
825 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
827 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
828 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
829 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
832 delete from atacc1 where a = 3;
833 ERROR: column "a" does not exist
834 LINE 1: delete from atacc1 where a = 3;
836 delete from atacc1 where "........pg.dropped.1........" = 3;
837 ERROR: column "........pg.dropped.1........" does not exist
838 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
841 -- try dropping a non-existent column, should fail
842 alter table atacc1 drop bar;
843 ERROR: column "bar" of relation "atacc1" does not exist
844 -- try dropping the oid column, should succeed
845 alter table atacc1 drop oid;
846 -- try dropping the xmin column, should fail
847 alter table atacc1 drop xmin;
848 ERROR: cannot drop system column "xmin"
849 -- try creating a view and altering that, should fail
850 create view myview as select * from atacc1;
851 select * from myview;
856 alter table myview drop d;
857 ERROR: "myview" is not a table or composite type
859 -- test some commands to make sure they fail on the dropped column
861 ERROR: column "a" of relation "atacc1" does not exist
862 analyze atacc1("........pg.dropped.1........");
863 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
864 vacuum analyze atacc1(a);
865 ERROR: column "a" of relation "atacc1" does not exist
866 vacuum analyze atacc1("........pg.dropped.1........");
867 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
868 comment on column atacc1.a is 'testing';
869 ERROR: column "a" of relation "atacc1" does not exist
870 comment on column atacc1."........pg.dropped.1........" is 'testing';
871 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
872 alter table atacc1 alter a set storage plain;
873 ERROR: column "a" of relation "atacc1" does not exist
874 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
875 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
876 alter table atacc1 alter a set statistics 0;
877 ERROR: column "a" of relation "atacc1" does not exist
878 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
879 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
880 alter table atacc1 alter a set default 3;
881 ERROR: column "a" of relation "atacc1" does not exist
882 alter table atacc1 alter "........pg.dropped.1........" set default 3;
883 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
884 alter table atacc1 alter a drop default;
885 ERROR: column "a" of relation "atacc1" does not exist
886 alter table atacc1 alter "........pg.dropped.1........" drop default;
887 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
888 alter table atacc1 alter a set not null;
889 ERROR: column "a" of relation "atacc1" does not exist
890 alter table atacc1 alter "........pg.dropped.1........" set not null;
891 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
892 alter table atacc1 alter a drop not null;
893 ERROR: column "a" of relation "atacc1" does not exist
894 alter table atacc1 alter "........pg.dropped.1........" drop not null;
895 ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist
896 alter table atacc1 rename a to x;
897 ERROR: column "a" does not exist
898 alter table atacc1 rename "........pg.dropped.1........" to x;
899 ERROR: column "........pg.dropped.1........" does not exist
900 alter table atacc1 add primary key(a);
901 ERROR: column "a" named in key does not exist
902 alter table atacc1 add primary key("........pg.dropped.1........");
903 ERROR: column "........pg.dropped.1........" named in key does not exist
904 alter table atacc1 add unique(a);
905 ERROR: column "a" named in key does not exist
906 alter table atacc1 add unique("........pg.dropped.1........");
907 ERROR: column "........pg.dropped.1........" named in key does not exist
908 alter table atacc1 add check (a > 3);
909 ERROR: column "a" does not exist
910 alter table atacc1 add check ("........pg.dropped.1........" > 3);
911 ERROR: column "........pg.dropped.1........" does not exist
912 create table atacc2 (id int4 unique);
913 NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
914 alter table atacc1 add foreign key (a) references atacc2(id);
915 ERROR: column "a" referenced in foreign key constraint does not exist
916 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
917 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
918 alter table atacc2 add foreign key (id) references atacc1(a);
919 ERROR: column "a" referenced in foreign key constraint does not exist
920 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
921 ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
923 create index "testing_idx" on atacc1(a);
924 ERROR: column "a" does not exist
925 create index "testing_idx" on atacc1("........pg.dropped.1........");
926 ERROR: column "........pg.dropped.1........" does not exist
927 -- test create as and select into
928 insert into atacc1 values (21, 22, 23);
929 create table test1 as select * from atacc1;
937 select * into test2 from atacc1;
945 -- try dropping all columns
946 alter table atacc1 drop c;
947 alter table atacc1 drop d;
948 alter table atacc1 drop b;
949 select * from atacc1;
955 create table parent (a int, b int, c int);
956 insert into parent values (1, 2, 3);
957 alter table parent drop a;
958 create table child (d varchar(255)) inherits (parent);
959 insert into child values (12, 13, 'testing');
960 select * from parent;
973 alter table parent drop c;
974 select * from parent;
990 create table test (a int4, b int4, c int4);
991 insert into test values (1,2,3);
992 alter table test drop a;
995 copy test(a) to stdout;
996 ERROR: column "a" of relation "test" does not exist
997 copy test("........pg.dropped.1........") to stdout;
998 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
999 copy test from stdin;
1000 ERROR: extra data after last expected column
1001 CONTEXT: COPY test, line 1: "10 11 12"
1008 copy test from stdin;
1016 copy test(a) from stdin;
1017 ERROR: column "a" of relation "test" does not exist
1018 copy test("........pg.dropped.1........") from stdin;
1019 ERROR: column "........pg.dropped.1........" of relation "test" does not exist
1020 copy test(b,c) from stdin;
1031 create table dropColumn (a int, b int, e int);
1032 create table dropColumnChild (c int) inherits (dropColumn);
1033 create table dropColumnAnother (d int) inherits (dropColumnChild);
1034 -- these two should fail
1035 alter table dropColumnchild drop column a;
1036 ERROR: cannot drop inherited column "a"
1037 alter table only dropColumnChild drop column b;
1038 ERROR: cannot drop inherited column "b"
1039 -- these three should work
1040 alter table only dropColumn drop column e;
1041 alter table dropColumnChild drop column c;
1042 alter table dropColumn drop column a;
1043 create table renameColumn (a int);
1044 create table renameColumnChild (b int) inherits (renameColumn);
1045 create table renameColumnAnother (c int) inherits (renameColumnChild);
1046 -- these three should fail
1047 alter table renameColumnChild rename column a to d;
1048 ERROR: cannot rename inherited column "a"
1049 alter table only renameColumnChild rename column a to d;
1050 ERROR: inherited column "a" must be renamed in child tables too
1051 alter table only renameColumn rename column a to d;
1052 ERROR: inherited column "a" must be renamed in child tables too
1053 -- these should work
1054 alter table renameColumn rename column a to d;
1055 alter table renameColumnChild rename column b to a;
1057 alter table renameColumn add column w int;
1059 alter table only renameColumn add column x int;
1060 ERROR: column must be added to child tables too
1061 -- Test corner cases in dropping of inherited columns
1062 create table p1 (f1 int, f2 int);
1063 create table c1 (f1 int not null) inherits(p1);
1064 NOTICE: merging column "f1" with inherited definition
1065 -- should be rejected since c1.f1 is inherited
1066 alter table c1 drop column f1;
1067 ERROR: cannot drop inherited column "f1"
1069 alter table p1 drop column f1;
1070 -- c1.f1 is still there, but no longer inherited
1076 alter table c1 drop column f1;
1078 ERROR: column "f1" does not exist
1079 LINE 1: select f1 from c1;
1081 drop table p1 cascade;
1082 NOTICE: drop cascades to table c1
1083 create table p1 (f1 int, f2 int);
1084 create table c1 () inherits(p1);
1085 -- should be rejected since c1.f1 is inherited
1086 alter table c1 drop column f1;
1087 ERROR: cannot drop inherited column "f1"
1088 alter table p1 drop column f1;
1089 -- c1.f1 is dropped now, since there is no local definition for it
1091 ERROR: column "f1" does not exist
1092 LINE 1: select f1 from c1;
1094 drop table p1 cascade;
1095 NOTICE: drop cascades to table c1
1096 create table p1 (f1 int, f2 int);
1097 create table c1 () inherits(p1);
1098 -- should be rejected since c1.f1 is inherited
1099 alter table c1 drop column f1;
1100 ERROR: cannot drop inherited column "f1"
1101 alter table only p1 drop column f1;
1102 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1103 alter table c1 drop column f1;
1104 drop table p1 cascade;
1105 NOTICE: drop cascades to table c1
1106 create table p1 (f1 int, f2 int);
1107 create table c1 (f1 int not null) inherits(p1);
1108 NOTICE: merging column "f1" with inherited definition
1109 -- should be rejected since c1.f1 is inherited
1110 alter table c1 drop column f1;
1111 ERROR: cannot drop inherited column "f1"
1112 alter table only p1 drop column f1;
1113 -- c1.f1 is still there, but no longer inherited
1114 alter table c1 drop column f1;
1115 drop table p1 cascade;
1116 NOTICE: drop cascades to table c1
1117 create table p1(id int, name text);
1118 create table p2(id2 int, name text, height int);
1119 create table c1(age int) inherits(p1,p2);
1120 NOTICE: merging multiple inherited definitions of column "name"
1121 create table gc1() inherits (c1);
1122 select relname, attname, attinhcount, attislocal
1123 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1124 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1125 order by relname, attnum;
1126 relname | attname | attinhcount | attislocal
1127 ---------+---------+-------------+------------
1136 gc1 | height | 1 | f
1146 alter table only p1 drop column name;
1147 -- should work. Now c1.name is local and inhcount is 0.
1148 alter table p2 drop column name;
1149 -- should be rejected since its inherited
1150 alter table gc1 drop column name;
1151 ERROR: cannot drop inherited column "name"
1152 -- should work, and drop gc1.name along
1153 alter table c1 drop column name;
1154 -- should fail: column does not exist
1155 alter table gc1 drop column name;
1156 ERROR: column "name" of relation "gc1" does not exist
1157 -- should work and drop the attribute in all tables
1158 alter table p2 drop column height;
1160 create table dropColumnExists ();
1161 alter table dropColumnExists drop column non_existing; --fail
1162 ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
1163 alter table dropColumnExists drop column if exists non_existing; --succeed
1164 NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
1165 select relname, attname, attinhcount, attislocal
1166 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1167 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1168 order by relname, attnum;
1169 relname | attname | attinhcount | attislocal
1170 ---------+---------+-------------+------------
1181 drop table p1, p2 cascade;
1182 NOTICE: drop cascades to 2 other objects
1183 DETAIL: drop cascades to table c1
1184 drop cascades to table gc1
1186 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
1188 create table altstartwith (col integer) with oids;
1189 insert into altstartwith values (1);
1190 select oid > 0, * from altstartwith;
1196 alter table altstartwith set without oids;
1197 select oid > 0, * from altstartwith; -- fails
1198 ERROR: column "oid" does not exist
1199 LINE 1: select oid > 0, * from altstartwith;
1201 select * from altstartwith;
1207 alter table altstartwith set with oids;
1208 select oid > 0, * from altstartwith;
1214 drop table altstartwith;
1215 -- Check inheritance cases
1216 create table altwithoid (col integer) with oids;
1217 -- Inherits parents oid column anyway
1218 create table altinhoid () inherits (altwithoid) without oids;
1219 insert into altinhoid values (1);
1220 select oid > 0, * from altwithoid;
1226 select oid > 0, * from altinhoid;
1232 alter table altwithoid set without oids;
1233 select oid > 0, * from altwithoid; -- fails
1234 ERROR: column "oid" does not exist
1235 LINE 1: select oid > 0, * from altwithoid;
1237 select oid > 0, * from altinhoid; -- fails
1238 ERROR: column "oid" does not exist
1239 LINE 1: select oid > 0, * from altinhoid;
1241 select * from altwithoid;
1247 select * from altinhoid;
1253 alter table altwithoid set with oids;
1254 select oid > 0, * from altwithoid;
1260 select oid > 0, * from altinhoid;
1266 drop table altwithoid cascade;
1267 NOTICE: drop cascades to table altinhoid
1268 create table altwithoid (col integer) without oids;
1269 -- child can have local oid column
1270 create table altinhoid () inherits (altwithoid) with oids;
1271 insert into altinhoid values (1);
1272 select oid > 0, * from altwithoid; -- fails
1273 ERROR: column "oid" does not exist
1274 LINE 1: select oid > 0, * from altwithoid;
1276 select oid > 0, * from altinhoid;
1282 alter table altwithoid set with oids;
1283 NOTICE: merging definition of column "oid" for child "altinhoid"
1284 select oid > 0, * from altwithoid;
1290 select oid > 0, * from altinhoid;
1296 -- the child's local definition should remain
1297 alter table altwithoid set without oids;
1298 select oid > 0, * from altwithoid; -- fails
1299 ERROR: column "oid" does not exist
1300 LINE 1: select oid > 0, * from altwithoid;
1302 select oid > 0, * from altinhoid;
1308 drop table altwithoid cascade;
1309 NOTICE: drop cascades to table altinhoid
1310 -- test renumbering of child-table columns in inherited operations
1311 create table p1 (f1 int);
1312 create table c1 (f2 text, f3 int) inherits (p1);
1313 alter table p1 add column a1 int check (a1 > 0);
1314 alter table p1 add column f2 text;
1315 NOTICE: merging definition of column "f2" for child "c1"
1316 insert into p1 values (1,2,'abc');
1317 insert into c1 values(11,'xyz',33,0); -- should fail
1318 ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1319 insert into c1 values(11,'xyz',33,22);
1327 update p1 set a1 = a1 + 1, f2 = upper(f2);
1335 drop table p1 cascade;
1336 NOTICE: drop cascades to table c1
1337 -- test that operations with a dropped column do not try to reference
1339 create domain mytype as text;
1340 create temp table foo (f1 text, f2 mytype, f3 text);
1341 insert into foo values('bb','cc','dd');
1348 drop domain mytype cascade;
1349 NOTICE: drop cascades to table foo column f2
1356 insert into foo values('qq','rr');
1364 update foo set f3 = 'zz';
1372 select f3,max(f1) from foo group by f3;
1378 -- Simple tests for alter table column type
1379 alter table foo alter f1 TYPE integer; -- fails
1380 ERROR: column "f1" cannot be cast to type integer
1381 alter table foo alter f1 TYPE varchar(10);
1382 create table anothertab (atcol1 serial8, atcol2 boolean,
1383 constraint anothertab_chk check (atcol1 <= 3));
1384 NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1"
1385 insert into anothertab (atcol1, atcol2) values (default, true);
1386 insert into anothertab (atcol1, atcol2) values (default, false);
1387 select * from anothertab;
1394 alter table anothertab alter column atcol1 type boolean; -- fails
1395 ERROR: column "atcol1" cannot be cast to type boolean
1396 alter table anothertab alter column atcol1 type integer;
1397 select * from anothertab;
1404 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1405 ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1406 insert into anothertab (atcol1, atcol2) values (default, null);
1407 select * from anothertab;
1415 alter table anothertab alter column atcol2 type text
1416 using case when atcol2 is true then 'IT WAS TRUE'
1417 when atcol2 is false then 'IT WAS FALSE'
1418 else 'IT WAS NULL!' end;
1419 select * from anothertab;
1421 --------+--------------
1427 alter table anothertab alter column atcol1 type boolean
1428 using case when atcol1 % 2 = 0 then true else false end; -- fails
1429 ERROR: default for column "atcol1" cannot be cast to type boolean
1430 alter table anothertab alter column atcol1 drop default;
1431 alter table anothertab alter column atcol1 type boolean
1432 using case when atcol1 % 2 = 0 then true else false end; -- fails
1433 ERROR: operator does not exist: boolean <= integer
1434 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
1435 alter table anothertab drop constraint anothertab_chk;
1436 alter table anothertab drop constraint anothertab_chk; -- fails
1437 ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
1438 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1439 NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
1440 alter table anothertab alter column atcol1 type boolean
1441 using case when atcol1 % 2 = 0 then true else false end;
1442 select * from anothertab;
1444 --------+--------------
1450 drop table anothertab;
1451 create table another (f1 int, f2 text);
1452 insert into another values(1, 'one');
1453 insert into another values(2, 'two');
1454 insert into another values(3, 'three');
1455 select * from another;
1464 alter f1 type text using f2 || ' more',
1465 alter f2 type bigint using f1 * 10;
1466 select * from another;
1476 create table tab1 (a int, b text);
1477 create table tab2 (x int, y tab1);
1478 alter table tab1 alter column b type varchar; -- fails
1479 ERROR: cannot alter table "tab1" because column "tab2"."y" uses its rowtype
1483 drop type lockmodes;
1484 ERROR: type "lockmodes" does not exist
1485 create type lockmodes as enum (
1489 ,'ShareUpdateExclusiveLock'
1491 ,'ShareRowExclusiveLock'
1493 ,'AccessExclusiveLock'
1496 ERROR: view "my_locks" does not exist
1497 create or replace view my_locks as
1498 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1499 from pg_locks l join pg_class c on l.relation = c.oid
1500 where virtualtransaction = (
1501 select virtualtransaction
1503 where transactionid = txid_current()::integer)
1504 and locktype = 'relation'
1505 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1506 and c.relname != 'my_locks'
1508 create table alterlock (f1 int primary key, f2 text);
1509 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "alterlock_pkey" for table "alterlock"
1510 -- share update exclusive
1511 begin; alter table alterlock alter column f2 set statistics 150;
1512 select * from my_locks order by 1;
1513 relname | max_lockmode
1514 -----------+--------------------------
1515 alterlock | ShareUpdateExclusiveLock
1519 begin; alter table alterlock cluster on alterlock_pkey;
1520 select * from my_locks order by 1;
1521 relname | max_lockmode
1522 ----------------+--------------------------
1523 alterlock | ShareUpdateExclusiveLock
1524 alterlock_pkey | ShareUpdateExclusiveLock
1528 begin; alter table alterlock set without cluster;
1529 select * from my_locks order by 1;
1530 relname | max_lockmode
1531 -----------+--------------------------
1532 alterlock | ShareUpdateExclusiveLock
1536 begin; alter table alterlock set (fillfactor = 100);
1537 select * from my_locks order by 1;
1538 relname | max_lockmode
1539 -----------+--------------------------
1540 alterlock | ShareUpdateExclusiveLock
1541 pg_toast | ShareUpdateExclusiveLock
1545 begin; alter table alterlock reset (fillfactor);
1546 select * from my_locks order by 1;
1547 relname | max_lockmode
1548 -----------+--------------------------
1549 alterlock | ShareUpdateExclusiveLock
1550 pg_toast | ShareUpdateExclusiveLock
1554 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1555 select * from my_locks order by 1;
1556 relname | max_lockmode
1557 -----------+--------------------------
1558 alterlock | ShareUpdateExclusiveLock
1559 pg_toast | ShareUpdateExclusiveLock
1563 begin; alter table alterlock set (autovacuum_enabled = off);
1564 select * from my_locks order by 1;
1565 relname | max_lockmode
1566 -----------+--------------------------
1567 alterlock | ShareUpdateExclusiveLock
1568 pg_toast | ShareUpdateExclusiveLock
1572 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1573 select * from my_locks order by 1;
1574 relname | max_lockmode
1575 -----------+--------------------------
1576 alterlock | ShareUpdateExclusiveLock
1580 begin; alter table alterlock alter column f2 set storage extended;
1581 select * from my_locks order by 1;
1582 relname | max_lockmode
1583 -----------+--------------------------
1584 alterlock | ShareUpdateExclusiveLock
1588 -- share row exclusive
1589 begin; alter table alterlock alter column f2 set default 'x';
1590 select * from my_locks order by 1;
1591 relname | max_lockmode
1592 -----------+-----------------------
1593 alterlock | ShareRowExclusiveLock
1598 drop table alterlock;
1600 drop type lockmodes;
1604 create function test_strict(text) returns text as
1605 'select coalesce($1, ''got passed a null'');'
1606 language sql returns null on null input;
1607 select test_strict(NULL);
1613 alter function test_strict(text) called on null input;
1614 select test_strict(NULL);
1620 create function non_strict(text) returns text as
1621 'select coalesce($1, ''got passed a null'');'
1622 language sql called on null input;
1623 select non_strict(NULL);
1629 alter function non_strict(text) returns null on null input;
1630 select non_strict(NULL);
1637 -- alter object set schema
1639 create schema alter1;
1640 create schema alter2;
1641 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1642 NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
1643 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
1644 create view alter1.v1 as select * from alter1.t1;
1645 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1646 create domain alter1.posint integer check (value > 0);
1647 create type alter1.ctype as (f1 int, f2 text);
1648 insert into alter1.t1(f2) values(11);
1649 insert into alter1.t1(f2) values(12);
1650 alter table alter1.t1 set schema alter2;
1651 alter table alter1.v1 set schema alter2;
1652 alter function alter1.plus1(int) set schema alter2;
1653 alter domain alter1.posint set schema alter2;
1654 alter type alter1.ctype set schema alter2;
1655 -- this should succeed because nothing is left in alter1
1657 insert into alter2.t1(f2) values(13);
1658 insert into alter2.t1(f2) values(14);
1659 select * from alter2.t1;
1668 select * from alter2.v1;
1677 select alter2.plus1(41);
1684 drop schema alter2 cascade;
1685 NOTICE: drop cascades to 5 other objects
1686 DETAIL: drop cascades to table alter2.t1
1687 drop cascades to view alter2.v1
1688 drop cascades to function alter2.plus1(integer)
1689 drop cascades to type alter2.posint
1690 drop cascades to type alter2.ctype
1694 CREATE TYPE test_type AS (a int);
1696 Composite type "public.test_type"
1701 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1702 ERROR: relation "nosuchtype" does not exist
1703 ALTER TYPE test_type ADD ATTRIBUTE b text;
1705 Composite type "public.test_type"
1711 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1712 ERROR: column "b" of relation "test_type" already exists
1713 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1715 Composite type "public.test_type"
1717 --------+-------------------
1719 b | character varying
1721 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1723 Composite type "public.test_type"
1729 ALTER TYPE test_type DROP ATTRIBUTE b;
1731 Composite type "public.test_type"
1736 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1737 ERROR: column "c" of relation "test_type" does not exist
1738 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1739 NOTICE: column "c" of relation "test_type" does not exist, skipping
1740 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1742 Composite type "public.test_type"
1747 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1748 ERROR: column "a" does not exist
1749 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1751 Composite type "public.test_type"
1756 DROP TYPE test_type;
1757 CREATE TYPE test_type1 AS (a int, b text);
1758 CREATE TABLE test_tbl1 (x int, y test_type1);
1759 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1760 ERROR: cannot alter type "test_type1" because column "test_tbl1"."y" uses it
1761 CREATE TYPE test_type2 AS (a int, b text);
1762 CREATE TABLE test_tbl2 OF test_type2;
1763 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1764 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1765 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1766 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1767 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1768 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1769 ALTER TYPE test_type2 RENAME ATTRIBUTE b TO bb; -- fails
1770 ERROR: cannot alter type "test_type2" because it is the type of a typed table
1771 CREATE TYPE test_type_empty AS ();
1772 DROP TYPE test_type_empty;