Differences

This shows you the differences between two versions of the page.

--- snippets:postgresql [2016/10/10 12:18] – created allspark_cp
+++ snippets:postgresql [2017/05/11 08:10] (current) – allspark
@@ Line 9: / Line 9: @@
 )
 SELECT * FROM moved_rows;
+```
+# fast select distinct with index
+```
+WITH RECURSIVE t AS (
+        ( SELECT flows.hostname
+           FROM flows
+          ORDER BY flows.hostname
+         LIMIT 1)
+        UNION ALL
+         SELECT ( SELECT flows.hostname
+                   FROM flows
+                  WHERE flows.hostname::text > t_1.hostname::text
+                  ORDER BY flows.hostname
+                 LIMIT 1) AS hostname
+           FROM t t_1
+          WHERE t_1.hostname IS NOT NULL
+        )
+ SELECT t.hostname
+   FROM t
+  WHERE t.hostname IS NOT NULL;
 ```

allsparks place