Horton
Menu

Thread

  1. what is good solution for support NULL inside string_to_array function?

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-03T22:05:39Z

    Hello

I understand why we don't support expression 'null'::sometype. But it
does problems with array deserialisation.

postgres=# select array_to_string(ARRAY[10,20,30,NULL,30], '|');
 array_to_string
-----------------
 10|20|30|30
(1 row)

quietly removing NULL is maybe good for compatibility but is wrong for
functionality. Can we enhance function array_to_string and
string_to_array like:

CREATE OR REPLACE FUNCTION array_to_string(dta anyarray, sep text,
nullsym text)
RETURNS text AS $$
  SELECT array_to_string(ARRAY(SELECT coalesce(v::text,$3)
                                                    FROM unnest($1) g(v)),$2)
$$ LANGUAGE sql;
CREATE FUNCTION
Time: 231.445 ms
postgres=# select array_to_string(ARRAY[10,20,30,NULL,30], '|', '');
 array_to_string
-----------------
 10|20|30||30
(1 row)

Time: 230.879 ms
postgres=# select array_to_string(ARRAY[10,20,30,NULL,30], '|', 'NULL');
 array_to_string
------------------
 10|20|30|NULL|30
(1 row)

Time: 2.031 ms

CREATE OR REPLACE FUNCTION string_to_array(str text, sep text, nullsym text)
RETURNS text[] AS $$
  SELECT ARRAY(SELECT CASE WHEN v <> $3 THEN v ELSE NULL END
                                 FROM unnest(string_to_array($1,$2)) g(v))
$$ LANGUAGE sql;
CREATE FUNCTION
Time: 29.044 ms

postgres=# SELECT string_to_array('10,20,30,,40',',','');
  string_to_array
--------------------
 {10,20,30,NULL,40}
(1 row)

postgres=# SELECT string_to_array('10,20,30,,40',',','')::int[];
  string_to_array
--------------------
 {10,20,30,NULL,40}
(1 row)

it is correct?

other ideas?

Regards
Pavel Stehule

  2. Re: what is good solution for support NULL inside string_to_array function?

    Josh Berkus <josh@agliodbs.com> — 2010-05-04T00:55:06Z

    > quietly removing NULL is maybe good for compatibility but is wrong for
> functionality. 

I agree.  I wasn't aware of this little misfeature.

Default display for NULL should be a zero-length string.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  3. Re: what is good solution for support NULL inside string_to_array function?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-05-04T02:54:36Z

    Josh Berkus <josh@agliodbs.com> writes:
>> quietly removing NULL is maybe good for compatibility but is wrong for
>> functionality. 

> I agree.  I wasn't aware of this little misfeature.

> Default display for NULL should be a zero-length string.

That's just as broken as Pavel's suggestion.  Unless you have something
that is guaranteed distingishable from the output of any non-null value,
you really can't make a significant improvement here.

			regards, tom lane

  4. Re: what is good solution for support NULL inside string_to_array function?

    Andrew Dunstan <andrew@dunslane.net> — 2010-05-04T03:18:32Z

    
Tom Lane wrote:
>> Default display for NULL should be a zero-length string.
>>     
>
> That's just as broken as Pavel's suggestion.  Unless you have something
> that is guaranteed distingishable from the output of any non-null value,
> you really can't make a significant improvement here.
>
> 			
>   

Right. This is the problem we solved in CSV processing by distinguishing 
between quoted and unquoted values that could be null - the CSV rule is 
that a null value isn't quoted.

cheers

andrew

  5. Re: what is good solution for support NULL inside string_to_array function?

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-04T05:36:19Z

    2010/5/4 Tom Lane <tgl@sss.pgh.pa.us>:
> Josh Berkus <josh@agliodbs.com> writes:
>>> quietly removing NULL is maybe good for compatibility but is wrong for
>>> functionality.
>
>> I agree.  I wasn't aware of this little misfeature.
>
>> Default display for NULL should be a zero-length string.
>
> That's just as broken as Pavel's suggestion.  Unless you have something
> that is guaranteed distingishable from the output of any non-null value,
> you really can't make a significant improvement here.
>

I wouldn't modify current two params string_to_array and
array_to_string function. So there are not any default string (maybe
empty string) for NULL. My proposal is new three params functions with
>>>explicit<<< "null string" definition. This cannot break
compatibility and enhance functionality - It is just short cut for
code from my proposal - in C this functionality can by implemented
much faster.

Regards
Pavel

