Thread: array_lower /array_prepend doubt
Greetings!
can anyone explain whySELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
returns 0 not 1
because
tradein_clients=# SELECT array_prepend(0, ARRAY[1,2,3]);
+---------------+
| array_prepend |
+---------------+
| {0,1,2,3} |
+---------------+
(1 row)
and
tradein_clients=# SELECT array_lower( ARRAY[0,1,2,3],1 );
+-------------+
| array_lower |
+-------------+
| 1 |
+-------------+
(1 row)
Time: 402.614 ms
Regds
Mallah.
On Wed, 21 Jan 2004, Rajesh Kumar Mallah wrote:
> can anyone explain why
> SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
> returns 0 not 1
>
> because
>
> tradein_clients=# SELECT array_prepend(0, ARRAY[1,2,3]);
> +---------------+
> | array_prepend |
> +---------------+
> | {0,1,2,3} |
> +---------------+
> (1 row)
It looks like array_prepend is basically inserting the new value at the
index before the start of the array, so 0 is the lower bound. This means
that array_prepend(0,ARRAY[1,2,3]) is not the same array as
ARRAY[0,1,2,3]. If you stick both in a table, and select col[1], in one
you appear to get back 1, in the other 0. However, I think there is a bug
here somewhere, because the former array does not appear to dump/restore
as the same value (both seem to dump as (0,1,2,3})
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> can anyone explain why
> SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
> returns 0 not 1
Because array_prepend keeps the subscripts of the existing array
elements the same. This was discussed during development of the
code, but I don't see anything in the documentation that mentions it.
regards, tom lane
Tom Lane wrote: > Rajesh Kumar Mallah <mallah@trade-india.com> writes: >>can anyone explain why >> SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1); >>returns 0 not 1 > > Because array_prepend keeps the subscripts of the existing array > elements the same. This was discussed during development of the > code, but I don't see anything in the documentation that mentions it. > It could perhaps be added to the "Functions and Operators" page for arrays, but it is mentioned here: http://www.postgresql.org/docs/current/static/arrays.html#AEN5183 "When a single element is pushed on to the beginning of a one-dimensional array, the result is an array with a lower bound subscript equal to the right-hand operand's lower bound subscript, minus one. When a single element is pushed on to the end of a one-dimensional array, the result is an array retaining the lower bound of the left-hand operand. For example: SELECT array_dims(1 || ARRAY[2,3]); array_dims ------------ [0:2] (1 row) SELECT array_dims(ARRAY[1,2] || 3); array_dims ------------ [1:3] (1 row) " Joe
Joe Conway <mail@joeconway.com> writes: > Tom Lane wrote: >> Because array_prepend keeps the subscripts of the existing array >> elements the same. This was discussed during development of the >> code, but I don't see anything in the documentation that mentions it. > It could perhaps be added to the "Functions and Operators" page for > arrays, but it is mentioned here: > http://www.postgresql.org/docs/current/static/arrays.html#AEN5183 Ah. I was looking at the mentions of array_prepend() ... but of course the manual also says that you should use the || operator instead ... so I guess this mention is sufficient. The other point about pg_dump failing to correctly restore arrays with nondefault lower bounds is a good one, though. We need to think about how to fix that. regards, tom lane
Joe Conway <mail@joeconway.com> writes:
> I'll put some thought into it, but note that it is hardly a new issue --
Of course; I suppose this bug goes back to Berkeley days. We just
hadn't recognized it before (or at least I hadn't).
regards, tom lane
Tom Lane wrote:
> The other point about pg_dump failing to correctly restore arrays with
> nondefault lower bounds is a good one, though. We need to think about
> how to fix that.
I'll put some thought into it, but note that it is hardly a new issue --
it's been possible to create an array with < 1 lower bound since well
before 7.4:
regression=# select version(); version
----------------------------------------------------------------------- PostgreSQL 7.3.5 on i686-pc-linux-gnu, compiled
byGCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
(1 row)
regression=# create table a(f1 int, f2 int[]);
CREATE TABLE
regression=# insert into a values (1,'{1,2}');
INSERT 565511 1
regression=# update a set f2[0] = 0 where f1 = 1;
UPDATE 1
regression=# select array_dims(f2) from a; array_dims
------------ [0:2]
(1 row)
Joe
Tom Lane wrote: > Of course; I suppose this bug goes back to Berkeley days. We just > hadn't recognized it before (or at least I hadn't). > Neither had I. But the changes in 7.4 probably make it more likely people will bump into this as a problem. Without looking to confirm, I believe SQL99 defines an array as always having a lower bound of 1, making our behavior an extension to the standard. We may need another extension to the array literal syntax in order to deal with this. I'll report back after I've had some time to study it. Joe
Joe Conway <mail@joeconway.com> writes:
> We may need another extension to the array literal syntax in
> order to deal with this. I'll report back after I've had some time to
> study it.
There already is support in array_in for specification of the array
dimensions (though it may be suffering bit rot for lack of use/testing).
I think the main thing needed is some thought about when array_out
should print dimensions; we don't want it doing so all the time, for
both clutter and backwards compatibility reasons. Maybe "whenever any
lower bound is not 1" would do; or maybe we want to invent a GUC switch
to control its behavior.
regards, tom lane
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: > > We may need another extension to the array literal syntax in > > order to deal with this. I'll report back after I've had some time to > > study it. > > There already is support in array_in for specification of the array > dimensions (though it may be suffering bit rot for lack of use/testing). > I think the main thing needed is some thought about when array_out > should print dimensions; we don't want it doing so all the time, for > both clutter and backwards compatibility reasons. Maybe "whenever any > lower bound is not 1" would do; or maybe we want to invent a GUC switch > to control its behavior. Is this a TODO? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Joe Conway wrote: > Bruce Momjian wrote: > > Is this a TODO? > > Probably -- something like: > Modify array literal representation to handle array index lower bound > of other than one Added to TODO. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Is this a TODO? Probably -- something like: Modify array literal representation to handle array index lower bound of other than one Joe