Thread: arrays and insert/select

I'm loading a table from another (in 7.3.2) and need to build a
varchar array from a varchar column in the source table.

   INSERT into X (ArrayCol) select oldval from Y;

gets a type error, but you can't cast to an array.

    INSERT into X (ArrayCol[1]) select oldval from Y;

doesn't work either.

    INSERT into X (ArrayCol) select '{"' || oldval || '"}' from Y;

also doesn't work.

There doesn't appear to be a to_array function.

Any suggestions?
--
Mike Nolan

nolan@celery.tssi.com wrote:
> I'm loading a table from another (in 7.3.2) and need to build a
> varchar array from a varchar column in the source table.
>

[...snip...]

>
> There doesn't appear to be a to_array function.
>

In 7.4 you'll have other options, but in 7.3.x the only way I can think
of to do this is like this (example is int/int[], but I think you'll get
the picture):

create table a (f int);
insert into a values(1);
create table b (f int[]);
create or replace function singleton_array(int) returns int[] as '
declare
  v_arr text;
begin
  v_arr := ''{'' || $1 || ''}'';
  return v_arr;
end;
' language 'plpgsql';
insert into b (f) select singleton_array(f) from a;
test=# select * from b;
   f
-----
  {1}
(1 row)

HTH,

Joe