update ARRAY of COMPOSITE TYPE of text

From: Wojciech Skaba <wojciech(dot)skaba(at)teleadreson(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: update ARRAY of COMPOSITE TYPE of text
Date: 2013-05-23 09:40:37
Message-ID: 519DE415.2070207@teleadreson.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neeraj Rai 2013-05-23 11:53:44 - pgaql long value corrupted using htons
Previous Message Heikki Linnakangas 2013-05-23 09:38:43 Re: [pgeu-general] Replication failover