6
CREATE TABLE tmp (initial int4);
8
COMMENT ON TABLE tmp_wrong IS 'table comment';
9
COMMENT ON TABLE tmp IS 'table comment';
10
COMMENT ON TABLE tmp IS NULL;
12
ALTER TABLE tmp ADD COLUMN a int4 default 3;
14
ALTER TABLE tmp ADD COLUMN b name;
16
ALTER TABLE tmp ADD COLUMN c text;
18
ALTER TABLE tmp ADD COLUMN d float8;
20
ALTER TABLE tmp ADD COLUMN e float4;
22
ALTER TABLE tmp ADD COLUMN f int2;
24
ALTER TABLE tmp ADD COLUMN g polygon;
26
ALTER TABLE tmp ADD COLUMN h abstime;
28
ALTER TABLE tmp ADD COLUMN i char;
30
ALTER TABLE tmp ADD COLUMN j abstime[];
32
ALTER TABLE tmp ADD COLUMN k int4;
34
ALTER TABLE tmp ADD COLUMN l tid;
36
ALTER TABLE tmp ADD COLUMN m xid;
38
ALTER TABLE tmp ADD COLUMN n oidvector;
40
--ALTER TABLE tmp ADD COLUMN o lock;
41
ALTER TABLE tmp ADD COLUMN p smgr;
43
ALTER TABLE tmp ADD COLUMN q point;
45
ALTER TABLE tmp ADD COLUMN r lseg;
47
ALTER TABLE tmp ADD COLUMN s path;
49
ALTER TABLE tmp ADD COLUMN t box;
51
ALTER TABLE tmp ADD COLUMN u tinterval;
53
ALTER TABLE tmp ADD COLUMN v timestamp;
55
ALTER TABLE tmp ADD COLUMN w interval;
57
ALTER TABLE tmp ADD COLUMN x float8[];
59
ALTER TABLE tmp ADD COLUMN y float4[];
61
ALTER TABLE tmp ADD COLUMN z int2[];
63
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
65
VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
66
'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
67
314159, '(1,1)', '512',
68
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
69
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
70
'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
76
-- the wolf bug - schema mods caused inconsistent row descriptors
81
ALTER TABLE tmp ADD COLUMN a int4;
83
ALTER TABLE tmp ADD COLUMN b name;
85
ALTER TABLE tmp ADD COLUMN c text;
87
ALTER TABLE tmp ADD COLUMN d float8;
89
ALTER TABLE tmp ADD COLUMN e float4;
91
ALTER TABLE tmp ADD COLUMN f int2;
93
ALTER TABLE tmp ADD COLUMN g polygon;
95
ALTER TABLE tmp ADD COLUMN h abstime;
97
ALTER TABLE tmp ADD COLUMN i char;
99
ALTER TABLE tmp ADD COLUMN j abstime[];
101
ALTER TABLE tmp ADD COLUMN k int4;
103
ALTER TABLE tmp ADD COLUMN l tid;
105
ALTER TABLE tmp ADD COLUMN m xid;
107
ALTER TABLE tmp ADD COLUMN n oidvector;
109
--ALTER TABLE tmp ADD COLUMN o lock;
110
ALTER TABLE tmp ADD COLUMN p smgr;
112
ALTER TABLE tmp ADD COLUMN q point;
114
ALTER TABLE tmp ADD COLUMN r lseg;
116
ALTER TABLE tmp ADD COLUMN s path;
118
ALTER TABLE tmp ADD COLUMN t box;
120
ALTER TABLE tmp ADD COLUMN u tinterval;
122
ALTER TABLE tmp ADD COLUMN v timestamp;
124
ALTER TABLE tmp ADD COLUMN w interval;
126
ALTER TABLE tmp ADD COLUMN x float8[];
128
ALTER TABLE tmp ADD COLUMN y float4[];
130
ALTER TABLE tmp ADD COLUMN z int2[];
132
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
134
VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
135
'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
136
314159, '(1,1)', '512',
137
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
138
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
139
'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
147
-- rename - check on both non-temp and temp tables
149
CREATE TABLE tmp (regtable int);
150
CREATE TEMP TABLE tmp (tmptable int);
152
ALTER TABLE tmp RENAME TO tmp_new;
155
SELECT * FROM tmp_new;
157
ALTER TABLE tmp RENAME TO tmp_new2;
159
SELECT * FROM tmp; -- should fail
160
SELECT * FROM tmp_new;
161
SELECT * FROM tmp_new2;
167
-- ALTER TABLE ... RENAME on non-table relations
168
-- renaming indexes (FIXME: this should probably test the index's functionality)
169
ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
170
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
172
CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
173
ALTER TABLE tmp_view RENAME TO tmp_view_new;
174
-- analyze to ensure we get an indexscan here
177
SELECT unique1 FROM tenk1 WHERE unique1 < 5;
178
DROP VIEW tmp_view_new;
179
-- toast-like relation name
180
alter table stud_emp rename to pg_toast_stud_emp;
181
alter table pg_toast_stud_emp rename to stud_emp;
183
-- FOREIGN KEY CONSTRAINT adding TEST
185
CREATE TABLE tmp2 (a int primary key);
187
CREATE TABLE tmp3 (a int, b int);
189
CREATE TABLE tmp4 (a int, b int, unique(a,b));
191
CREATE TABLE tmp5 (a int, b int);
193
-- Insert rows into tmp2 (pktable)
194
INSERT INTO tmp2 values (1);
195
INSERT INTO tmp2 values (2);
196
INSERT INTO tmp2 values (3);
197
INSERT INTO tmp2 values (4);
199
-- Insert rows into tmp3
200
INSERT INTO tmp3 values (1,10);
201
INSERT INTO tmp3 values (1,20);
202
INSERT INTO tmp3 values (5,50);
204
-- Try (and fail) to add constraint due to invalid source columns
205
ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
207
-- Try (and fail) to add constraint due to invalide destination columns explicitly given
208
ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
210
-- Try (and fail) to add constraint due to invalid data
211
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
213
-- Delete failing row
214
DELETE FROM tmp3 where a=5;
217
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
219
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
222
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
232
-- Foreign key adding test with mixed types
234
-- Note: these tables are TEMP to avoid name conflicts when this test
235
-- is run in parallel with foreign_key.sql.
237
CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
238
CREATE TEMP TABLE FKTABLE (ftest1 inet);
239
-- This next should fail, because inet=int does not exist
240
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
241
-- This should also fail for the same reason, but here we
242
-- give the column name
243
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
244
-- This should succeed, even though they are different types
245
-- because varchar=int does exist
247
CREATE TEMP TABLE FKTABLE (ftest1 varchar);
248
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
250
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
251
DROP TABLE pktable cascade;
254
CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
255
PRIMARY KEY(ptest1, ptest2));
256
-- This should fail, because we just chose really odd types
257
CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
258
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
260
-- Again, so should this...
261
CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
262
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
263
references pktable(ptest1, ptest2);
265
-- This fails because we mixed up the column ordering
266
CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
267
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
268
references pktable(ptest2, ptest1);
270
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
271
references pktable(ptest1, ptest2);
273
-- temp tables should go away by themselves, need not drop them.
275
-- test check constraint adding
277
create table atacc1 ( test int );
278
-- add a check constraint
279
alter table atacc1 add constraint atacc_test1 check (test>3);
281
insert into atacc1 (test) values (2);
283
insert into atacc1 (test) values (4);
286
-- let's do one where the check fails when added
287
create table atacc1 ( test int );
288
-- insert a soon to be failing row
289
insert into atacc1 (test) values (2);
290
-- add a check constraint (fails)
291
alter table atacc1 add constraint atacc_test1 check (test>3);
292
insert into atacc1 (test) values (4);
295
-- let's do one where the check fails because the column doesn't exist
296
create table atacc1 ( test int );
297
-- add a check constraint (fails)
298
alter table atacc1 add constraint atacc_test1 check (test1>3);
301
-- something a little more complicated
302
create table atacc1 ( test int, test2 int, test3 int);
303
-- add a check constraint (fails)
304
alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
306
insert into atacc1 (test,test2,test3) values (4,4,2);
308
insert into atacc1 (test,test2,test3) values (4,4,5);
311
-- lets do some naming tests
312
create table atacc1 (test int check (test>3), test2 int);
313
alter table atacc1 add check (test2>test);
314
-- should fail for $2
315
insert into atacc1 (test2, test) values (3, 4);
318
-- inheritance related tests
319
create table atacc1 (test int);
320
create table atacc2 (test2 int);
321
create table atacc3 (test3 int) inherits (atacc1, atacc2);
322
alter table atacc2 add constraint foo check (test2>0);
323
-- fail and then succeed on atacc2
324
insert into atacc2 (test2) values (-3);
325
insert into atacc2 (test2) values (3);
326
-- fail and then succeed on atacc3
327
insert into atacc3 (test2) values (-3);
328
insert into atacc3 (test2) values (3);
333
-- let's try only to add only to the parent
335
create table atacc1 (test int);
336
create table atacc2 (test2 int);
337
create table atacc3 (test3 int) inherits (atacc1, atacc2);
338
alter table only atacc2 add constraint foo check (test2>0);
339
-- fail and then succeed on atacc2
340
insert into atacc2 (test2) values (-3);
341
insert into atacc2 (test2) values (3);
342
-- both succeed on atacc3
343
insert into atacc3 (test2) values (-3);
344
insert into atacc3 (test2) values (3);
349
-- test unique constraint adding
351
create table atacc1 ( test int ) with oids;
352
-- add a unique constraint
353
alter table atacc1 add constraint atacc_test1 unique (test);
354
-- insert first value
355
insert into atacc1 (test) values (2);
357
insert into atacc1 (test) values (2);
359
insert into atacc1 (test) values (4);
360
-- try adding a unique oid constraint
361
alter table atacc1 add constraint atacc_oid1 unique(oid);
364
-- let's do one where the unique constraint fails when added
365
create table atacc1 ( test int );
366
-- insert soon to be failing rows
367
insert into atacc1 (test) values (2);
368
insert into atacc1 (test) values (2);
369
-- add a unique constraint (fails)
370
alter table atacc1 add constraint atacc_test1 unique (test);
371
insert into atacc1 (test) values (3);
374
-- let's do one where the unique constraint fails
375
-- because the column doesn't exist
376
create table atacc1 ( test int );
377
-- add a unique constraint (fails)
378
alter table atacc1 add constraint atacc_test1 unique (test1);
381
-- something a little more complicated
382
create table atacc1 ( test int, test2 int);
383
-- add a unique constraint
384
alter table atacc1 add constraint atacc_test1 unique (test, test2);
385
-- insert initial value
386
insert into atacc1 (test,test2) values (4,4);
388
insert into atacc1 (test,test2) values (4,4);
389
-- should all succeed
390
insert into atacc1 (test,test2) values (4,5);
391
insert into atacc1 (test,test2) values (5,4);
392
insert into atacc1 (test,test2) values (5,5);
395
-- lets do some naming tests
396
create table atacc1 (test int, test2 int, unique(test));
397
alter table atacc1 add unique (test2);
398
-- should fail for @@ second one @@
399
insert into atacc1 (test2, test) values (3, 3);
400
insert into atacc1 (test2, test) values (2, 3);
403
-- test primary key constraint adding
405
create table atacc1 ( test int ) with oids;
406
-- add a primary key constraint
407
alter table atacc1 add constraint atacc_test1 primary key (test);
408
-- insert first value
409
insert into atacc1 (test) values (2);
411
insert into atacc1 (test) values (2);
413
insert into atacc1 (test) values (4);
414
-- inserting NULL should fail
415
insert into atacc1 (test) values(NULL);
416
-- try adding a second primary key (should fail)
417
alter table atacc1 add constraint atacc_oid1 primary key(oid);
418
-- drop first primary key constraint
419
alter table atacc1 drop constraint atacc_test1 restrict;
420
-- try adding a primary key on oid (should succeed)
421
alter table atacc1 add constraint atacc_oid1 primary key(oid);
424
-- let's do one where the primary key constraint fails when added
425
create table atacc1 ( test int );
426
-- insert soon to be failing rows
427
insert into atacc1 (test) values (2);
428
insert into atacc1 (test) values (2);
429
-- add a primary key (fails)
430
alter table atacc1 add constraint atacc_test1 primary key (test);
431
insert into atacc1 (test) values (3);
434
-- let's do another one where the primary key constraint fails when added
435
create table atacc1 ( test int );
436
-- insert soon to be failing row
437
insert into atacc1 (test) values (NULL);
438
-- add a primary key (fails)
439
alter table atacc1 add constraint atacc_test1 primary key (test);
440
insert into atacc1 (test) values (3);
443
-- let's do one where the primary key constraint fails
444
-- because the column doesn't exist
445
create table atacc1 ( test int );
446
-- add a primary key constraint (fails)
447
alter table atacc1 add constraint atacc_test1 primary key (test1);
450
-- something a little more complicated
451
create table atacc1 ( test int, test2 int);
452
-- add a primary key constraint
453
alter table atacc1 add constraint atacc_test1 primary key (test, test2);
454
-- try adding a second primary key - should fail
455
alter table atacc1 add constraint atacc_test2 primary key (test);
456
-- insert initial value
457
insert into atacc1 (test,test2) values (4,4);
459
insert into atacc1 (test,test2) values (4,4);
460
insert into atacc1 (test,test2) values (NULL,3);
461
insert into atacc1 (test,test2) values (3, NULL);
462
insert into atacc1 (test,test2) values (NULL,NULL);
463
-- should all succeed
464
insert into atacc1 (test,test2) values (4,5);
465
insert into atacc1 (test,test2) values (5,4);
466
insert into atacc1 (test,test2) values (5,5);
469
-- lets do some naming tests
470
create table atacc1 (test int, test2 int, primary key(test));
471
-- only first should succeed
472
insert into atacc1 (test2, test) values (3, 3);
473
insert into atacc1 (test2, test) values (2, 3);
474
insert into atacc1 (test2, test) values (1, NULL);
477
-- alter table / alter column [set/drop] not null tests
478
-- try altering system catalogs, should fail
479
alter table pg_class alter column relname drop not null;
480
alter table pg_class alter relname set not null;
482
-- try altering non-existent table, should fail
483
alter table non_existent alter column bar set not null;
484
alter table non_existent alter column bar drop not null;
486
-- test setting columns to null and not null and vice versa
487
-- test checking for null values and primary key
488
create table atacc1 (test int not null) with oids;
489
alter table atacc1 add constraint "atacc1_pkey" primary key (test);
490
alter table atacc1 alter column test drop not null;
491
alter table atacc1 drop constraint "atacc1_pkey";
492
alter table atacc1 alter column test drop not null;
493
insert into atacc1 values (null);
494
alter table atacc1 alter test set not null;
496
alter table atacc1 alter test set not null;
498
-- try altering a non-existent column, should fail
499
alter table atacc1 alter bar set not null;
500
alter table atacc1 alter bar drop not null;
502
-- try altering the oid column, should fail
503
alter table atacc1 alter oid set not null;
504
alter table atacc1 alter oid drop not null;
506
-- try creating a view and altering that, should fail
507
create view myview as select * from atacc1;
508
alter table myview alter column test drop not null;
509
alter table myview alter column test set not null;
515
create table parent (a int);
516
create table child (b varchar(255)) inherits (parent);
518
alter table parent alter a set not null;
519
insert into parent values (NULL);
520
insert into child (a, b) values (NULL, 'foo');
521
alter table parent alter a drop not null;
522
insert into parent values (NULL);
523
insert into child (a, b) values (NULL, 'foo');
524
alter table only parent alter a set not null;
525
alter table child alter a set not null;
527
alter table only parent alter a set not null;
528
insert into parent values (NULL);
529
alter table child alter a set not null;
530
insert into child (a, b) values (NULL, 'foo');
532
alter table child alter a set not null;
533
insert into child (a, b) values (NULL, 'foo');
537
-- test setting and removing default values
538
create table def_test (
540
c2 text default 'initial_default'
542
insert into def_test default values;
543
alter table def_test alter column c1 drop default;
544
insert into def_test default values;
545
alter table def_test alter column c2 drop default;
546
insert into def_test default values;
547
alter table def_test alter column c1 set default 10;
548
alter table def_test alter column c2 set default 'new_default';
549
insert into def_test default values;
550
select * from def_test;
552
-- set defaults to an incorrect type: this should fail
553
alter table def_test alter column c1 set default 'wrong_datatype';
554
alter table def_test alter column c2 set default 20;
556
-- set defaults on a non-existent column: this should fail
557
alter table def_test alter column c3 set default 30;
559
-- set defaults on views: we need to create a view, add a rule
560
-- to allow insertions into it, and then alter the view to add
562
create view def_view_test as select * from def_test;
563
create rule def_view_test_ins as
564
on insert to def_view_test
565
do instead insert into def_test select new.*;
566
insert into def_view_test default values;
567
alter table def_view_test alter column c1 set default 45;
568
insert into def_view_test default values;
569
alter table def_view_test alter column c2 set default 'view_default';
570
insert into def_view_test default values;
571
select * from def_view_test;
573
drop rule def_view_test_ins on def_view_test;
574
drop view def_view_test;
577
-- alter table / drop column tests
578
-- try altering system catalogs, should fail
579
alter table pg_class drop column relname;
581
-- try altering non-existent table, should fail
582
alter table nosuchtable drop column bar;
584
-- test dropping columns
585
create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
586
insert into atacc1 values (1, 2, 3, 4);
587
alter table atacc1 drop a;
588
alter table atacc1 drop a;
591
select * from atacc1;
592
select * from atacc1 order by a;
593
select * from atacc1 order by "........pg.dropped.1........";
594
select * from atacc1 group by a;
595
select * from atacc1 group by "........pg.dropped.1........";
596
select atacc1.* from atacc1;
597
select a from atacc1;
598
select atacc1.a from atacc1;
599
select b,c,d from atacc1;
600
select a,b,c,d from atacc1;
601
select * from atacc1 where a = 1;
602
select "........pg.dropped.1........" from atacc1;
603
select atacc1."........pg.dropped.1........" from atacc1;
604
select "........pg.dropped.1........",b,c,d from atacc1;
605
select * from atacc1 where "........pg.dropped.1........" = 1;
608
update atacc1 set a = 3;
609
update atacc1 set b = 2 where a = 3;
610
update atacc1 set "........pg.dropped.1........" = 3;
611
update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
614
insert into atacc1 values (10, 11, 12, 13);
615
insert into atacc1 values (default, 11, 12, 13);
616
insert into atacc1 values (11, 12, 13);
617
insert into atacc1 (a) values (10);
618
insert into atacc1 (a) values (default);
619
insert into atacc1 (a,b,c,d) values (10,11,12,13);
620
insert into atacc1 (a,b,c,d) values (default,11,12,13);
621
insert into atacc1 (b,c,d) values (11,12,13);
622
insert into atacc1 ("........pg.dropped.1........") values (10);
623
insert into atacc1 ("........pg.dropped.1........") values (default);
624
insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
625
insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
628
delete from atacc1 where a = 3;
629
delete from atacc1 where "........pg.dropped.1........" = 3;
632
-- try dropping a non-existent column, should fail
633
alter table atacc1 drop bar;
635
-- try dropping the oid column, should succeed
636
alter table atacc1 drop oid;
638
-- try dropping the xmin column, should fail
639
alter table atacc1 drop xmin;
641
-- try creating a view and altering that, should fail
642
create view myview as select * from atacc1;
643
select * from myview;
644
alter table myview drop d;
647
-- test some commands to make sure they fail on the dropped column
649
analyze atacc1("........pg.dropped.1........");
650
vacuum analyze atacc1(a);
651
vacuum analyze atacc1("........pg.dropped.1........");
652
comment on column atacc1.a is 'testing';
653
comment on column atacc1."........pg.dropped.1........" is 'testing';
654
alter table atacc1 alter a set storage plain;
655
alter table atacc1 alter "........pg.dropped.1........" set storage plain;
656
alter table atacc1 alter a set statistics 0;
657
alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
658
alter table atacc1 alter a set default 3;
659
alter table atacc1 alter "........pg.dropped.1........" set default 3;
660
alter table atacc1 alter a drop default;
661
alter table atacc1 alter "........pg.dropped.1........" drop default;
662
alter table atacc1 alter a set not null;
663
alter table atacc1 alter "........pg.dropped.1........" set not null;
664
alter table atacc1 alter a drop not null;
665
alter table atacc1 alter "........pg.dropped.1........" drop not null;
666
alter table atacc1 rename a to x;
667
alter table atacc1 rename "........pg.dropped.1........" to x;
668
alter table atacc1 add primary key(a);
669
alter table atacc1 add primary key("........pg.dropped.1........");
670
alter table atacc1 add unique(a);
671
alter table atacc1 add unique("........pg.dropped.1........");
672
alter table atacc1 add check (a > 3);
673
alter table atacc1 add check ("........pg.dropped.1........" > 3);
674
create table atacc2 (id int4 unique);
675
alter table atacc1 add foreign key (a) references atacc2(id);
676
alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
677
alter table atacc2 add foreign key (id) references atacc1(a);
678
alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
680
create index "testing_idx" on atacc1(a);
681
create index "testing_idx" on atacc1("........pg.dropped.1........");
683
-- test create as and select into
684
insert into atacc1 values (21, 22, 23);
685
create table test1 as select * from atacc1;
688
select * into test2 from atacc1;
692
-- try dropping all columns
693
alter table atacc1 drop c;
694
alter table atacc1 drop d;
695
alter table atacc1 drop b;
696
select * from atacc1;
701
create table parent (a int, b int, c int);
702
insert into parent values (1, 2, 3);
703
alter table parent drop a;
704
create table child (d varchar(255)) inherits (parent);
705
insert into child values (12, 13, 'testing');
707
select * from parent;
709
alter table parent drop c;
710
select * from parent;
717
create table test (a int4, b int4, c int4);
718
insert into test values (1,2,3);
719
alter table test drop a;
721
copy test(a) to stdout;
722
copy test("........pg.dropped.1........") to stdout;
723
copy test from stdin;
727
copy test from stdin;
731
copy test(a) from stdin;
732
copy test("........pg.dropped.1........") from stdin;
733
copy test(b,c) from stdin;
741
create table dropColumn (a int, b int, e int);
742
create table dropColumnChild (c int) inherits (dropColumn);
743
create table dropColumnAnother (d int) inherits (dropColumnChild);
745
-- these two should fail
746
alter table dropColumnchild drop column a;
747
alter table only dropColumnChild drop column b;
749
-- these three should work
750
alter table only dropColumn drop column e;
751
alter table dropColumnChild drop column c;
752
alter table dropColumn drop column a;
754
create table renameColumn (a int);
755
create table renameColumnChild (b int) inherits (renameColumn);
756
create table renameColumnAnother (c int) inherits (renameColumnChild);
758
-- these three should fail
759
alter table renameColumnChild rename column a to d;
760
alter table only renameColumnChild rename column a to d;
761
alter table only renameColumn rename column a to d;
764
alter table renameColumn rename column a to d;
765
alter table renameColumnChild rename column b to a;
768
alter table renameColumn add column w int;
771
alter table only renameColumn add column x int;
774
-- Test corner cases in dropping of inherited columns
776
create table p1 (f1 int, f2 int);
777
create table c1 (f1 int not null) inherits(p1);
779
-- should be rejected since c1.f1 is inherited
780
alter table c1 drop column f1;
782
alter table p1 drop column f1;
783
-- c1.f1 is still there, but no longer inherited
785
alter table c1 drop column f1;
788
drop table p1 cascade;
790
create table p1 (f1 int, f2 int);
791
create table c1 () inherits(p1);
793
-- should be rejected since c1.f1 is inherited
794
alter table c1 drop column f1;
795
alter table p1 drop column f1;
796
-- c1.f1 is dropped now, since there is no local definition for it
799
drop table p1 cascade;
801
create table p1 (f1 int, f2 int);
802
create table c1 () inherits(p1);
804
-- should be rejected since c1.f1 is inherited
805
alter table c1 drop column f1;
806
alter table only p1 drop column f1;
807
-- c1.f1 is NOT dropped, but must now be considered non-inherited
808
alter table c1 drop column f1;
810
drop table p1 cascade;
812
create table p1 (f1 int, f2 int);
813
create table c1 (f1 int not null) inherits(p1);
815
-- should be rejected since c1.f1 is inherited
816
alter table c1 drop column f1;
817
alter table only p1 drop column f1;
818
-- c1.f1 is still there, but no longer inherited
819
alter table c1 drop column f1;
821
drop table p1 cascade;
823
create table p1(id int, name text);
824
create table p2(id2 int, name text, height int);
825
create table c1(age int) inherits(p1,p2);
826
create table gc1() inherits (c1);
828
select relname, attname, attinhcount, attislocal
829
from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
830
where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
831
order by relname, attnum;
834
alter table only p1 drop column name;
835
-- should work. Now c1.name is local and inhcount is 0.
836
alter table p2 drop column name;
837
-- should be rejected since its inherited
838
alter table gc1 drop column name;
839
-- should work, and drop gc1.name along
840
alter table c1 drop column name;
841
-- should fail: column does not exist
842
alter table gc1 drop column name;
843
-- should work and drop the attribute in all tables
844
alter table p2 drop column height;
846
select relname, attname, attinhcount, attislocal
847
from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
848
where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
849
order by relname, attnum;
851
drop table p1, p2 cascade;
854
-- Test the ALTER TABLE WITHOUT OIDS command
856
create table altstartwith (col integer) with oids;
858
insert into altstartwith values (1);
860
select oid > 0, * from altstartwith;
862
alter table altstartwith set without oids;
864
select oid > 0, * from altstartwith; -- fails
865
select * from altstartwith;
867
-- Run inheritance tests
868
create table altwithoid (col integer) with oids;
870
-- Inherits parents oid column
871
create table altinhoid () inherits (altwithoid) without oids;
873
insert into altinhoid values (1);
875
select oid > 0, * from altwithoid;
876
select oid > 0, * from altinhoid;
878
alter table altwithoid set without oids;
879
alter table altinhoid set without oids;
881
select oid > 0, * from altwithoid; -- fails
882
select oid > 0, * from altinhoid; -- fails
883
select * from altwithoid;
884
select * from altinhoid;
886
-- test renumbering of child-table columns in inherited operations
888
create table p1 (f1 int);
889
create table c1 (f2 text, f3 int) inherits (p1);
891
alter table p1 add column a1 int check (a1 > 0);
892
alter table p1 add column f2 text;
894
insert into p1 values (1,2,'abc');
895
insert into c1 values(11,'xyz',33,0); -- should fail
896
insert into c1 values(11,'xyz',33,22);
899
update p1 set a1 = a1 + 1, f2 = upper(f2);
902
drop table p1 cascade;
904
-- test that operations with a dropped column do not try to reference
907
create domain mytype as text;
908
create temp table foo (f1 text, f2 mytype, f3 text);
910
insert into foo values('aa','bb','cc');
913
drop domain mytype cascade;
916
insert into foo values('qq','rr');
918
update foo set f3 = 'zz';
920
select f3,max(f1) from foo group by f3;
922
-- Simple tests for alter table column type
923
alter table foo alter f1 TYPE integer; -- fails
924
alter table foo alter f1 TYPE varchar(10);
926
create table anothertab (atcol1 serial8, atcol2 boolean,
927
constraint anothertab_chk check (atcol1 <= 3));
929
insert into anothertab (atcol1, atcol2) values (default, true);
930
insert into anothertab (atcol1, atcol2) values (default, false);
931
select * from anothertab;
933
alter table anothertab alter column atcol1 type boolean; -- fails
934
alter table anothertab alter column atcol1 type integer;
936
select * from anothertab;
938
insert into anothertab (atcol1, atcol2) values (45, null); -- fails
939
insert into anothertab (atcol1, atcol2) values (default, null);
941
select * from anothertab;
943
alter table anothertab alter column atcol2 type text
944
using case when atcol2 is true then 'IT WAS TRUE'
945
when atcol2 is false then 'IT WAS FALSE'
946
else 'IT WAS NULL!' end;
948
select * from anothertab;
949
alter table anothertab alter column atcol1 type boolean
950
using case when atcol1 % 2 = 0 then true else false end; -- fails
951
alter table anothertab alter column atcol1 drop default;
952
alter table anothertab alter column atcol1 type boolean
953
using case when atcol1 % 2 = 0 then true else false end; -- fails
954
alter table anothertab drop constraint anothertab_chk;
956
alter table anothertab alter column atcol1 type boolean
957
using case when atcol1 % 2 = 0 then true else false end;
959
select * from anothertab;
961
drop table anothertab;
963
create table another (f1 int, f2 text);
965
insert into another values(1, 'one');
966
insert into another values(2, 'two');
967
insert into another values(3, 'three');
969
select * from another;
972
alter f1 type text using f2 || ' more',
973
alter f2 type bigint using f1 * 10;
975
select * from another;
added
removed
src/test/regress/sql/alter_table.sql
