Postgres query: array_to_string with empty values

Question

I am trying to combine rows and concatenate two columns (name, vorname) in a Postgres query.

This works good like this:

SELECT nummer, 
       array_to_string(array_agg(name|| ', ' ||vorname), '\n') as name
FROM  (
   SELECT DISTINCT
          nummer, name, vorname
   FROM   myTable
   ) AS m
GROUP  BY nummer 
ORDER  BY nummer;

Unfortunately, if "vorname" is empty I get no results although name has a value.

Is it possible get this working:

   array_to_string(array_agg(name|| ', ' ||vorname), '\n') as name

also if one column is empty?

Years later, I just want to say a big 'thank you'. This only came up recently in a project. Saved my life. — DavidHyogo
– DavidHyogo, Commented Mar 25, 2017 at 3:54

Ants Aasma · Accepted Answer · 2012-09-13 09:08:38Z

1

Use coalesce to convert NULL values to something that you can concatenate:

array_to_string(array_agg(name|| ', ' ||coalesce(vorname, '<missing>')), '\n')

Also, you can concatenate strings directly without collecting them to an array by using the string_agg function.

answered Sep 13, 2012 at 9:08

Ants Aasma

55.3k16 gold badges98 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pavel Stehule · Accepted Answer · 2012-09-13 10:27:33Z

1

If you have 9.1, then you can use third parameter for array_to_string - null string

array_to_string(array_agg(name), ',', '<missing>') from bbb

answered Sep 13, 2012 at 10:27

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Collectives™ on Stack Overflow

Postgres query: array_to_string with empty values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related