23

Supposing we have a table with four columns (a,b,c,d) of the same data type.

Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?

  • 7
    You mean SELECT a FROM tablename UNION SELECT b FROM tablename UNION SELECT c FROM tablename UNION SELECT d FROM tablename ; ? – ypercubeᵀᴹ May 28 '15 at 13:34
  • Yes. That would do but I'd have to run 4 queries. Wouldn't it be a performance bottleneck? – Fabrizio Mazzoni May 28 '15 at 13:36
  • 6
    That's one query, not 4. – ypercubeᵀᴹ May 28 '15 at 13:36
  • 1
    I can see several ways to write the query which may have different performance, depending on indexes available, etc. But I can't envision how a function would help – ypercubeᵀᴹ May 28 '15 at 13:41
  • 1
    OK. Giving it a go with UNION – Fabrizio Mazzoni May 28 '15 at 13:42
24

Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values).

A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the four columns, if Postgres had implemented Loose Index Scan optimization, which it hasn't. So this query will not be efficient as it requires 4 scans of the table (and no index is used):

-- Query 1. (334 ms, 368ms) 
SELECT a AS abcd FROM tablename 
UNION                           -- means UNION DISTINCT
SELECT b FROM tablename 
UNION 
SELECT c FROM tablename 
UNION 
SELECT d FROM tablename ;

Another would be to first UNION ALL and then use DISTINCT. This will also require 4 table scans (and no use of indexes). Not bad efficiency when the values are few, and with more values becomes the fastest in my (not extensive) test:

-- Query 2. (87 ms, 117 ms)
SELECT DISTINCT a AS abcd
FROM
  ( SELECT a FROM tablename 
    UNION ALL 
    SELECT b FROM tablename 
    UNION ALL
    SELECT c FROM tablename 
    UNION ALL
    SELECT d FROM tablename 
  ) AS x ;

The other answers have provided with more options using array functions or the LATERAL syntax. Jack's query (187 ms, 261 ms) has reasonable performance but AndriyM's query seems more efficient (125 ms, 155 ms). Both of them do one sequential scan of the table and do not use any index.

Actually Jack's query results are a bit better than shown above (if we remove the order by) and can be further improved by removing the 4 internal distinct and leaving only the external one.


Finally, if - and only if - the distinct values of the 4 columns are relatively few, you can use the WITH RECURSIVE hack/optimization described in the above Loose Index Scan page and use all 4 indexes, with remarkably fast result! Tested with the same 100K rows and approximately 25 distinct values spread across the 4 columns (runs in only 2 ms!) while with 25K distinct values it's the slowest with 368 ms:

-- Query 3.  (2 ms, 368ms)
WITH RECURSIVE 
    da AS (
       SELECT min(a) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(a) FROM observations
               WHERE  a > s.n)
       FROM   da AS s  WHERE s.n IS NOT NULL  ),
    db AS (
       SELECT min(b) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(b) FROM observations
               WHERE  b > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  ),
   dc AS (
       SELECT min(c) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(c) FROM observations
               WHERE  c > s.n)
       FROM   dc AS s  WHERE s.n IS NOT NULL  ),
   dd AS (
       SELECT min(d) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(d) FROM observations
               WHERE  d > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  )
SELECT n 
FROM 
( TABLE da  UNION 
  TABLE db  UNION 
  TABLE dc  UNION 
  TABLE dd
) AS x 
WHERE n IS NOT NULL ;

SQLfiddle


To summarize, when the distinct values are few, the recursive query is the absolute winner while with lots of values, my 2nd one, Jack's (improved version below) and AndriyM's queries are the best performers.


Late additions, a variation on the 1st query which despite the extra distinct operations, performs much better than the original 1st and only slightly worse than the 2nd:

-- Query 1b.  (85 ms, 149 ms)
SELECT DISTINCT a AS n FROM observations 
UNION 
SELECT DISTINCT b FROM observations 
UNION 
SELECT DISTINCT c FROM observations 
UNION 
SELECT DISTINCT d FROM observations ;

and Jack's improved:

-- Query 4b.  (104 ms, 128 ms)
select distinct unnest( array_agg(a)||
                        array_agg(b)||
                        array_agg(c)||
                        array_agg(d) )
from t ;
12

You could use LATERAL, like in this query:

SELECT DISTINCT
  x.n
FROM
  atable
  CROSS JOIN LATERAL (
    VALUES (a), (b), (c), (d)
  ) AS x (n)
;

The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values you want to put into a single column. The main query simply references the new column, also applying DISTINCT to it.

10

To be clear, I'd use union as ypercube suggests, but it is also possible with arrays:

select distinct unnest( array_agg(distinct a)||
                        array_agg(distinct b)||
                        array_agg(distinct c)||
                        array_agg(distinct d) )
from t
order by 1;
| unnest |
| :----- |
| 0      |
| 1      |
| 2      |
| 3      |
| 5      |
| 6      |
| 8      |
| 9      |

dbfiddle here

7

Shortest

SELECT DISTINCT n FROM observations, unnest(ARRAY[a,b,c,d]) n;

A less verbose version of Andriy's idea is only slightly longer, but more elegant and faster.
For many distinct / few duplicate values:

SELECT DISTINCT n FROM observations, LATERAL (VALUES (a),(b),(c),(d)) t(n);

Fastest

With an index on each involved column!
For few distinct / many duplicate values:

