Thread: 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/

"Sean Davis" <sdavis2@mail.nih.gov> writes:
> 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.

> '{' || rtrim(block_sizes,',') || '}'

That is one correct way to compute the external textual representation
of the array you want.  The trick is to get it fed to the array datatype
input converter.  The main SQL parser is pretty strongly typed and I'm
not sure there is any way to get it to make that conversion.  However
several of the PLs are pretty lax about cross-type conversions; in
particular you could do this in plpgsql.  Untested, but

    create function block_sizes_to_array(varchar) returns int[] as '
    declare x int[];
    begin
    x := ''{'' || rtrim($1, '','') || ''}'';
    return x;
    end' language plpgsql;

should work well enough.  plpgsql will observe that the text result it
got from the expression is not the right type to assign to the int[]
variable x, and will deal with this by converting to external textual
form (a no-op for the text side) and back again.

            regards, tom lane