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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general