Re: list manipulation at column level - Mailing list pgsql-general

From Matthew Peter
Subject Re: list manipulation at column level
Date
Msg-id 20050912020038.58222.qmail@web35209.mail.mud.yahoo.com
Whole thread Raw
In response to Re: list manipulation at column level  (Michael Fuhr <mike@fuhr.org>)
List pgsql-general
I was reading the concatenation operator earlier.
Postgresql is very neat.

What about deleting an item in the array without
knowing it's position? For example, deleting the item
"12" in the array? Is that possible? Like using

UPDATE array_delete_at(array_find(a,'12'));

where "a" is the column and "12" is the value to find
or would a select be in order to find the position?

from the docs
-----
SELECT * FROM sal_emp WHERE 10000 = ALL
(pay_by_quarter);

    Tip: Arrays are not sets; searching for specific
array elements may be a sign of database misdesign.
Consider using a separate table with a row for each
item that would be an array element. This will be
easier to search, and is likely to scale up better to
large numbers of elements.
-----

Lastly, what's considered a large number of elements?



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

> On Sun, Sep 11, 2005 at 04:02:24PM -0700, Chris
> Travers wrote:
> > Matthew Peter wrote:
> > >Is it possible to append and delete (unknown
> location)
> > >items in a list stored in a column? For instance,
> > >
> > >a column with 'some,values,in,a,list,12,34';
> > >
> > >Could I [ap|pre]pend and or delete items in this
> list
> > >through pgsql?
> > >
> > prepend:
> > 'value' || ',' || column
> > append
> > column || ',' ||'value'
>
> Or use an array type and perform array operations.
>
>
http://www.postgresql.org/docs/8.0/interactive/arrays.html
>
http://www.postgresql.org/docs/8.0/interactive/functions-array.html
>
> CREATE TABLE foo (a text[]);
> INSERT INTO foo VALUES
> ('{some,values,in,a,list,12,34}');
>
> SELECT array_prepend('foo', a) FROM foo;
>               array_prepend
> -----------------------------------------
>  [0:7]={foo,some,values,in,a,list,12,34}
> (1 row)
>
> SELECT array_append(a, 'foo') FROM foo;
>            array_append
> -----------------------------------
>  {some,values,in,a,list,12,34,foo}
> (1 row)
>
> SELECT array_cat(a[1:2], a[6:7]) FROM foo;
>       array_cat
> ---------------------
>  {some,values,12,34}
> (1 row)
>
> --
> Michael Fuhr
>





______________________________________________________
Yahoo! for Good
Watch the Hurricane Katrina Shelter From The Storm concert
http://advision.webevents.yahoo.com/shelter


pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: list manipulation at column level
Next
From: "Thomas F. O'Connell"
Date:
Subject: Re: pg_autovacuum not doing anything