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