1-- 2-- SELECT_HAVING 3-- 4-- load test data 5CREATE TABLE test_having (a int, b int, c char(8), d char); 6INSERT INTO test_having VALUES (0, 1, 'XXXX', 'A'); 7INSERT INTO test_having VALUES (1, 2, 'AAAA', 'b'); 8INSERT INTO test_having VALUES (2, 2, 'AAAA', 'c'); 9INSERT INTO test_having VALUES (3, 3, 'BBBB', 'D'); 10INSERT INTO test_having VALUES (4, 3, 'BBBB', 'e'); 11INSERT INTO test_having VALUES (5, 3, 'bbbb', 'F'); 12INSERT INTO test_having VALUES (6, 4, 'cccc', 'g'); 13INSERT INTO test_having VALUES (7, 4, 'cccc', 'h'); 14INSERT INTO test_having VALUES (8, 4, 'CCCC', 'I'); 15INSERT INTO test_having VALUES (9, 4, 'CCCC', 'j'); 16SELECT b, c FROM test_having 17 GROUP BY b, c HAVING count(*) = 1 ORDER BY b, c; 18 b | c 19---+---------- 20 1 | XXXX 21 3 | bbbb 22(2 rows) 23 24-- HAVING is effectively equivalent to WHERE in this case 25SELECT b, c FROM test_having 26 GROUP BY b, c HAVING b = 3 ORDER BY b, c; 27 b | c 28---+---------- 29 3 | BBBB 30 3 | bbbb 31(2 rows) 32 33SELECT lower(c), count(c) FROM test_having 34 GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a) 35 ORDER BY lower(c); 36 lower | count 37-------+------- 38 bbbb | 3 39 cccc | 4 40 xxxx | 1 41(3 rows) 42 43SELECT c, max(a) FROM test_having 44 GROUP BY c HAVING count(*) > 2 OR min(a) = max(a) 45 ORDER BY c; 46 c | max 47----------+----- 48 bbbb | 5 49 XXXX | 0 50(2 rows) 51 52-- test degenerate cases involving HAVING without GROUP BY 53-- Per SQL spec, these should generate 0 or 1 row, even without aggregates 54SELECT min(a), max(a) FROM test_having HAVING min(a) = max(a); 55 min | max 56-----+----- 57(0 rows) 58 59SELECT min(a), max(a) FROM test_having HAVING min(a) < max(a); 60 min | max 61-----+----- 62 0 | 9 63(1 row) 64 65-- errors: ungrouped column references 66SELECT a FROM test_having HAVING min(a) < max(a); 67ERROR: column "test_having.a" must appear in the GROUP BY clause or be used in an aggregate function 68LINE 1: SELECT a FROM test_having HAVING min(a) < max(a); 69 ^ 70SELECT 1 AS one FROM test_having HAVING a > 1; 71ERROR: column "test_having.a" must appear in the GROUP BY clause or be used in an aggregate function 72LINE 1: SELECT 1 AS one FROM test_having HAVING a > 1; 73 ^ 74-- the really degenerate case: need not scan table at all 75SELECT 1 AS one FROM test_having HAVING 1 > 2; 76 one 77----- 78(0 rows) 79 80SELECT 1 AS one FROM test_having HAVING 1 < 2; 81 one 82----- 83 1 84(1 row) 85 86-- and just to prove that we aren't scanning the table: 87SELECT 1 AS one FROM test_having WHERE 1/a = 1 HAVING 1 < 2; 88 one 89----- 90 1 91(1 row) 92 93DROP TABLE test_having; 94