Re: How may I keep prepended array items positive?

On Mon, Oct 31, 2005 at 10:22:14PM -0800, Matthew Peter wrote:
> I want to use it like this...
>
> UPDATE SET _array = {1,2,3} || _array;
>
> Which if _array had {1} in it, I'd get something like
> [-2:1]{1,1,2,3} as the range...

You have the result backwards: concatenating {1,2,3} and {1} yields
{1,2,3,1}, not {1,1,2,3}.  And I don't see the bounds behaving the
way you describe with array-to-array concatenation:

CREATE TABLE foo (a integer[]);
INSERT INTO foo (a) VALUES ('{1}');
UPDATE foo SET a = '{1,2,3}'::integer[] || a;
SELECT a, array_lower(a, 1), array_upper(a, 1) FROM foo;
     a     | array_lower | array_upper
-----------+-------------+-------------
 {1,2,3,1} |           1 |           4
(1 row)

However, element-to-array concatenation does change the lower bound,
which is why I suggested using array-to-array instead of element-to-array:

UPDATE foo SET a = 99 || a;
SELECT a, array_lower(a, 1), array_upper(a, 1) FROM foo;
         a          | array_lower | array_upper
--------------------+-------------+-------------
 [0:4]={99,1,2,3,1} |           0 |           4
(1 row)

> I only want it to push the existing values to the right so I'd have
> [1:4]{1,1,2,3}
>
> I don't have a pgsql on this box to show output..

If the example above doesn't help then please post the actual
commands and output that show the problem.

--
Michael Fuhr