6 CREATE TABLE tmp (initial int4);
8 ALTER TABLE tmp ADD COLUMN a int4;
10 ALTER TABLE tmp ADD COLUMN b name;
12 ALTER TABLE tmp ADD COLUMN c text;
14 ALTER TABLE tmp ADD COLUMN d float8;
16 ALTER TABLE tmp ADD COLUMN e float4;
18 ALTER TABLE tmp ADD COLUMN f int2;
20 ALTER TABLE tmp ADD COLUMN g polygon;
22 ALTER TABLE tmp ADD COLUMN h abstime;
24 ALTER TABLE tmp ADD COLUMN i char;
26 ALTER TABLE tmp ADD COLUMN j abstime[];
28 ALTER TABLE tmp ADD COLUMN k dt;
30 ALTER TABLE tmp ADD COLUMN l tid;
32 ALTER TABLE tmp ADD COLUMN m xid;
34 ALTER TABLE tmp ADD COLUMN n oidvector;
36 --ALTER TABLE tmp ADD COLUMN o lock;
37 ALTER TABLE tmp ADD COLUMN p smgr;
39 ALTER TABLE tmp ADD COLUMN q point;
41 ALTER TABLE tmp ADD COLUMN r lseg;
43 ALTER TABLE tmp ADD COLUMN s path;
45 ALTER TABLE tmp ADD COLUMN t box;
47 ALTER TABLE tmp ADD COLUMN u tinterval;
49 ALTER TABLE tmp ADD COLUMN v datetime;
51 ALTER TABLE tmp ADD COLUMN w timespan;
53 ALTER TABLE tmp ADD COLUMN x float8[];
55 ALTER TABLE tmp ADD COLUMN y float4[];
57 ALTER TABLE tmp ADD COLUMN z int2[];
59 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
61 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
62 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
64 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
65 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["current" "infinity"]',
66 '1/3', '1,name', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
72 -- the wolf bug - schema mods caused inconsistent row descriptors
77 ALTER TABLE tmp ADD COLUMN a int4;
79 ALTER TABLE tmp ADD COLUMN b name;
81 ALTER TABLE tmp ADD COLUMN c text;
83 ALTER TABLE tmp ADD COLUMN d float8;
85 ALTER TABLE tmp ADD COLUMN e float4;
87 ALTER TABLE tmp ADD COLUMN f int2;
89 ALTER TABLE tmp ADD COLUMN g polygon;
91 ALTER TABLE tmp ADD COLUMN h abstime;
93 ALTER TABLE tmp ADD COLUMN i char;
95 ALTER TABLE tmp ADD COLUMN j abstime[];
97 ALTER TABLE tmp ADD COLUMN k dt;
99 ALTER TABLE tmp ADD COLUMN l tid;
101 ALTER TABLE tmp ADD COLUMN m xid;
103 ALTER TABLE tmp ADD COLUMN n oidvector;
105 --ALTER TABLE tmp ADD COLUMN o lock;
106 ALTER TABLE tmp ADD COLUMN p smgr;
108 ALTER TABLE tmp ADD COLUMN q point;
110 ALTER TABLE tmp ADD COLUMN r lseg;
112 ALTER TABLE tmp ADD COLUMN s path;
114 ALTER TABLE tmp ADD COLUMN t box;
116 ALTER TABLE tmp ADD COLUMN u tinterval;
118 ALTER TABLE tmp ADD COLUMN v datetime;
120 ALTER TABLE tmp ADD COLUMN w timespan;
122 ALTER TABLE tmp ADD COLUMN x float8[];
124 ALTER TABLE tmp ADD COLUMN y float4[];
126 ALTER TABLE tmp ADD COLUMN z int2[];
128 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
130 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
131 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
132 314159, '(1,1)', 512,
133 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
134 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["current" "infinity"]',
135 '1/3', '1,name', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
144 -- should preserve indices, which we can check by seeing if a SELECT
145 -- chooses an indexscan; however, in the absence of vacuum statistics
146 -- it might not. Therefore, vacuum first.
148 VACUUM ANALYZE tenk1;
150 ALTER TABLE tenk1 RENAME TO ten_k;
153 SELECT unique1 FROM ten_k WHERE unique1 < 20;
156 SELECT unique2 FROM ten_k WHERE unique2 < 20;
158 -- 100 values, sorted
159 SELECT hundred FROM ten_k WHERE hundred = 50;
161 ALTER TABLE ten_k RENAME TO tenk1;
164 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
166 -- FOREIGN KEY CONSTRAINT adding TEST
168 CREATE TABLE tmp2 (a int primary key);
170 CREATE TABLE tmp3 (a int, b int);
172 -- Insert rows into tmp2 (pktable)
173 INSERT INTO tmp2 values (1);
174 INSERT INTO tmp2 values (2);
175 INSERT INTO tmp2 values (3);
176 INSERT INTO tmp2 values (4);
178 -- Insert rows into tmp3
179 INSERT INTO tmp3 values (1,10);
180 INSERT INTO tmp3 values (1,20);
181 INSERT INTO tmp3 values (5,50);
183 -- Try (and fail) to add constraint due to invalid data
184 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
186 -- Delete failing row
187 DELETE FROM tmp3 where a=5;
190 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;