string_to_array with an empty input string

On 10 August 2010 19:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Looking through Pavel's string_to_array patch, I notice that the new
version of string_to_array returns an empty (zero-element) array when
the input string is of zero length, whereas the traditional version
returned NULL instead.  The patch fails to emulate the old behavior
exactly, but rather than fix it, I wonder if we shouldn't make the two
versions behave the same.  (If we don't have them doing the same thing,
we're going to have to document the difference...)  The NULL result
seems a bit peculiar to me, and the empty-array result saner.  Given
the general inconsistency surrounding empty arrays elsewhere, I think
this wouldn't be a very problematic change for most users.

Thoughts?

I, personally, would expect an empty array output given an empty
input, and a null output for a null input.
--
Thom Brown
Registered Linux user: #516935

On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:

I, personally, would expect an empty array output given an empty
input, and a null output for a null input.

+1

David

On 10 August 2010 19:48, David E. Wheeler <david@kineticode.com> wrote:

On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:

I, personally, would expect an empty array output given an empty
input, and a null output for a null input.

+1

Agreed. After all, the result isn't indeterminate - it's an empty
array. Some people might think that it's useful for the result to be
NULL, but they'd probably also think that it's useful for an empty
string to be NULL.

--
Regards,
Peter Geoghegan

On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:

On 10 August 2010 19:48, David E. Wheeler <david@kineticode.com> wrote:

On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:

I, personally, would expect an empty array output given an empty
input, and a null output for a null input.

+1

Agreed. After all, the result isn't indeterminate - it's an empty
array. Some people might think that it's useful for the result to be
NULL, but they'd probably also think that it's useful for an empty
string to be NULL.

For what it's worth there are two reasonable return values for
string_to_array(''). It could be [] or it could be ['']. There are
applications where the former makes the most sense and there are
applications where the latter makes the most sense.

Ideally you really want string_to_array(array_to_string(x, ':'),':')
to return x. There's no safe return value to pick for the cases where
x=[''] and x=[] that will make this work.

--
greg

There's already been one rather-long thread on this topic.

http://thread.gmane.org/gmane.comp.db.postgresql.general/121450

In there I argue for the empty array interpretation and Tom goes back
and forth a few times. I'm not sure where that thread ended though.

--
greg

2010/8/11 Greg Stark <gsstark@mit.edu>:

On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:

On 10 August 2010 19:48, David E. Wheeler <david@kineticode.com> wrote:

On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:

I, personally, would expect an empty array output given an empty
input, and a null output for a null input.

+1

Agreed. After all, the result isn't indeterminate - it's an empty
array. Some people might think that it's useful for the result to be
NULL, but they'd probably also think that it's useful for an empty
string to be NULL.

For what it's worth there are two reasonable return values for
string_to_array(''). It could be [] or it could be ['']. There are
applications where the former makes the most sense and there are
applications where the latter makes the most sense.

you have a true. The safe solution is return NULL on empty string. But
this behave is pretty unpractical for all domains other than texts. On
numeric or date there are not possible described situation.

I have a two ideas, just ideas:

a) to create a text_to_array function as complement to string_to_array
function. This function is same as string_to_array, but empty string
can be a NULL. But I see it as too academical.

b) to create a functions "explode" for other than text domains. One
parameter can be a regtype of expected array (maybe element). Then we
can correctly to decide what is correct result for empty string, and
we can to safe a some memory/time because the result will not be a
short life text array but desired array.

explode('1,2,3,4,,5', ',', '', int[])

Similar function have to be implemented with parser and transformation
changes - so we can design this function more verbose if we want:

explode('1,2,3,4,5,,' TO int[] DELIMITER AS ',' NULL AS '')

delimiter and nullstr can be a optional.

