Обсуждение: How may I keep prepended array items positive?
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
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