Thread: update ARRAY of COMPOSITE TYPE of text

I did:

CREATE TYPE telephone AS (
area text,
number text,
ext text
);

Then:

CREATE TABLE directory (
id integer,
tel telephone,
faxes telephone[]
);

After some data has been entered, I tried:

UPDATE directory SET tel = ROW('11', '2222222', '333') WHERE id = 1;
UPDATE directory SET faxes[1] = ROW('11', '2222222', '333') WHERE id = 1;

both worked fine, but:

UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333'), ROW('44', '555', '666')] WHERE id = 1;

has failed: (You will need to rewrite or cast the expression) with arrow pointing to ARRAY.

Does anybody know how to overcome it?

Please note, I'm not seeking an alternative, as I can still do the following:

UPDATE directory SET faxes = '{(11\,2222222\,333),(44\,555\,666)}' WHERE id=1;

I would like, however, do the same with ARRAY/ROW.

W

Wojciech Skaba <wojciech.skaba@teleadreson.pl> writes:
> UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333'), ROW('44', '555', '666')] WHERE id = 1;
> has failed: (You will need to rewrite or cast the expression) with arrow pointing to ARRAY.

> Does anybody know how to overcome it?

Just like it says, cast the expression:

UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333'), ROW('44', '555', '666')]::telephone[] WHERE id = 1;

or if it's more convenient for your app, do it like this:

UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333')::telephone, ROW('44', '555', '666')::telephone] WHERE id
=1; 

I don't recall at the moment why we allow implicit casting from an
anonymous "record" type to a named composite type but not from record[]
to an array of named composite.  There might be a good reason for it,
or just a lack of round tuits.

            regards, tom lane