4

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?

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

2 Answers 2

3

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
6
  • How they differ from the performance point of view?
    – mat_boy
    Feb 1, 2017 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, 2017 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, 2017 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, 2017 at 20:46
  • Just different values from an enum. Yeah, they should not be equal and could be null
    – mat_boy
    Feb 1, 2017 at 20:48
3
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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