Array to string sometimes concatenates fewer values array_to_string(array_agg(array_to_string(array[
            highlights,exceptions,inter_note,inter_status::text
           ,comp_note,comp_status::text,hard_note,hard_status::text]
        ,'|#|'::text))
     ,'|*|'::text) as status


By executing the above query the status should contain 8 values separated by |#| or |*|. But in some cases it returns only 3, 4 or 6 values. I need to get all 8 values weather the record is empty or not.

Example:


  original array: [|#||#||#||#|Complete|#||#|Undefined|#||#|Undefined]
  
  some cases: [|#||#||#|PASS|#|Complete|#|Undefined|#|Undefined]
  
  another case: [Complete|#|Undefined|#|Undefined]


Any idea why some array elements are missing?
Ensure that every field has a default value by using COALESCE.




create table t (highlights varchar(10), 
                exceptions varchar(10), 
                inter_note varchar(10),
                inter_status int,
                comp_note varchar(10), 
                comp_status int,
                hard_note varchar(10),
                hard_status int);

insert into t values ('a','b','c',1,'d',2,'e',3),('',null,'f',4,null,5,'g',null);



✓

2 rows affected






select array_to_string(
         array_agg(
           array_to_string(
             array[coalesce(highlights, ''),
                   coalesce(exceptions, ''),
                   coalesce(inter_note,inter_status::text, ''),
                   coalesce(comp_note, ''),
                   coalesce(comp_status::text, ''),
                   coalesce(hard_note, ''),
                   coalesce(hard_status::text, '')],'|#|'::text)),'|*|'::text) as status
from   t;



| status                                            |
| :------------------------------------------------ |
| a|#|b|#|c|#|d|#|2|#|e|#|3|*||#||#|f|#||#|5|#|g|#| |



dbfiddle here