Postgres Pro
Downloads
Home   >   mailing lists

Thread: How may I keep prepended array items positive?

How may I keep prepended array items positive?

From
Matthew Peter
Date:
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?




__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

Re: How may I keep prepended array items positive?

From
Michael Fuhr
Date:
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

Re: How may I keep prepended array items positive?

From
Matthew Peter
Date:
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

Re: How may I keep prepended array items positive?

From
Michael Fuhr
Date:
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