Thread: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
A few minutes ago I got bitten by the following PostgreSQL (9.4) behavior
SELECT ARRAY[1] || NULL, array_append(ARRAY[1], NULL);
?column? | array_append
----------+--------------
{1} | {1,NULL}
I expected that array_append and || are equivalent in this case but
obviously they are not. Sure, this is not too surprising since "||" has
to guess which operation is appropriate. However, I would have highly
appreciated ARRAY[1,2] || NULL as an example in [Table 9-45]. Combined
with the example of NULL || ARRAY[1,2] the underlying principle becomes
clear to me.
Strings behave different, but maybe this is also a potential pitfall:
SELECT 'abc' || NULL, concat('abc', NULL);
?column? | concat
----------+--------
(NULL) | abc
Best,
Michael
[Table 9-45]:
<http://www.postgresql.org/docs/9.4/static/functions-array.html#ARRAY-OPERATORS-TABLE>
Michael Herold <quabla@hemio.de> writes:
> A few minutes ago I got bitten by the following PostgreSQL (9.4) behavior
> SELECT ARRAY[1] || NULL, array_append(ARRAY[1], NULL);
> ?column? | array_append
> ----------+--------------
> {1} | {1,NULL}
> I expected that array_append and || are equivalent in this case but
> obviously they are not.
Yeah, the first case turns out to be array_cat not array_append.
> Sure, this is not too surprising since "||" has
> to guess which operation is appropriate. However, I would have highly
> appreciated ARRAY[1,2] || NULL as an example in [Table 9-45].
Hm. I don't think there is really room for such an example in that table,
and certainly not room for a discursive discussion. What would make more
sense to me is to add a paragraph and example concerning this issue at the
bottom of section 8.15.4, right after array_append and array_cat are
introduced. Since there's already a pointer to 8.15 just below that
table, people would hopefully find the example from that vicinity as well.
Seem reasonable?
regards, tom lane
Hi, On 07/09/2015 04:00 PM, Tom Lane wrote: > Hm. I don't think there is really room for such an example in that table, > and certainly not room for a discursive discussion. What would make more > sense to me is to add a paragraph and example concerning this issue at the > bottom of section 8.15.4, right after array_append and array_cat are > introduced. Since there's already a pointer to 8.15 just below that > table, people would hopefully find the example from that vicinity as well. > > Seem reasonable? Sounds perfect. Best, Michael
I just got confused by another behavior of the || operator. I thought it
might be appropriate to report it here.
The docs say "the result retains the lower bound subscript of the
left-hand operand’s outer dimension" [1]. That's again not true for
corner cases.
Expected:
# SELECT '{0}'::int[] || '[15:16]={1,2}';
----------
{0,1,2}
Unexpected (lower bound is untouched):
# SELECT '{}'::int[] || '[15:16]={1,2}';
---------------
[15:16]={1,2}
Actually, I was looking for a way to reset the index lower bound of an
array to the default. I didn't found a solution documented anywhere, [2]
only works for known array sizes. So, it might also be worth stating
ARRAY(SELECT UNNEST(...)) as a solution?
[1]: <http://www.postgresql.org/docs/9.5/static/arrays.html>
[2]: <http://www.postgresql.org/message-id/40854D0B.6000005@cromwell.co.uk>
On Mon, Oct 26, 2015 at 11:04:40AM +0100, Michael Herold wrote:
> I just got confused by another behavior of the || operator. I
> thought it might be appropriate to report it here.
>
> The docs say "the result retains the lower bound subscript of the
> left-hand operand’s outer dimension" [1]. That's again not true for
> corner cases.
You didn't quote the entire sentence:
When two arrays with an equal number of dimensions are concatenated, the
-------------------------------------------------------------------
result retains the lower bound subscript of the left-hand operand's
outer dimension.
> Expected:
> # SELECT '{0}'::int[] || '[15:16]={1,2}';
> ----------
> {0,1,2}
>
> Unexpected (lower bound is untouched):
> # SELECT '{}'::int[] || '[15:16]={1,2}';
> ---------------
> [15:16]={1,2}
I would argue that '{}'::int[] is zero dimmensions, so there is no
documented behavior for this.
The C code is:
/*
* short circuit - if one input array is empty, and the other is not, we
* return the non-empty one as the result
*
* if both are empty, return the first one
*/
if (ndims1 == 0 && ndims2 > 0)
PG_RETURN_ARRAYTYPE_P(v2);
I doubt we want to change this.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
On Tue, Dec 1, 2015 at 01:22:25PM +0100, Michael Herold wrote:
> On 01/12/15 00:59, Bruce Momjian wrote:
> >I would argue that '{}'::int[] is zero dimmensions, so there is no
> >documented behavior for this.
>
> Thank you for your reply. Agree, I am writing to pgsql-docs because
> I think this should be fixed with proper documentation.
Right. I think the docs just don't explain what happens in the case you
showed.
> My actual concern is that it is totally unclear how to (re)set the
> lower bound of an array. At least I waisted an hour to figure that
> out.
Yes, I am not sure how to do that either.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
On 01/12/15 00:59, Bruce Momjian wrote:
> I would argue that '{}'::int[] is zero dimmensions, so there is no
> documented behavior for this.
Thank you for your reply. Agree, I am writing to pgsql-docs because I
think this should be fixed with proper documentation.
My actual concern is that it is totally unclear how to (re)set the lower
bound of an array. At least I waisted an our to figure that out.