# Select distinct multiple columns with one result column

I have a table like the following:

``````CREATE TABLE aschema.atable
(
id       BIGSERIAL,
col_a    aschema.anenum,
col_b    aschema.anenum,
col_c    aschema.anenum
)
``````

and I would like to do something like

``````SELECT DISTINCT unnest(array_agg(col_a, col_b, col_c)) as anenum_value
FROM aschema.atable
WHERE anenum_value IS NOT NULL
``````

but I do not know if it makes sense. Maybe with an index over each of the three column I can simply select independently the three columns like that:

``````SELECT DISTINCT value FROM (
SELECT col_a as value
FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
FROM aschema.atable  WHERE col_c IS NOT NULL
)
``````

But I am not sure about the performance.

Actually I have rows like

``````id  col_a  col_b  col_c
1   ABD    CDE    XYZ
2   CDE    null   null
3   ABD    null   null
3   FGH    LMN   null
``````

And I expect as a result

``````ABC
ABD
CDE
FGH
LMN
XYZ
``````

Any recommendation and good example?

• What are you trying to do? Show us test data, and what you would like to get out. – Evan Carroll Jan 31 '17 at 20:02
• @EvanCarroll updated the question with an example. Assume the values are enum values – mat_boy Jan 31 '17 at 20:07

There another several solutions:

1. Using `lateral` join and `values`:

``````select distinct
x
from
aschema.atable cross join lateral
(values(col_a),(col_b),(col_c)) as t(x)
where
x is not null
order by
x;
``````
2. Using arrays:

``````select distinct
x
from
(select unnest(array[col_a,col_b,col_c]) from aschema.atable) as     t(x)
where
x is not null
order by
x;
``````
3. Normalize data structure:

``````CREATE TABLE aschema.atable
(
id         bigserial,      -- Just an ID
trinity_id bigint not null -- It is ID from your original table
what       int check(what in (1,2,3)), -- 1 - a, 2 - b, 3 - c
value      aschema.anenum,
unique (trinity_id,what),
unique (trinity_id,value)  -- To be sure that each trinity have distinct values
);
``````

And for now your data could be:

```id  trinity_id what value
1   1          1    ABD
2   1          2    CDE
3   1          3    XYZ
4   2          1    CDE
5   3          1    ABD
6   4          1    FGH
7   4          2    LMN
```
• How they differ from the performance point of view? – mat_boy Feb 1 '17 at 19:51
• @mat_boy We are talking about very schematic query without any real data provided. You have three different solutions so you can to choose the most efficient one for your actual data. BTW the most efficient way is to normalize your data structure. – Abelisto Feb 1 '17 at 20:09
• Case 2 is exactly what I was looking for in my example! Good! I do not understand your normalization. You added a constraint so that `what` is in the set o values. Do you recommend to not use the enum? Can you be clearer, because I have up to n values from the enum and I would like to store up to 3 of them – mat_boy Feb 1 '17 at 20:29
• @mat_boy I there any logical difference between `col_a`, `col_b` and `col_c`? Is it ordered sequence or just a set of values? – Abelisto Feb 1 '17 at 20:46
• Just different values from an enum. Yeah, they should not be equal and could be null – mat_boy Feb 1 '17 at 20:48
``````SELECT DISTINCT value FROM (
SELECT col_a as value
FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
FROM aschema.atable  WHERE col_c IS NOT NULL
)
``````

The `UNION` there is `UNION [DISTINCT]`. You don't need to wrap it in `SELECT DISTINCT`. Though the previous pattern may be faster if it matters if you do `UNION ALL`, and wrap that in one `SELECT DISTINCT FROM ()`

``````SELECT col_a as value
FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
FROM aschema.atable  WHERE col_c IS NOT NULL
``````

Why doesn't that work to do what you want?

If you ever find yourself writing a query like this though, I would think the problem would be in the schema itself.