ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

Started by Michael Heroldalmost 11 years ago7 messagesdocs
Jump to latest
#1Michael Herold
quabla@hemio.de

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&gt;

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Michael Herold (#1)
Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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

#3Michael Herold
quabla@hemio.de
In reply to: Tom Lane (#2)
Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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

#4Michael Herold
quabla@hemio.de
In reply to: Tom Lane (#2)
Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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&gt;. 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&gt;
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&gt;
[2]: </messages/by-id/40854D0B.6000005@cromwell.co.uk&gt;

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

#5Bruce Momjian
bruce@momjian.us
In reply to: Michael Herold (#4)
Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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

#6Michael Herold
quabla@hemio.de
In reply to: Bruce Momjian (#5)
Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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

#7Bruce Momjian
bruce@momjian.us
In reply to: Michael Herold (#6)
Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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