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?