public inbox for [email protected]
help / color / mirror / Atom feedARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
7+ messages / 3 participants
[nested] [flat]
* ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
@ 2015-07-09 00:31 Michael Herold <[email protected]>
2015-07-09 14:00 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Herold @ 2015-07-09 00:31 UTC (permalink / raw)
To: pgsql-docs
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
@ 2015-07-09 14:00 ` Tom Lane <[email protected]>
2015-07-11 15:39 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-10-26 10:04 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2015-07-09 14:00 UTC (permalink / raw)
To: Michael Herold <[email protected]>; +Cc: pgsql-docs
Michael Herold <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-07-09 14:00 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Tom Lane <[email protected]>
@ 2015-07-11 15:39 ` Michael Herold <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Herold @ 2015-07-11 15:39 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-07-09 14:00 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Tom Lane <[email protected]>
@ 2015-10-26 10:04 ` Michael Herold <[email protected]>
2015-11-30 23:59 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Bruce Momjian <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Herold @ 2015-10-26 10:04 UTC (permalink / raw)
To: Tom Lane <[email protected]>; 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]. 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/[email protected];
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-07-09 14:00 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Tom Lane <[email protected]>
2015-10-26 10:04 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
@ 2015-11-30 23:59 ` Bruce Momjian <[email protected]>
2015-12-01 12:22 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2015-11-30 23:59 UTC (permalink / raw)
To: Michael Herold <[email protected]>; +Cc: Tom Lane <[email protected]>; 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 <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-07-09 14:00 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Tom Lane <[email protected]>
2015-10-26 10:04 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-11-30 23:59 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Bruce Momjian <[email protected]>
@ 2015-12-01 12:22 ` Michael Herold <[email protected]>
2015-12-01 13:23 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Bruce Momjian <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Herold @ 2015-12-01 12:22 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Tom Lane <[email protected]>; 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-07-09 14:00 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Tom Lane <[email protected]>
2015-10-26 10:04 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-11-30 23:59 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Bruce Momjian <[email protected]>
2015-12-01 12:22 ` Re: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
@ 2015-12-01 13:23 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2015-12-01 13:23 UTC (permalink / raw)
To: Michael Herold <[email protected]>; +Cc: Tom Lane <[email protected]>; 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 <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2015-12-01 13:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-09 00:31 ARRAY[1] || NULL <> array_append(ARRAY[1], NULL) Michael Herold <[email protected]>
2015-07-09 14:00 ` Tom Lane <[email protected]>
2015-07-11 15:39 ` Michael Herold <[email protected]>
2015-10-26 10:04 ` Michael Herold <[email protected]>
2015-11-30 23:59 ` Bruce Momjian <[email protected]>
2015-12-01 12:22 ` Michael Herold <[email protected]>
2015-12-01 13:23 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox