1-- 2-- SELECT_DISTINCT 3-- 4-- 5-- awk '{print $3;}' onek.data | sort -n | uniq 6-- 7SELECT DISTINCT two FROM tmp ORDER BY 1; 8 two 9----- 10 0 11 1 12(2 rows) 13 14-- 15-- awk '{print $5;}' onek.data | sort -n | uniq 16-- 17SELECT DISTINCT ten FROM tmp ORDER BY 1; 18 ten 19----- 20 0 21 1 22 2 23 3 24 4 25 5 26 6 27 7 28 8 29 9 30(10 rows) 31 32-- 33-- awk '{print $16;}' onek.data | sort -d | uniq 34-- 35SELECT DISTINCT string4 FROM tmp ORDER BY 1; 36 string4 37--------- 38 AAAAxx 39 HHHHxx 40 OOOOxx 41 VVVVxx 42(4 rows) 43 44-- 45-- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | 46-- sort +0n -1 +1d -2 +2n -3 47-- 48SELECT DISTINCT two, string4, ten 49 FROM tmp 50 ORDER BY two using <, string4 using <, ten using <; 51 two | string4 | ten 52-----+---------+----- 53 0 | AAAAxx | 0 54 0 | AAAAxx | 2 55 0 | AAAAxx | 4 56 0 | AAAAxx | 6 57 0 | AAAAxx | 8 58 0 | HHHHxx | 0 59 0 | HHHHxx | 2 60 0 | HHHHxx | 4 61 0 | HHHHxx | 6 62 0 | HHHHxx | 8 63 0 | OOOOxx | 0 64 0 | OOOOxx | 2 65 0 | OOOOxx | 4 66 0 | OOOOxx | 6 67 0 | OOOOxx | 8 68 0 | VVVVxx | 0 69 0 | VVVVxx | 2 70 0 | VVVVxx | 4 71 0 | VVVVxx | 6 72 0 | VVVVxx | 8 73 1 | AAAAxx | 1 74 1 | AAAAxx | 3 75 1 | AAAAxx | 5 76 1 | AAAAxx | 7 77 1 | AAAAxx | 9 78 1 | HHHHxx | 1 79 1 | HHHHxx | 3 80 1 | HHHHxx | 5 81 1 | HHHHxx | 7 82 1 | HHHHxx | 9 83 1 | OOOOxx | 1 84 1 | OOOOxx | 3 85 1 | OOOOxx | 5 86 1 | OOOOxx | 7 87 1 | OOOOxx | 9 88 1 | VVVVxx | 1 89 1 | VVVVxx | 3 90 1 | VVVVxx | 5 91 1 | VVVVxx | 7 92 1 | VVVVxx | 9 93(40 rows) 94 95-- 96-- awk '{print $2;}' person.data | 97-- awk '{if(NF!=1){print $2;}else{print;}}' - emp.data | 98-- awk '{if(NF!=1){print $2;}else{print;}}' - student.data | 99-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $5;}else{print;}}' - stud_emp.data | 100-- sort -n -r | uniq 101-- 102SELECT DISTINCT p.age FROM person* p ORDER BY age using >; 103 age 104----- 105 98 106 88 107 78 108 68 109 60 110 58 111 50 112 48 113 40 114 38 115 34 116 30 117 28 118 25 119 24 120 23 121 20 122 19 123 18 124 8 125(20 rows) 126 127-- 128-- Check mentioning same column more than once 129-- 130EXPLAIN (VERBOSE, COSTS OFF) 131SELECT count(*) FROM 132 (SELECT DISTINCT two, four, two FROM tenk1) ss; 133 QUERY PLAN 134-------------------------------------------------------- 135 Aggregate 136 Output: count(*) 137 -> HashAggregate 138 Output: tenk1.two, tenk1.four, tenk1.two 139 Group Key: tenk1.two, tenk1.four, tenk1.two 140 -> Seq Scan on public.tenk1 141 Output: tenk1.two, tenk1.four, tenk1.two 142(7 rows) 143 144SELECT count(*) FROM 145 (SELECT DISTINCT two, four, two FROM tenk1) ss; 146 count 147------- 148 4 149(1 row) 150 151-- 152-- Also, some tests of IS DISTINCT FROM, which doesn't quite deserve its 153-- very own regression file. 154-- 155CREATE TEMP TABLE disttable (f1 integer); 156INSERT INTO DISTTABLE VALUES(1); 157INSERT INTO DISTTABLE VALUES(2); 158INSERT INTO DISTTABLE VALUES(3); 159INSERT INTO DISTTABLE VALUES(NULL); 160-- basic cases 161SELECT f1, f1 IS DISTINCT FROM 2 as "not 2" FROM disttable; 162 f1 | not 2 163----+------- 164 1 | t 165 2 | f 166 3 | t 167 | t 168(4 rows) 169 170SELECT f1, f1 IS DISTINCT FROM NULL as "not null" FROM disttable; 171 f1 | not null 172----+---------- 173 1 | t 174 2 | t 175 3 | t 176 | f 177(4 rows) 178 179SELECT f1, f1 IS DISTINCT FROM f1 as "false" FROM disttable; 180 f1 | false 181----+------- 182 1 | f 183 2 | f 184 3 | f 185 | f 186(4 rows) 187 188SELECT f1, f1 IS DISTINCT FROM f1+1 as "not null" FROM disttable; 189 f1 | not null 190----+---------- 191 1 | t 192 2 | t 193 3 | t 194 | f 195(4 rows) 196 197-- check that optimizer constant-folds it properly 198SELECT 1 IS DISTINCT FROM 2 as "yes"; 199 yes 200----- 201 t 202(1 row) 203 204SELECT 2 IS DISTINCT FROM 2 as "no"; 205 no 206---- 207 f 208(1 row) 209 210SELECT 2 IS DISTINCT FROM null as "yes"; 211 yes 212----- 213 t 214(1 row) 215 216SELECT null IS DISTINCT FROM null as "no"; 217 no 218---- 219 f 220(1 row) 221 222-- negated form 223SELECT 1 IS NOT DISTINCT FROM 2 as "no"; 224 no 225---- 226 f 227(1 row) 228 229SELECT 2 IS NOT DISTINCT FROM 2 as "yes"; 230 yes 231----- 232 t 233(1 row) 234 235SELECT 2 IS NOT DISTINCT FROM null as "no"; 236 no 237---- 238 f 239(1 row) 240 241SELECT null IS NOT DISTINCT FROM null as "yes"; 242 yes 243----- 244 t 245(1 row) 246 247