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?