How may I keep prepended array items positive?

On Mon, Oct 31, 2005 at 04:37:39PM -0800, Matthew Peter wrote:

I would need to prepend a couple array items BUT I
NEED them to be positive [1:13] instead of [-4:9] for
instance.

How may I keep prepended array items positive?

You could use array-to-array concatenation instead of prepending
(aka element-to-array concatenation):

test=> SELECT 99 || ARRAY[1, 2, 3]; -- unwanted results
?column?
------------------
[0:3]={99,1,2,3}
(1 row)

test=> SELECT ARRAY[99] || ARRAY[1, 2, 3]; -- desired results
?column?
------------
{99,1,2,3}
(1 row)

--
Michael Fuhr

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... 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..

--- Michael Fuhr <mike@fuhr.org> wrote:

On Mon, Oct 31, 2005 at 04:37:39PM -0800, Matthew
Peter wrote:

I would need to prepend a couple array items BUT I
NEED them to be positive [1:13] instead of [-4:9]

for

instance.

How may I keep prepended array items positive?

You could use array-to-array concatenation instead
of prepending
(aka element-to-array concatenation):

test=> SELECT 99 || ARRAY[1, 2, 3]; -- unwanted
results
?column?
------------------
[0:3]={99,1,2,3}
(1 row)

test=> SELECT ARRAY[99] || ARRAY[1, 2, 3]; --
desired results
?column?
------------
{99,1,2,3}
(1 row)

--
Michael Fuhr

__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

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