Обсуждение: what is good solution for support NULL inside string_to_array function?
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
> 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
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
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
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 >
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 >
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 >> >
Вложения
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
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 >
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