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';