Re: list manipulation at column level

From: Matthew Peter <survivedsushi(at)yahoo(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>, Chris Travers <chris(at)travelamericas(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: list manipulation at column level
Date: 2005-09-12 02:00:38
Message-ID: 20050912020038.58222.qmail@web35209.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)fuhr(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Thomas F. O'Connell 2005-09-12 05:38:40 Re: pg_autovacuum not doing anything
Previous Message Michael Fuhr 2005-09-11 23:25:01 Re: list manipulation at column level