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>
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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]<http://www.postgresql.org/docs/9.5/static/arrays.html>. 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]</messages/by-id/40854D0B.6000005@cromwell.co.uk>
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]: </messages/by-id/40854D0B.6000005@cromwell.co.uk>
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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 +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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.
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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 +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs