2 -- UNION (also INTERSECT, EXCEPT)
4 -- Simple UNION constructs
5 SELECT 1 AS two UNION SELECT 2;
12 SELECT 1 AS one UNION SELECT 1;
18 SELECT 1 AS two UNION ALL SELECT 2;
25 SELECT 1 AS two UNION ALL SELECT 1;
32 SELECT 1 AS three UNION SELECT 2 UNION SELECT 3;
40 SELECT 1 AS two UNION SELECT 2 UNION SELECT 2;
47 SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2;
55 SELECT 1.1 AS two UNION SELECT 2.2;
63 SELECT 1.1 AS two UNION SELECT 2;
70 SELECT 1 AS two UNION SELECT 2.2;
77 SELECT 1 AS one UNION SELECT 1.0::float8;
83 SELECT 1.1 AS two UNION ALL SELECT 2;
90 SELECT 1.0::float8 AS two UNION ALL SELECT 1;
97 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3;
105 SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
112 SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2;
120 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2);
128 -- Try testing from tables...
130 SELECT f1 AS five FROM FLOAT8_TBL
132 SELECT f1 FROM FLOAT8_TBL
135 -----------------------
136 -1.2345678901234e+200
139 -1.2345678901234e-200
143 SELECT f1 AS ten FROM FLOAT8_TBL
145 SELECT f1 FROM FLOAT8_TBL;
147 -----------------------
151 -1.2345678901234e+200
152 -1.2345678901234e-200
156 -1.2345678901234e+200
157 -1.2345678901234e-200
160 SELECT f1 AS nine FROM FLOAT8_TBL
162 SELECT f1 FROM INT4_TBL
165 -----------------------
166 -1.2345678901234e+200
171 -1.2345678901234e-200
177 SELECT f1 AS ten FROM FLOAT8_TBL
179 SELECT f1 FROM INT4_TBL;
181 -----------------------
185 -1.2345678901234e+200
186 -1.2345678901234e-200
194 SELECT f1 AS five FROM FLOAT8_TBL
195 WHERE f1 BETWEEN -1e6 AND 1e6
197 SELECT f1 FROM INT4_TBL
198 WHERE f1 BETWEEN 0 AND 1000000;
200 -----------------------
203 -1.2345678901234e-200
208 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
210 SELECT f1 FROM CHAR_TBL
219 SELECT f1 AS three FROM VARCHAR_TBL
221 SELECT CAST(f1 AS varchar) FROM CHAR_TBL
230 SELECT f1 AS eight FROM VARCHAR_TBL
232 SELECT f1 FROM CHAR_TBL;
245 SELECT f1 AS five FROM TEXT_TBL
247 SELECT f1 FROM VARCHAR_TBL
249 SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
261 -- INTERSECT and EXCEPT
263 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl;
270 SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl;
278 SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
285 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
292 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
300 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
305 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
312 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
323 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
329 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
331 -----------------------
332 -1.2345678901234e+200
335 -1.2345678901234e-200
339 -- Operator precedence and (((((extra))))) parentheses
341 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
353 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
360 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
372 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
379 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
391 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
399 -- Subqueries with ORDER BY & LIMIT clauses
401 -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
402 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
405 ------------------+-------------------
406 4567890123456789 | -4567890123456789
410 -- This should fail, because q2 isn't a name of an EXCEPT output column
411 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
412 ERROR: column "q2" does not exist
413 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
415 -- But this should work:
416 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
424 -- New syntaxes (7.1) permit new tests
426 (((((select * from int8_tbl)))));
428 ------------------+-------------------
430 123 | 4567890123456789
431 4567890123456789 | 123
432 4567890123456789 | 4567890123456789
433 4567890123456789 | -4567890123456789
437 -- Check handling of a case with unknown constants. We don't guarantee
438 -- an undecorated constant will work in all cases, but historically this
439 -- usage has worked, so test we don't break it.
441 SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
443 SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
453 -- This should fail, but it should produce an error cursor
454 SELECT '3.4'::numeric UNION SELECT 'foo';
455 ERROR: invalid input syntax for type numeric: "foo"
456 LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';