2 -- UNION (also INTERSECT, EXCEPT)
4 -- Simple UNION constructs
5 SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
12 SELECT 1 AS one UNION SELECT 1 ORDER BY 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 ORDER BY 1;
40 SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
47 SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
55 SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
63 SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
70 SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
77 SELECT 1 AS one UNION SELECT 1.0::float8 ORDER BY 1;
83 SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
90 SELECT 1.0::float8 AS two UNION ALL SELECT 1 ORDER BY 1;
97 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
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 ORDER BY 1;
120 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
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
201 -----------------------
204 -1.2345678901234e-200
209 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
211 SELECT f1 FROM CHAR_TBL
220 SELECT f1 AS three FROM VARCHAR_TBL
222 SELECT CAST(f1 AS varchar) FROM CHAR_TBL
231 SELECT f1 AS eight FROM VARCHAR_TBL
233 SELECT f1 FROM CHAR_TBL;
246 SELECT f1 AS five FROM TEXT_TBL
248 SELECT f1 FROM VARCHAR_TBL
250 SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
262 -- INTERSECT and EXCEPT
264 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
271 SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
279 SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
286 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
293 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
301 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
306 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
313 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
321 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
322 ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
324 (SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
325 ?column? | ?column? | ?column?
326 ----------+----------+----------
330 (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
331 ?column? | ?column? | ?column?
332 ----------+----------+----------
336 (SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
337 ?column? | ?column? | ?column?
338 ----------+----------+----------
342 (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
343 ?column? | ?column? | ?column?
344 ----------+----------+----------
351 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1;
357 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
359 -----------------------
360 -1.2345678901234e+200
363 -1.2345678901234e-200
367 -- Operator precedence and (((((extra))))) parentheses
369 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl ORDER BY 1;
381 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1;
388 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl ORDER BY 1))) UNION ALL SELECT q2 FROM int8_tbl;
400 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
407 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
419 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
427 -- Subqueries with ORDER BY & LIMIT clauses
429 -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
430 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
433 ------------------+-------------------
434 4567890123456789 | -4567890123456789
438 -- This should fail, because q2 isn't a name of an EXCEPT output column
439 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
440 ERROR: column "q2" does not exist
441 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
443 HINT: There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
444 -- But this should work:
445 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
453 -- New syntaxes (7.1) permit new tests
455 (((((select * from int8_tbl)))));
457 ------------------+-------------------
459 123 | 4567890123456789
460 4567890123456789 | 123
461 4567890123456789 | 4567890123456789
462 4567890123456789 | -4567890123456789
466 -- Check handling of a case with unknown constants. We don't guarantee
467 -- an undecorated constant will work in all cases, but historically this
468 -- usage has worked, so test we don't break it.
470 SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
472 SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
482 -- This should fail, but it should produce an error cursor
483 SELECT '3.4'::numeric UNION SELECT 'foo';
484 ERROR: invalid input syntax for type numeric: "foo"
485 LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
488 -- Test that expression-index constraints can be pushed down through
489 -- UNION or UNION ALL
491 CREATE TEMP TABLE t1 (a text, b text);
492 CREATE INDEX t1_ab_idx on t1 ((a || b));
493 CREATE TEMP TABLE t2 (ab text primary key);
494 INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y');
495 INSERT INTO t2 VALUES ('ab'), ('xy');
496 set enable_seqscan = off;
497 set enable_indexscan = on;
498 set enable_bitmapscan = off;
501 (SELECT a || b AS ab FROM t1
506 ---------------------------------------------
508 -> Index Scan using t1_ab_idx on t1
509 Index Cond: ((a || b) = 'ab'::text)
510 -> Index Only Scan using t2_pkey on t2
511 Index Cond: (ab = 'ab'::text)
516 (SELECT a || b AS ab FROM t1
521 ---------------------------------------------------
523 Group Key: ((t1.a || t1.b))
525 -> Index Scan using t1_ab_idx on t1
526 Index Cond: ((a || b) = 'ab'::text)
527 -> Index Only Scan using t2_pkey on t2
528 Index Cond: (ab = 'ab'::text)
532 -- Test that ORDER BY for UNION ALL can be pushed down to inheritance
535 CREATE TEMP TABLE t1c (b text, a text);
536 ALTER TABLE t1c INHERIT t1;
537 CREATE TEMP TABLE t2c (primary key (ab)) INHERITS (t2);
538 INSERT INTO t1c VALUES ('v', 'w'), ('c', 'd'), ('m', 'n'), ('e', 'f');
539 INSERT INTO t2c VALUES ('vw'), ('cd'), ('mn'), ('ef');
540 CREATE INDEX t1c_ab_idx on t1c ((a || b));
541 set enable_seqscan = on;
542 set enable_indexonlyscan = off;
545 (SELECT a || b AS ab FROM t1
550 ------------------------------------------------
553 Sort Key: ((t1.a || t1.b))
554 -> Index Scan using t1_ab_idx on t1
555 -> Index Scan using t1c_ab_idx on t1c
556 -> Index Scan using t2_pkey on t2
557 -> Index Scan using t2c_pkey on t2c
561 (SELECT a || b AS ab FROM t1
577 reset enable_seqscan;
578 reset enable_indexscan;
579 reset enable_bitmapscan;
580 -- This simpler variant of the above test has been observed to fail differently
581 create table events (event_id int primary key);
582 create table other_events (event_id int primary key);
583 create table events_child () inherits (events);
586 from (select event_id from events
588 select event_id from other_events) ss
591 ----------------------------------------------------------
593 Sort Key: events.event_id
594 -> Index Scan using events_pkey on events
596 Sort Key: events_child.event_id
597 -> Seq Scan on events_child
598 -> Index Scan using other_events_pkey on other_events
601 drop table events_child, events, other_events;
602 reset enable_indexonlyscan;
603 -- Test constraint exclusion of UNION ALL subqueries
606 (SELECT 1 AS t, * FROM tenk1 a
608 SELECT 2 AS t, * FROM tenk1 b) c
611 ---------------------------
613 -> Seq Scan on tenk1 b
616 -- Test that we push quals into UNION sub-selects only when it's safe
619 (SELECT 1 AS t, 2 AS x
621 SELECT 2 AS t, 4 AS x) ss
625 --------------------------------------------------
634 One-Time Filter: false
638 (SELECT 1 AS t, 2 AS x
640 SELECT 2 AS t, 4 AS x) ss
650 (SELECT 1 AS t, generate_series(1,10) AS x
652 SELECT 2 AS t, 4 AS x) ss
656 --------------------------------------------------------
659 -> Subquery Scan on ss
662 Group Key: (1), (generate_series(1, 10))
670 (SELECT 1 AS t, generate_series(1,10) AS x
672 SELECT 2 AS t, 4 AS x) ss
684 (SELECT 1 AS t, (random()*3)::int AS x
686 SELECT 2 AS t, 4 AS x) ss
690 ------------------------------------------------------------------------------------
693 -> Subquery Scan on ss
697 Sort Key: (1), (((random() * '3'::double precision))::integer)
704 (SELECT 1 AS t, (random()*3)::int AS x
706 SELECT 2 AS t, 4 AS x) ss
714 -- Test proper handling of parameterized appendrel paths when the
715 -- potential join qual is expensive
716 create function expensivefunc(int) returns int
717 language plpgsql immutable strict cost 10000
718 as $$begin return $1; end$$;
719 create temp table t3 as select generate_series(-1000,1000) as x;
720 create index t3i on t3 (expensivefunc(x));
724 (select * from t3 a union all select * from t3 b) ss
725 join int4_tbl on f1 = expensivefunc(x);
727 ------------------------------------------------------------
729 -> Seq Scan on int4_tbl
731 -> Index Scan using t3i on t3 a
732 Index Cond: (expensivefunc(x) = int4_tbl.f1)
733 -> Index Scan using t3i on t3 b
734 Index Cond: (expensivefunc(x) = int4_tbl.f1)
738 (select * from t3 a union all select * from t3 b) ss
739 join int4_tbl on f1 = expensivefunc(x);
747 drop function expensivefunc(int);
748 -- Test handling of appendrel quals that const-simplify into an AND
751 (select *, 0 as x from int8_tbl a
753 select *, 1 as x from int8_tbl b) ss
754 where (x = 0) or (q1 >= q2 and q1 <= q2);
756 ---------------------------------------------
758 -> Seq Scan on int8_tbl a
759 -> Seq Scan on int8_tbl b
760 Filter: ((q1 >= q2) AND (q1 <= q2))
764 (select *, 0 as x from int8_tbl a
766 select *, 1 as x from int8_tbl b) ss
767 where (x = 0) or (q1 >= q2 and q1 <= q2);
769 ------------------+-------------------+---
771 123 | 4567890123456789 | 0
772 4567890123456789 | 123 | 0
773 4567890123456789 | 4567890123456789 | 0
774 4567890123456789 | -4567890123456789 | 0
775 4567890123456789 | 4567890123456789 | 1