[SQL] delete item[5] from varchar[] array???
How is it possible to delete an item from a single
dimension varchar[] array? Lets say it has the values
{1,2,3,4,5,6}... how do i delete at position [4]?
Could this be done in a SQL statement or a function? I
appreciate any help. Thank you
MP
__
Yahoo! for Good
Donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] delete item[5] from varchar[] array???
Ya. I didn't see any in the \df. If it's so simple could you show me one? PLEEEASE Thanks MP --- Tom Lane <[EMAIL PROTECTED]> wrote: > Matthew Peter <[EMAIL PROTECTED]> writes: > > How is it possible to delete an item from a single > > dimension varchar[] array? > > AFAIR there is no built-in function for this, but it > seems like you > could write a generic polymorphic function for it > easily enough. > > regards, tom lane > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] delete item[5] from varchar[] array???
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 21, 2005 at 06:56:36PM -0700, Matthew
> Peter wrote:
> > How is it possible to delete an item from a single
> > dimension varchar[] array? Lets say it has the
> values
> > {1,2,3,4,5,6}... how do i delete at position [4]?
>
> I'm not sure if there's a better way, but you could
> concatenate the
> slices adjacent to the position you want to delete:
>
> test=> SELECT a FROM foo;
>a
> ---
> {1,2,3,4,5,6}
> (1 row)
>
> test=> UPDATE foo SET a = a[1:3] || a[5:6];
> UPDATE 1
> test=> SELECT a FROM foo;
> a
> -
> {1,2,3,5,6}
> (1 row)
>
> --
> Michael Fuhr
>
I sure hope there is a better way :) There must be an
easy, native way to interface with arrays. We all know
programming languages have ways to delete items in an
array natively, it just needs to be exposed.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] delete item[5] from varchar[] array???
--- Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Fri, Sep 23, 2005 at 10:02:44AM -0700, Matthew > Peter wrote: > > --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > > > test=> UPDATE foo SET a = a[1:3] || a[5:6]; > > > > I sure hope there is a better way :) There must be > an > > easy, native way to interface with arrays. > > Hoping a thing is true doesn't mean it must be true > :-) > > > We all know programming languages have ways to > delete items in an > > array natively, it just needs to be exposed. > > No, we don't all know that because it isn't > generally true, or else > you're using a loose definition of "natively." > Languages differ > in what capabilities they offer "natively." > > contrib/intarray implements an intarray_del_elem() > function, > presumably because the core product doesn't offer > that capability, > or at least didn't when the module was written. If > you think it's > there just waiting to be exposed, then dig into the > source code and > expose it :-) > > -- > Michael Fuhr > Yes. I should've said "most" and some thanks to libs. I'm busy in other source which is why I hoped those who were already engulfed in pg's source would have a solution... Thought this functionality may be around... somewhere which is why I asked. Took a brief look at intarray_del_elem() function you mentioned. This may be perfect, and then some. I'll play around with it. Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] problems with array
Not sure if you got this figured out but I think SELECT * from tb_cat WHERE id IN (SELECT array_to_string(cat,',') as cat FROM tb_array WHERE id=1); is what your looking for? --- paperinik 100 <[EMAIL PROTECTED]> wrote: > PostgreSQL is 7.4.7. > > My first table > CREATE TABLE tb_cat ( > id INTEGER, > desc text > ); > INSERT INTO tb_cat VALUES (10, 'cat10'); > INSERT INTO tb_cat VALUES (20, 'cat20'); > INSERT INTO tb_cat VALUES (30, 'cat30'); > > My second table > CREATE TABLE tb_array( > id INTEGER, > cat INTEGER[] > ); > INSERT INTO tb_array VALUES(1, ARRAY [10, 20]); > > When I write my select > SELECT * from tb_cat WHERE id IN (SELECT cat FROM > tb_array WHERE id=1); > the output is: > ERROR: operator does not exist: integer = integer[] > HINT: No operator matches the given name and > argument type(s). You may need > to add explicit type casts. > > Can anyone help me? > thanks. > > > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problems with array
Ya, I didn't test it. The error message was expecting an integer not an array, so coverting it to a list crossed my mind 'assuming' the subselect 'could' return a string of integers for the IN clause. Oh well. I'm glad there's people like you test it. --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > I doubt it, considering that it doesn't work :-( __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] update question
I need a throw away value for an insert statement... example...update table set value = 1, value = 2, throw_away_value -- so i don't break the query where id = 1; Thanks Yahoo! Personals Let fate take it's course directly to your email. See who's waiting for you Yahoo! Personals
Re: [SQL] update question
it's in a loop so there's an extra comma at the end so i was thinking i could put in a throw away value to keep the update from breaking if there's an additional commaJaime Casanova <[EMAIL PROTECTED]> wrote: On 12/5/05, Matthew Peter wrote:> I need a throw away value for an insert statement... example...>> update table> set value = 1, value = 2, throw_away_value -- so i don't break the query> where id = 1;>> Thanks>What do you mean by "throw away value"?are you trying to update and if the row doesn't exist then insert? ifthat is you can do a function in plpgsql and use exceptions forthat... there is an example in the manuals--regards,Jaime Casanova(DBA: DataBase Aniquilator ;)---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org Yahoo! Personals Single? There's someone we'd like you to meet. Lots of someones, actually. Yahoo! Personals
Re: [SQL] update question
Like WHERE 1 = 1, but in UPDATE table SET value = 1, 1 = 1; Yahoo! Personals Single? There's someone we'd like you to meet. Lots of someones, actually. Try Yahoo! Personals
