Re: CONCAT returns null

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andreas Kretschmer
> Sent: Sonntag, 28. Februar 2016 11:30
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] CONCAT returns null
>
> Sterpu Victor <victor@caido.ro> wrote:
>
> > Hello
> >
> > I have this concat:
> > CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo, TO_CHAR
> > (fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))
> > that works fine but when I change to this(I added a ' with '):
> > ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' with ',
> > f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE
> > (fd1.validfrom))) AS temp,
> > then concat returns NULL.
> > Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is
> > still NULL.
> >
> > Thank you
>
> NULL concat with a value returns NULL. You can avoid that using
> COALESCE(value, ''), that returns the value, or, if the value NULL, ''.

That was my first thought, too, but:

db=> select concat(null,'sssdf',null,'dfg',NULL);
  concat
----------
 sssdfdfg
(1 row)

Bye Charles