Re: Converting value to array

On Sat, Feb 05, 2005 at 11:12:09AM -0500, Sean Davis wrote:
>
> I have a column in my table block_sizes(varchar) that looks like:
>
> 12,23,
> 234,23,
> 78,64,28,
>
> i.e., comma-separated integer values (and the included trailing comma).
> I would like to convert these each to an array and store in another
> column.

Have you tried string_to_array()?

http://www.postgresql.org/docs/8.0/static/functions-array.html

CREATE TABLE foo (
    strcol    text,
    arraycol  integer[]
);

INSERT INTO foo (strcol) VALUES ('12,23,');
INSERT INTO foo (strcol) VALUES ('234,23,');
INSERT INTO foo (strcol) VALUES ('78,64,28,');

UPDATE foo SET arraycol = string_to_array(rtrim(strcol, ','), ',')::integer[];

SELECT * FROM foo;
  strcol   |  arraycol
-----------+------------
 12,23,    | {12,23}
 234,23,   | {234,23}
 78,64,28, | {78,64,28}
(3 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/