Thread: array_to_string

Hi there,

I used the function array_to_string, and I found it ignores NULL values, 
e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.

The function documentation doesn't explain this case.
So please tell me: is it the normal behavior or a bug ?

TIA,
Sabin 

"Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
> I used the function array_to_string, and I found it ignores NULL values, 
> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.

Do you have a better idea?
        regards, tom lane

Tom Lane wrote:
> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>> I used the function array_to_string, and I found it ignores NULL values, 
>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
> 
> Do you have a better idea?

If you're being strict it should presumably return NULL for the whole 
string. Or you'd need a "coalesce-to-this" parameter.

--   Richard Huxton  Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Tom Lane wrote:
>> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>>> I used the function array_to_string, and I found it ignores NULL values, 
>>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>> 
>> Do you have a better idea?

> If you're being strict it should presumably return NULL for the whole 
> string. Or you'd need a "coalesce-to-this" parameter.

If the whole array is NULL it would return NULL, but that seems
unhelpful for a single element being NULL ...
        regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Tom Lane wrote:
>>> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>>>> I used the function array_to_string, and I found it ignores NULL values, 
>>>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>>> Do you have a better idea?
> 
>> If you're being strict it should presumably return NULL for the whole 
>> string. Or you'd need a "coalesce-to-this" parameter.
> 
> If the whole array is NULL it would return NULL, but that seems
> unhelpful for a single element being NULL ...

It's the least surprising choice though. You'd expect array_to_string() 
to be a loop doing something like:  output = output || connector || arr[i]
Of course this will produce NULL if any of the inputs are NULL. Now that 
might be unhelpful, but that's NULL for you.

All you're doing is (presumably) adding a hidden test to the loop where 
Sabin seemed to expect COALESCE(arr[i],<something>). I'm not sure either 
is better across the board.

The problem comes with what <something> should be. You're either looking 
at a third parameter or perhaps more cleanly you need a 
coalesce_array(ARRAY,VAL) function which produces an output array where 
all the null elemts are replaced by VAL.

--   Richard Huxton  Archonet Ltd

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message 
news:20090.1175006990@sss.pgh.pa.us...
> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>> I used the function array_to_string, and I found it ignores NULL values,
>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>
> Do you have a better idea?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

I found the function would be useful to build dynamic statements that
includes array items.

For instance I have a function with an array argument of integer. In that
function I'd like to send that argument by a dynamic string to other
function. If array_to_string would return the whole elements, including
NULLs, I'd be able to build that statement. But with the present behavior,
the result statement damages the array structure.

On the other hand, I saw there is available its complement function
string_to_array. It would be nice to be able to reverse a string built by
array_to_string, with string_to_array, getting the original array. This
could happen if the function would consider the NULLs as well.

A simple 'NULL' string would be enough to fulfil this reversing process, so
that array_to_string( ARRAY[1,NULL,3], ',' ) would returns '1,NULL,3'.

A problem will occur when it would be applied to string arrays, because NULL
string, and 'NULL' string value would have the same result. This could be
solved if the string arrays would be formed with the same rules as for SQL
constant syntax. I mean with quotes. So that, array_to_string( ARRAY[ 'a',
'NULL', NULL, 'b'], '/' ) would return not a/NULL/NULL/b , but
'a'/'NULL'/NULL/'b'. Consequently, string_to_array function should interpret
this result.

Regards,
Sabin