PostgreSQL – Array to String Sometimes Concatenates Fewer Values

arrayconcatnullpostgresql

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?

Best Answer

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

Related Question