c) do nothing and returns NULL for empty string :(

I like a variant b.

Regards

Pavel Stehule

Greg Stark <gsstark@mit.edu> writes:

Ideally you really want string_to_array(array_to_string(x, ':'),':')
to return x. There's no safe return value to pick for the cases where
x=[''] and x=[] that will make this work.

It's easy to see that string_to_array/array_to_string are *not* usable
as general-purpose serialize/deserialize operations, so sweating over
corner cases like this one seems a bit pointless. The design center
for 'em seems to be array elements that are numbers, so there's no
issue with empty strings and no great difficulty in picking delimiters
and null representations that can't appear in the data. I think they're
essentially worthless for arrays of text.

regards, tom lane

Greg Stark <gsstark@mit.edu> writes:

There's already been one rather-long thread on this topic.
http://thread.gmane.org/gmane.comp.db.postgresql.general/121450

In there I argue for the empty array interpretation and Tom goes back
and forth a few times. I'm not sure where that thread ended though.

I had forgotten that discussion. It looks like we trailed off without
any real consensus: there was about equal sentiment for an array with
zero elements and an array with one empty-string element. We ended
up leaving it alone because (a) that wouldn't break anything and (b)
you could use COALESCE() to substitute whichever behavior your
application needed for the case.

So maybe we need to revisit the issue. Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so. Is anybody still excited about the alternatives?

regards, tom lane

On Wed, Aug 11, 2010 at 10:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Greg Stark <gsstark@mit.edu> writes:

Ideally you really want string_to_array(array_to_string(x, ':'),':')
to return x. There's no safe return value to pick for the cases where
x=[''] and x=[] that will make this work.

It's easy to see that string_to_array/array_to_string are *not* usable
as general-purpose serialize/deserialize operations, so sweating over
corner cases like this one seems a bit pointless.  The design center
for 'em seems to be array elements that are numbers, so there's no
issue with empty strings and no great difficulty in picking delimiters
and null representations that can't appear in the data.  I think they're
essentially worthless for arrays of text.

array_to_string() is quite useful for arrays of text; I use it to
generate human-readable output, by setting the delimiter to ', '.

Whether string_to_array() is useful is another matter. It probably is
in some cases, but putting parsing logic into the database layer tends
to be a bit klunky, unless you know from context that you needn't
worry about the error cases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:

I had forgotten that discussion. It looks like we trailed off without
any real consensus: there was about equal sentiment for an array with
zero elements and an array with one empty-string element. We ended
up leaving it alone because (a) that wouldn't break anything and (b)
you could use COALESCE() to substitute whichever behavior your
application needed for the case.

So maybe we need to revisit the issue. Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so. Is anybody still excited about the alternatives?

% perl -E 'say q{"}, join(",", ""), q{"}'
""
% ruby -e 'puts %q{"} + [""].join(",") + %q{"}'
""
% python -c 'print "\"" + ",".join([""]) + "\""'
""

I believe those are all "", rather than '"' + undef + '"'.

Best,

David

"David E. Wheeler" <david@kineticode.com> writes:

On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:

So maybe we need to revisit the issue. Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so. Is anybody still excited about the alternatives?

% perl -E 'say q{"}, join(",", ""), q{"}'
""
% ruby -e 'puts %q{"} + [""].join(",") + %q{"}'
""
% python -c 'print "\"" + ",".join([""]) + "\""'
""

I believe those are all "", rather than '"' + undef + '"'.

If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever. Returning a single empty string will
make those cases fail. So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.

regards, tom lane

On Wed, Aug 11, 2010 at 12:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David E. Wheeler" <david@kineticode.com> writes:

On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:

So maybe we need to revisit the issue.  Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so.  Is anybody still excited about the alternatives?

% perl -E 'say q{"}, join(",", ""), q{"}'
""
% ruby -e 'puts %q{"} + [""].join(",") + %q{"}'
""
% python -c 'print "\"" + ",".join([""]) + "\""'
""

I believe those are all "", rather than '"' + undef + '"'.

If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever.  Returning a single empty string will
make those cases fail.  So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.

Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do. I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want. That's a bit more difficult
if you use return any other value. But I think your argument that an
empty array is better than a one-element array containing an empty
string is very much correct, as between those options.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do.  I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want.  That's a bit more difficult
if you use return any other value.

I think that there's a need for additional built-in array functions,
including one to succinctly test if an array has no elements.
Iterating through an array with plpgsql, for example, is more clunky
than it should be.

--
Regards,
Peter Geoghegan

On Aug 11, 2010, at 9:36 AM, Tom Lane wrote:

I believe those are all "", rather than '"' + undef + '"'.

If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever. Returning a single empty string will
make those cases fail. So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.

Seems to be precedent for that:

% perl -E 'say scalar @{[ split ",", ""]}'
0

Best,

David

On Aug 11, 2010, at 9:40 AM, Robert Haas wrote:

Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do.

Right, sorry about that.

I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want. That's a bit more difficult
if you use return any other value. But I think your argument that an
empty array is better than a one-element array containing an empty
string is very much correct, as between those options.

I prefer an empty array.

Best,

David

On Wed, Aug 11, 2010 at 1:32 PM, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:

Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do.  I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want.  That's a bit more difficult
if you use return any other value.

I think that there's a need for additional built-in array functions,
including one to succinctly test if an array has no elements.

What do you propose? I think the easiest ways to do it right now are:

array_length(arr, 1) is null

or just using an equality test, like this:

arr = '{}'::int[]

Iterating through an array with plpgsql, for example, is more clunky
than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Aug 11, 2010, at 10:53 AM, Robert Haas wrote:

Iterating through an array with plpgsql, for example, is more clunky
than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

That tends to over-flatten if you have nested arrays and just want to iterate over the top level. In that case you must use generate_subscripts().

Best,

David

On 08/11/2010 01:54 PM, David E. Wheeler wrote:

On Aug 11, 2010, at 10:53 AM, Robert Haas wrote:

Iterating through an array with plpgsql, for example, is more clunky
than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

That tends to over-flatten if you have nested arrays and just want to iterate over the top level. In that case you must use generate_subscripts().

for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ...

works well

cheers

andrew

On Aug 11, 2010, at 10:58 AM, Andrew Dunstan wrote:

for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ...

works well

for i in select array_subscripts(myarray, 1) loop ...

Best,

David

On 08/11/2010 01:59 PM, David E. Wheeler wrote:

On Aug 11, 2010, at 10:58 AM, Andrew Dunstan wrote:

for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ...

works well

for i in select array_subscripts(myarray, 1) loop ...

That's not a built-in function AFAIK.

cheers

andrew

On Aug 11, 2010, at 11:35 AM, Andrew Dunstan wrote:

for i in select array_subscripts(myarray, 1) loop ...

That's not a built-in function AFAIK.

Pavel pointed out to me only yesterday that it is:

http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

Best,

david

On 08/11/2010 02:39 PM, David E. Wheeler wrote:

On Aug 11, 2010, at 11:35 AM, Andrew Dunstan wrote:

for i in select array_subscripts(myarray, 1) loop ...

That's not a built-in function AFAIK.

Pavel pointed out to me only yesterday that it is:

http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

except that you mis-spelled it: It's generate_subscripts(), not
array_subscripts().

I guess I must have missed that in the 8.4 features.

cheers

andrew

On 11 August 2010 18:53, Robert Haas <robertmhaas@gmail.com> wrote:

I think that there's a need for additional built-in array functions,
including one to succinctly test if an array has no elements.

What do you propose?  I think the easiest ways to do it right now are:

array_length(arr, 1) is null

or just using an equality test, like this:

arr = '{}'::int[]

What's wrong with something like array_is_empty(anyarray) returns
boolean? I don't know why we're so apparently averse to creating
built-in convenience functions. It's quite easy to forget the intent
of either of those two statements.

Iterating through an array with plpgsql, for example, is more clunky
than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

Right. I agree that many of the idioms are on the clunky side, but I
think that the fact that my original remarks about iterating over
arrays generated discussion is a bit telling. unnest() was only
introduced in PG 8.4.

Iterating over an array is a simple thing. We should make simple things easy.

--
Regards,
Peter Geoghegan

Peter Geoghegan <peter.geoghegan86@gmail.com> writes:

What's wrong with something like array_is_empty(anyarray) returns
boolean?

What's that got to do with iterating over an array? We could certainly
provide it if it were commonly useful, but I'm not convinced of that.

regards, tom lane

On Wed, Aug 11, 2010 at 4:21 PM, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:

On 11 August 2010 18:53, Robert Haas <robertmhaas@gmail.com> wrote:

I think that there's a need for additional built-in array functions,
including one to succinctly test if an array has no elements.

What do you propose?  I think the easiest ways to do it right now are:

array_length(arr, 1) is null

or just using an equality test, like this:

arr = '{}'::int[]

What's wrong with something like array_is_empty(anyarray) returns
boolean? I don't know why we're so apparently averse to creating
built-in convenience functions. It's quite easy to forget the intent
of either of those two statements.

Nothing's wrong with it, but the second one seems pretty hard to
forget the intent of... at least to me.

Iterating through an array with plpgsql, for example, is more clunky
than it should be.

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

Right. I agree that many of the idioms are on the clunky side, but I
think that the fact that my original remarks about iterating over
arrays generated discussion is a bit telling. unnest() was only
introduced in PG 8.4.

True... but now we have it.

Iterating over an array is a simple thing. We should make simple things easy.

I definitely agree that PL/pgsql could be more usable. Or if not,
then some other PL with a better overall design could be more usable.
I am not entirely sure how to create such a thing, however.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On 11 August 2010 21:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Geoghegan <peter.geoghegan86@gmail.com> writes:

What's wrong with something like array_is_empty(anyarray) returns
boolean?

What's that got to do with iterating over an array?

Only that I'm of the opinion that we'd be well served by more array
convenience functions, including convenience functions like the
proposed array_is_empty(), and, perhaps, functions that exist for the
express purpose of iterating over arrays more tersely in plpgsql.

We could certainly
provide it if it were commonly useful, but I'm not convinced of that.

Fair enough. I agree that it isn't *commonly* useful. I just don't
think that we're disadvantaged by being more inclusive, while still
bringing benefits to a minority of applications and users.

On 11 August 2010 23:06, Robert Haas <robertmhaas@gmail.com> wrote:

Iterating over an array is a simple thing. We should make simple things easy.

I definitely agree that PL/pgsql could be more usable.  Or if not,
then some other PL with a better overall design could be more usable.
I am not entirely sure how to create such a thing, however.

Nor am I. However, I think that richer array manipulation facilities
are low-hanging fruit.

--
Regards,
Peter Geoghegan

Really?

FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP

I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.

this simple construction can take much more memory than other. I
proposed two or three years ago FOREACH statement

FOREACH var IN array LOOP END LOOP

this statement can be implemented very efective - and I think it can
be joined to some form of string_to_array function, because var
specify target element type.

FOREACH var IN parse('....',...) LOOP END LOOP

Regards

Pavel Stehule

I definitely agree that PL/pgsql could be more usable. Or if not,
then some other PL with a better overall design could be more usable.
I am not entirely sure how to create such a thing, however.

Would the standard plpsm be just that? Pavel maintains a pg implémentation of it, I believe.

Regards,
--
dim

2010/8/12 Dimitri Fontaine <dfontaine@hi-media.com>:

I definitely agree that PL/pgsql could be more usable.  Or if not,
then some other PL with a better overall design could be more usable.
I am not entirely sure how to create such a thing, however.

Would the standard plpsm be just that? Pavel maintains a pg implémentation of it, I believe.

there isn't nothing about iteration over collections :(

Regards

Pavel