>                        regards, tom lane
>

  6. Re: what is good solution for support NULL inside string_to_array function?

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-04T11:53:00Z

    2010/5/4 Josh Berkus <josh@agliodbs.com>:
>
>> quietly removing NULL is maybe good for compatibility but is wrong for
>> functionality.
>
> I agree.  I wasn't aware of this little misfeature.
>
> Default display for NULL should be a zero-length string.
>

I disagree - NULL is NULL, not empty string (Oracle is different)

if array_to_string is equivalent to

x[1] || sep || x[2] || sep || x[3] || sep ....

then correct result is NULL

and then string_to_array and array_to_string are correct, because
string_to_array cannot contain any NULL symbol.

Regards
Pavel Stehule



> --
>                                  -- Josh Berkus
>                                     PostgreSQL Experts Inc.
>                                     http://www.pgexperts.com
>

  7. Re: what is good solution for support NULL inside string_to_array function?

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-04T14:05:18Z

    2010/5/4 Pavel Stehule <pavel.stehule@gmail.com>:
> 2010/5/4 Tom Lane <tgl@sss.pgh.pa.us>:
>> Josh Berkus <josh@agliodbs.com> writes:
>>>> quietly removing NULL is maybe good for compatibility but is wrong for
>>>> functionality.
>>
>>> I agree.  I wasn't aware of this little misfeature.
>>
>>> Default display for NULL should be a zero-length string.
>>
>> That's just as broken as Pavel's suggestion.  Unless you have something
>> that is guaranteed distingishable from the output of any non-null value,
>> you really can't make a significant improvement here.
>>
>
> I wouldn't modify current two params string_to_array and
> array_to_string function. So there are not any default string (maybe
> empty string) for NULL. My proposal is new three params functions with
>>>>explicit<<< "null string" definition. This cannot break
> compatibility and enhance functionality - It is just short cut for
> code from my proposal - in C this functionality can by implemented
> much faster.

I did some coding - the patch can be very simple

postgres=# select array_to_string(array[1,2,3,4,5,null],',','*');
 array_to_string
-----------------
 1,2,3,4,5,*
(1 row)

Time: 0,501 ms
postgres=# select
string_to_array(array_to_string(array[1,2,3,4,5,null],',','*'),',','*');
 string_to_array
------------------
 {1,2,3,4,5,NULL}
(1 row)

Time: 0,617 ms

postgres=# select string_to_array('1,2,3,4,5,*',',','*')::int[];
 string_to_array
------------------
 {1,2,3,4,5,NULL}
(1 row)

Time: 0,652 ms

and then string_to_array and array_to_string are orthogonal with NULL.

Pavel


>
> Regards
> Pavel
>
>>                        regards, tom lane
>>
>

  8. Re: what is good solution for support NULL inside string_to_array function?

    Merlin Moncure <mmoncure@gmail.com> — 2010-05-04T14:24:45Z

    On Tue, May 4, 2010 at 10:05 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> and then string_to_array and array_to_string are orthogonal with NULL.

I like the behavior, but should it share the name with the 2 argument
version given the incompatibility? Maybe make a new function
to_string(anyarray, sep, nullsym='') and deprecate the old one?

merlin

  9. Re: what is good solution for support NULL inside string_to_array function?

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-04T14:32:48Z

    2010/5/4 Merlin Moncure <mmoncure@gmail.com>:
> On Tue, May 4, 2010 at 10:05 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> and then string_to_array and array_to_string are orthogonal with NULL.
>
> I like the behavior, but should it share the name with the 2 argument
> version given the incompatibility? Maybe make a new function
> to_string(anyarray, sep, nullsym='') and deprecate the old one?
>

maybe to_string X to_array ... Why not? It shorter, maybe it is cleaner

Regards
Pavel

> merlin
>

  10. Re: what is good solution for support NULL inside string_to_array function?

    Steve Crawford <scrawford@pinpointresearch.com> — 2010-05-07T17:53:10Z

    Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>   
>>> quietly removing NULL is maybe good for compatibility but is wrong for
>>> functionality. 
>>>       
>
>   
>> I agree.  I wasn't aware of this little misfeature.
>>     
>
>   
>> Default display for NULL should be a zero-length string.
>>     
>
> That's just as broken as Pavel's suggestion.  Unless you have something
> that is guaranteed distingishable from the output of any non-null value,
> you really can't make a significant improvement here.
>
> 			regards, tom lane
>
>   
Is this, perhaps, a generalized case of this long-running discussion 
from last year?: 
http://archives.postgresql.org/pgsql-hackers/2009-03/msg01350.php

Cheers,
Steve