pgsql: How to put Array_to_string inside the COALESCE

Question

In select statement i have column max(array_to_string(v_date,'||'::text)) filter (where type_key=1) .

Now i have to use COALESCE, to display default values for v_date nulls.

Tables/data is here dbfiddle

select obj_key, max(updated_on) as updated_on, max(att.status) as status, 
      COALESCE(max(array_to_string(v_date,'||'::text)) filter (where att.type_key=1),max(default_value))  as "DOB",
      max(array_to_string(v_text,'||'::text)) filter (where att.type_key=2) as "First Name",
      max(array_to_string(v_text,'||'::text)) filter (where att.type_key=3) as "Last Name",
      max(array_to_string(v_number,'||'::text)) filter (where att.type_key=4) as "Contact"
from attributes att right join types ty on att.type_key=ty.type_key
group by obj_key 

DOB for obj_key 3 must be '1/1/1950' but its giving sample1 ?

Is it possible to use array_to_string inside coalesce to get none null values (default values) ?

Answer 1

You need to join on the types table again to get the value as you have only the default_values for the type_key for the values 2 and 3 when you join on the attributes table grouped by obj_key.

Try this version:

select obj_key, max(updated_on) as updated_on, max(att.status) as status, 
  COALESCE(max(array_to_string(v_date,'||'::text)) filter (where att.type_key=1),(select default_value from types where type_key=1))  as "DOB",
  COALESCE(max(array_to_string(v_text,'||'::text)) filter (where att.type_key=2),(select default_value from types where type_key=2)) as "First Name",
  COALESCE(max(array_to_string(v_text,'||'::text)) filter (where att.type_key=3),(select default_value from types where type_key=3))as "Last Name",
  COALESCE(max(array_to_string(v_number,'||'::text)) filter (where att.type_key=4),(select default_value from types where type_key=4)) as "Contact"
  from attributes att right join types ty on att.type_key=ty.type_key
  group by obj_key