pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedarray_lower /array_prepend doubt
12+ messages / 5 participants
[nested] [flat]
* array_lower /array_prepend doubt
@ 2004-01-21 14:00 Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 15:53 ` Re: array_lower /array_prepend doubt Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2004-01-21 14:00 UTC (permalink / raw)
To: pgsql-sql
Greetings!
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)
and
tradein_clients=# SELECT array_lower( ARRAY[0,1,2,3],1 );
+-------------+
| array_lower |
+-------------+
| 1 |
+-------------+
(1 row)
Time: 402.614 ms
Regds
Mallah.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-01-21 15:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 12+ messages in thread
From: Stephan Szabo @ 2004-01-21 15:53 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
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})
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-01-21 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-01-21 17:14 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql@postgresql.org, Joe Conway <mail@joeconway.com>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-21 18:03 ` Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Joe Conway @ 2004-01-21 18:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
@ 2004-01-21 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-01-21 18:11 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-21 18:36 ` Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Joe Conway @ 2004-01-21 18:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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 by GCC 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
@ 2004-01-21 18:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:47 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-01-21 18:41 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-21 18:47 ` Joe Conway <mail@joeconway.com>
2004-01-21 19:15 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Joe Conway @ 2004-01-21 18:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:47 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
@ 2004-01-21 19:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 15:10 ` Re: array_lower /array_prepend doubt Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-01-21 19:15 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:47 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 19:15 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-12 15:10 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-02-12 23:25 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Momjian @ 2004-02-12 15:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe Conway <mail@joeconway.com>; Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:47 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 19:15 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 15:10 ` Re: array_lower /array_prepend doubt Bruce Momjian <pgman@candle.pha.pa.us>
@ 2004-02-12 23:25 ` Joe Conway <mail@joeconway.com>
2004-02-12 23:29 ` Re: array_lower /array_prepend doubt Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Joe Conway @ 2004-02-12 23:25 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: array_lower /array_prepend doubt
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 17:14 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:47 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
2004-01-21 19:15 ` Re: array_lower /array_prepend doubt Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 15:10 ` Re: array_lower /array_prepend doubt Bruce Momjian <pgman@candle.pha.pa.us>
2004-02-12 23:25 ` Re: array_lower /array_prepend doubt Joe Conway <mail@joeconway.com>
@ 2004-02-12 23:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 2004-02-12 23:29 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-02-12 23:29 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-21 14:00 array_lower /array_prepend doubt Rajesh Kumar Mallah <mallah@trade-india.com>
2004-01-21 15:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-21 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:03 ` Joe Conway <mail@joeconway.com>
2004-01-21 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:36 ` Joe Conway <mail@joeconway.com>
2004-01-21 18:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-21 18:47 ` Joe Conway <mail@joeconway.com>
2004-01-21 19:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 15:10 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-02-12 23:25 ` Joe Conway <mail@joeconway.com>
2004-02-12 23:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox