Lists: | pgsql-hackers |
---|
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-03 22:05:39 |
Message-ID: | AANLkTinzwMEPss-SO-w3p6AoQ23hBdcuiMQAeXSUc_xd@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 00:55:06 |
Message-ID: | 4BDF706A.8070207@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 02:54:36 |
Message-ID: | 292.1272941676@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Josh Berkus <josh(at)agliodbs(dot)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
From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 03:18:32 |
Message-ID: | 4BDF9208.10801@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 05:36:19 |
Message-ID: | l2i162867791005032236ja26d30fcu2fb430d65e4c7d3c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2010/5/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Josh Berkus <josh(at)agliodbs(dot)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
>
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 11:53:00 |
Message-ID: | x2w162867791005040453tc5c7ef5z9fd5711ffe457457@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2010/5/4 Josh Berkus <josh(at)agliodbs(dot)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
>
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 14:05:18 |
Message-ID: | x2k162867791005040705z6f2979bds256cc3241e38d78d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2010/5/4 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2010/5/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Josh Berkus <josh(at)agliodbs(dot)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
>>
>
Attachment | Content-Type | Size |
---|---|---|
3params_arrayfce.diff | application/octet-stream | 3.8 KB |
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 14:24:45 |
Message-ID: | z2wb42b73151005040724v32ba7db4ve2d70ef0cf6160a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Tue, May 4, 2010 at 10:05 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-04 14:32:48 |
Message-ID: | s2n162867791005040732j4fb14d45z642259db6682170d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2010/5/4 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Tue, May 4, 2010 at 10:05 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>
From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: what is good solution for support NULL inside string_to_array function? |
Date: | 2010-05-07 17:53:10 |
Message-ID: | 4BE45386.9010109@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)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