WITH RECURSIVE
  ta AS (
   (SELECT a FROM observations ORDER BY a LIMIT 1)  -- parentheses required!
   UNION ALL
   SELECT o.a FROM ta t
    , LATERAL (SELECT a FROM observations WHERE a > t.a ORDER BY a LIMIT 1) o
   )
, tb AS (
   (SELECT b FROM observations ORDER BY b LIMIT 1)
   UNION ALL
   SELECT o.b FROM tb t
    , LATERAL (SELECT b FROM observations WHERE b > t.b ORDER BY b LIMIT 1) o
   )
, tc AS (
   (SELECT c FROM observations ORDER BY c LIMIT 1)
   UNION ALL
   SELECT o.c FROM tc t
    , LATERAL (SELECT c FROM observations WHERE c > t.c ORDER BY c LIMIT 1) o
   )
, td AS (
   (SELECT d FROM observations ORDER BY d LIMIT 1)
   UNION ALL
   SELECT o.d FROM td t
    , LATERAL (SELECT d FROM observations WHERE d > t.d ORDER BY d LIMIT 1) o
   )
SELECT a
FROM  (
       TABLE ta
 UNION TABLE tb
 UNION TABLE tc
 UNION TABLE td
 ) sub;

This is another rCTE variant, similar to the one @ypercube already posted, but I use ORDER BY 1 LIMIT 1 instead of min(a) which is typically a bit faster. I also need no additional predicate to exclude NULL values.
And LATERAL instead of a correlated subquery, because it's cleaner (not necessarily faster).

Detailed explanation in my go-to answer for this technique:

I updated ypercube's SQL Fiddle and added mine to the playlist.

  • Can you test with EXPLAIN (ANALYZE, TIMING OFF) to verify best overall performance? (Best of 5 to exclude caching effects.) – Erwin Brandstetter May 29 '15 at 3:21
  • Interesting. I thought a comma join would be equivalent to a CROSS JOIN in every respect, i.e. in terms of performance too. Is the difference specific to using LATERAL? – Andriy M May 29 '15 at 5:11
  • Or maybe I misunderstood. When you said "faster" about the less verbose version of my suggestion, did you mean faster than mine or faster than the SELECT DISTINCT with unnest? – Andriy M May 29 '15 at 5:19
  • 1
    @AndriyM: The comma is equivalent (except that explicit ` CROSS JOIN` syntax binds stronger when resolving join sequence). Yes, I mean your idea with VALUES ... is faster than unnest(ARRAY[...]). LATERAL is implicit for set-returning functions in the FROM list. – Erwin Brandstetter May 29 '15 at 5:22
  • Thnx for the improvements! I tried the order/limit-1 variant but there wasn't any noticable difference. Using LATERAL there is pretty cool, avoiding the multiple IS NOT NULL checks, great. You should suggest this variant to the Postgres guys, to be added in the Loose-Index-Scan page. – ypercubeᵀᴹ May 29 '15 at 9:36
3

You can, but as I wrote and tested the function I felt wrong. It is a resources waste.
Just please use a union and more select. Only advantage (if it is), one single scan from main table.

In sql fiddle you need to change separator from $ to something else, like /

CREATE TABLE observations (
    id         serial
  , a int not null
  , b int not null
  , c int not null
  , d int not null
  , created_at timestamp
  , foo        text
);

INSERT INTO observations (a, b, c, d, created_at, foo)
SELECT (random() * 20)::int        AS a          -- few values for a,b,c,d
     , (15 + random() * 10)::int 
     , (10 + random() * 10)::int 
     , ( 5 + random() * 20)::int 
     , '2014-01-01 0:0'::timestamp 
       + interval '1s' * g         AS created_at -- ascending (probably like in real life)
     , 'aöguihaophgaduigha' || g   AS foo        -- random ballast
FROM generate_series (1, 10) g;               -- 10k rows

CREATE INDEX observations_a_idx ON observations (a);
CREATE INDEX observations_b_idx ON observations (b);
CREATE INDEX observations_c_idx ON observations (c);
CREATE INDEX observations_d_idx ON observations (d);

CREATE OR REPLACE FUNCTION fn_readuniqu()
  RETURNS SETOF text AS $$
DECLARE
    a_array     text[];
    b_array     text[];
    c_array     text[];
    d_array     text[];
    r       text;
BEGIN

    SELECT INTO a_array, b_array, c_array, d_array array_agg(a), array_agg(b), array_agg(c), array_agg(d)
    FROM observations;

    FOR r IN
        SELECT DISTINCT x
        FROM
        (
            SELECT unnest(a_array) AS x
            UNION
            SELECT unnest(b_array) AS x
            UNION
            SELECT unnest(c_array) AS x
            UNION
            SELECT unnest(d_array) AS x
        ) AS a

    LOOP
        RETURN NEXT r;
    END LOOP;

END;
$$
  LANGUAGE plpgsql STABLE
  COST 100
  ROWS 1000;

SELECT * FROM fn_readuniqu();
  • You're actually right as a function would still use a union. In any case +1 for the effort. – Fabrizio Mazzoni May 28 '15 at 15:36
  • 2
    Why are you doing this array and cursor magic? @ypercube's solution does the work and it's very easy to wrap into a SQL language function. – dezso May 28 '15 at 15:43
  • Sorry, I couldn't make your function to compile. I probably did something silly. If you manage to have it working here, please provide me with a link and I'll update my answer with results, so we can compare with the other answers. – ypercubeᵀᴹ May 29 '15 at 0:09
  • @ypercube Edited solution must work. Remember to change the separator in fiddle. I tested on my local db with table creation and works fine. – user_0 May 29 '15 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.