what is good solution for support NULL inside string_to_array function?
in [PgSql]
|
Prev: what is good solution for support NULL inside string_to_arrayfunction?
Next: [HACKERS] Reg: SQL Query for Postgres 8.4.3
From: Tom Lane on 3 May 2010 22:54 Josh Berkus <josh(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 4 May 2010 01:36 2010/5/4 Tom Lane <tgl(a)sss.pgh.pa.us>: > Josh Berkus <josh(a)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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 4 May 2010 07:53 2010/5/4 Josh Berkus <josh(a)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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 4 May 2010 10:05 2010/5/4 Pavel Stehule <pavel.stehule(a)gmail.com>: > 2010/5/4 Tom Lane <tgl(a)sss.pgh.pa.us>: >> Josh Berkus <josh(a)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 >> >
From: Merlin Moncure on 4 May 2010 10:24
On Tue, May 4, 2010 at 10:05 AM, Pavel Stehule <pavel.stehule(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |