On Dec 30, 2011, at 21:53, John Poole <jlpool...@gmail.com> wrote: > I am trying to create a custom data type for phone numbers where > I have a primary phone number and then an array of additional > phone numbers qualified by certain types. > > Below is a set of SQL commands I used to set up my custom > types. I am unable to insert into the array field and wonder > if I have found a bug, or I'm just missing some basic technique. > > Here is the error I receive when trying to insert into the array field: > > ERROR: cannot cast type record[] to phonenumber_type > LINE 2: ...22222,'office'), (3333333,'cell'),(4444444,'eve')]::phonenum... > > Can someone provide me an example of how to insert one or more > records into the secondary array component of the type or provide > some light on creating a custom type that would hold two fields: > 1) a single type > 2) an array of types (this field may be null) > > > Thank you. > > John Poole > > ================================================= > Here is the SQL to recreate my attempt: > -- > -- Creating a custom data type and inserting/updating example > -- > -- create database demo_typestudy1; > > create type phoneattribute_type as ENUM > ('home','office','day','eve','mobile','fax'); > > > create type phonenumber_type as > (numbers int, > phone_type phoneattribute_type > ); > > create type contactphone_type as > (primarynumber phonenumber_type, > othernumbers phonenumber_type ARRAY > ); > > create table people > (id integer PRIMARY KEY, > username text NOT NULL UNIQUE, > phone contactphone_type > ); > > -- > -- create a record w/o phone > -- > insert into people > (id, username) > VALUES > (1,'bob'); > -- > -- add the custom data type: contactphone, just the primary and no other > -- we'll try adding array values later > -- > update people > set phone = ((1234567899,'home'),NULL) > where id = 1; > -- > -- also more qualified > -- > update people > set phone = ROW(ROW(1234567899,'home'),NULL) > where id = 1; > -- > -- most qualified (with casting) > -- > update people > set phone = CAST(ROW(CAST(ROW(1234567899,'home') as phonenumber_type),NULL) > as contactphone_type) > where id = 1; > -- > -- view it > -- > select phone > from people where id = 1; > -- > -- try adding to the array field > -- replace the primary and add additional phone numbers > -- This is where things go awry. > -- > update people > set phone = CAST(ROW(CAST(ROW(1111111,'home') as > phonenumber_type),ARRAY[(2222222,'office'), > (3333333,'cell'),(4444444,'eve')]::phonenumber_type) > as contactphone_type) > where id = 1; > >
... ,ARRAY[ ... ]::phonenumber_type[] You need to cast to an array of the type which is done by adding the trailing brackets to the type. Also, your CREATE TYPE syntax (the "othernumbers phonenumber_type ARRAY" part) is something I have not yet seen. Where did you see this? I would have expected it to read "othernumbers phonenumber_type[]"; without the word ARRAY. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql