1-- 2-- SELECT_DISTINCT 3-- 4 5-- 6-- awk '{print $3;}' onek.data | sort -n | uniq 7-- 8SELECT DISTINCT two FROM tmp ORDER BY 1; 9 10-- 11-- awk '{print $5;}' onek.data | sort -n | uniq 12-- 13SELECT DISTINCT ten FROM tmp ORDER BY 1; 14 15-- 16-- awk '{print $16;}' onek.data | sort -d | uniq 17-- 18SELECT DISTINCT string4 FROM tmp ORDER BY 1; 19 20-- 21-- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | 22-- sort +0n -1 +1d -2 +2n -3 23-- 24SELECT DISTINCT two, string4, ten 25 FROM tmp 26 ORDER BY two using <, string4 using <, ten using <; 27 28-- 29-- awk '{print $2;}' person.data | 30-- awk '{if(NF!=1){print $2;}else{print;}}' - emp.data | 31-- awk '{if(NF!=1){print $2;}else{print;}}' - student.data | 32-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $5;}else{print;}}' - stud_emp.data | 33-- sort -n -r | uniq 34-- 35SELECT DISTINCT p.age FROM person* p ORDER BY age using >; 36 37-- 38-- Check mentioning same column more than once 39-- 40 41EXPLAIN (VERBOSE, COSTS OFF) 42SELECT count(*) FROM 43 (SELECT DISTINCT two, four, two FROM tenk1) ss; 44 45SELECT count(*) FROM 46 (SELECT DISTINCT two, four, two FROM tenk1) ss; 47 48-- 49-- Compare results between plans using sorting and plans using hash 50-- aggregation. Force spilling in both cases by setting work_mem low. 51-- 52 53SET work_mem='64kB'; 54 55-- Produce results with sorting. 56 57SET enable_hashagg=FALSE; 58 59SET jit_above_cost=0; 60 61EXPLAIN (costs off) 62SELECT DISTINCT g%1000 FROM generate_series(0,9999) g; 63 64CREATE TABLE distinct_group_1 AS 65SELECT DISTINCT g%1000 FROM generate_series(0,9999) g; 66 67SET jit_above_cost TO DEFAULT; 68 69CREATE TABLE distinct_group_2 AS 70SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g; 71 72SET enable_hashagg=TRUE; 73 74-- Produce results with hash aggregation. 75 76SET enable_sort=FALSE; 77 78SET jit_above_cost=0; 79 80EXPLAIN (costs off) 81SELECT DISTINCT g%1000 FROM generate_series(0,9999) g; 82 83CREATE TABLE distinct_hash_1 AS 84SELECT DISTINCT g%1000 FROM generate_series(0,9999) g; 85 86SET jit_above_cost TO DEFAULT; 87 88CREATE TABLE distinct_hash_2 AS 89SELECT DISTINCT (g%1000)::text FROM generate_series(0,9999) g; 90 91SET enable_sort=TRUE; 92 93SET work_mem TO DEFAULT; 94 95-- Compare results 96 97(SELECT * FROM distinct_hash_1 EXCEPT SELECT * FROM distinct_group_1) 98 UNION ALL 99(SELECT * FROM distinct_group_1 EXCEPT SELECT * FROM distinct_hash_1); 100 101(SELECT * FROM distinct_hash_1 EXCEPT SELECT * FROM distinct_group_1) 102 UNION ALL 103(SELECT * FROM distinct_group_1 EXCEPT SELECT * FROM distinct_hash_1); 104 105DROP TABLE distinct_hash_1; 106DROP TABLE distinct_hash_2; 107DROP TABLE distinct_group_1; 108DROP TABLE distinct_group_2; 109 110-- 111-- Also, some tests of IS DISTINCT FROM, which doesn't quite deserve its 112-- very own regression file. 113-- 114 115CREATE TEMP TABLE disttable (f1 integer); 116INSERT INTO DISTTABLE VALUES(1); 117INSERT INTO DISTTABLE VALUES(2); 118INSERT INTO DISTTABLE VALUES(3); 119INSERT INTO DISTTABLE VALUES(NULL); 120 121-- basic cases 122SELECT f1, f1 IS DISTINCT FROM 2 as "not 2" FROM disttable; 123SELECT f1, f1 IS DISTINCT FROM NULL as "not null" FROM disttable; 124SELECT f1, f1 IS DISTINCT FROM f1 as "false" FROM disttable; 125SELECT f1, f1 IS DISTINCT FROM f1+1 as "not null" FROM disttable; 126 127-- check that optimizer constant-folds it properly 128SELECT 1 IS DISTINCT FROM 2 as "yes"; 129SELECT 2 IS DISTINCT FROM 2 as "no"; 130SELECT 2 IS DISTINCT FROM null as "yes"; 131SELECT null IS DISTINCT FROM null as "no"; 132 133-- negated form 134SELECT 1 IS NOT DISTINCT FROM 2 as "no"; 135SELECT 2 IS NOT DISTINCT FROM 2 as "yes"; 136SELECT 2 IS NOT DISTINCT FROM null as "no"; 137SELECT null IS NOT DISTINCT FROM null as "yes"; 138