Thread: Unexpected array_remove results
Hi,
should not in example below array_remove return same results?
test1=# create temp table tmptest (trid text[]);
CREATE TABLE
test1=# insert into tmptest values(null::text[]);
INSERT 0 1
test1=# update tmptest set trid[2:4]='{b,NULL,d}';
UPDATE 1
test1=# select trid,array_remove(trid, NULL::text),array_lower(array_remove(trid, NULL::text), 1),array_upper(array_remove(trid, NULL::text), 1) from tmptest;
trid | array_remove | array_lower | array_upper
------------------+--------------+-------------+-------------
[2:4]={b,NULL,d} | [2:3]={b,d} | 2 | 3
(1 row)
test1=# update tmptest set trid='{NULL,b,NULL,d}';
UPDATE 1
test1=# select trid,array_remove(trid, NULL::text),array_lower(array_remove(trid, NULL::text), 1),array_upper(array_remove(trid, NULL::text), 1) from tmptest;
trid | array_remove | array_lower | array_upper
-----------------+--------------+-------------+-------------
{NULL,b,NULL,d} | {b,d} | 1 | 2
(1 row)
I expected that in both results values will start from index 1.should not in example below array_remove return same results?
test1=# create temp table tmptest (trid text[]);
CREATE TABLE
test1=# insert into tmptest values(null::text[]);
INSERT 0 1
test1=# update tmptest set trid[2:4]='{b,NULL,d}';
UPDATE 1
test1=# select trid,array_remove(trid, NULL::text),array_lower(array_remove(trid, NULL::text), 1),array_upper(array_remove(trid, NULL::text), 1) from tmptest;
trid | array_remove | array_lower | array_upper
------------------+--------------+-------------+-------------
[2:4]={b,NULL,d} | [2:3]={b,d} | 2 | 3
(1 row)
test1=# update tmptest set trid='{NULL,b,NULL,d}';
UPDATE 1
test1=# select trid,array_remove(trid, NULL::text),array_lower(array_remove(trid, NULL::text), 1),array_upper(array_remove(trid, NULL::text), 1) from tmptest;
trid | array_remove | array_lower | array_upper
-----------------+--------------+-------------+-------------
{NULL,b,NULL,d} | {b,d} | 1 | 2
(1 row)
Matija Lesar <matija.lesar@gmail.com> writes:
> should not in example below array_remove return same results?
AFAICS, array_remove keeps the existing lower bound number. I don't
see anything particularly wrong with that definition.
Even if we didn't care about backwards compatibility, it would require
nontrivial effort to change it --- for example, there are several
early-exit cases that return the original array unmodified, and that would
be wrong if we were to adopt some other definition such as "force the
lower bound to 1".
regards, tom lane
On 20 March 2015 at 14:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matija Lesar
AFAICS, array_remove keeps the existing lower bound number.
Thank you for explanation. This is not specified in http://www.postgresql.org/docs/current/static/functions-array.html#ARRAY-FUNCTIONS-TABLE so I was not sure.
Regards,