pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested custom types: array - unable to insert
3+ messages / 3 participants
[nested] [flat]
* Nested custom types: array - unable to insert
@ 2011-12-31 02:53 John Poole <jlpoole56@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: John Poole @ 2011-12-31 02:53 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Nested custom types: array - unable to insert
@ 2011-12-31 03:31 David Johnston <polobo@yahoo.com>
parent: John Poole <jlpoole56@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Johnston @ 2011-12-31 03:31 UTC (permalink / raw)
To: John Poole <jlpoole56@gmail.com>; +Cc: pgsql-sql
On Dec 30, 2011, at 21:53, John Poole <jlpoole56@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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Nested custom types: array - unable to insert [SOLVED]
@ 2011-12-31 03:47 John L. Poole <jlpoole56@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: John L. Poole @ 2011-12-31 03:47 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql
On 12/30/2011 7:31 PM, David Johnston wrote:
> On Dec 30, 2011, at 21:53, John Poole<jlpoole56@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.
David et al.,
That works.
demo_typestudy1=# update people
demo_typestudy1-# set phone = CAST(ROW(CAST(ROW(1111111,'home') as
phonenumber_type),ARRAY[(2222222,'office'),
(3333333,'mobile'),(4444444,'eve')]::phonenumber_type[])
demo_typestudy1(# as contactphone_type)
demo_typestudy1-# where id = 1;
UPDATE 1
demo_typestudy1=#
To answer your question, I did originally try
"othernumbers phonenumber_type[]"
but as I kept running into a wall, I figured I had not tried
a correct syntax and somewhere found the "ARRAY" syntax
as an acceptable form; I figured the more explicit, the better.
It may have been from a railroad diagram.
The type definition was accepted so I ran with it.
Thank you, I spent several hours gated by this issue, so your
insight has been a tremendous help.
John
Attachments:
[text/x-vcard] jlpoole56.vcf (162B, ../../4EFE85E9.9020602@gmail.com/2-jlpoole56.vcf)
download | inline:
begin:vcard
fn:John L. Poole
n:Poole;John
email;internet:jlpoole56@gmail.com
tel;work:707-812-1323
tel;home:707-812-1323
x-mozilla-html:TRUE
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-12-31 03:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-31 02:53 Nested custom types: array - unable to insert John Poole <jlpoole56@gmail.com>
2011-12-31 03:31 ` David Johnston <polobo@yahoo.com>
2011-12-31 03:47 ` Re: Nested custom types: array - unable to insert [SOLVED] John L. Poole <jlpoole56@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox