Thread: error caused by FOREIGN KEY on composite type

I have the following definitions:

--------------------------------------

create type mytype as (x integer, y integer);

create table foo(
    a mytype primary key,
    b integer
);

create table bar(
    a mytype references foo
);

insert into foo values((0,0)::mytype,0);

--------------------------------------

When I try to do a simple update on foo, I get an error:

test=> update foo set b=1;
ERROR:  no conversion function from mytype to record

Can someone explain what does this error mean?

silly8888 <silly8888@gmail.com> writes:
> create type mytype as (x integer, y integer);

> create table foo(
>     a mytype primary key,
>     b integer
> );

> create table bar(
>     a mytype references foo
> );

While that probably ought to work, is there a really good reason that
you're not doing this with a conventional two-column primary key and
foreign key?  The composite type is going to be exceedingly inefficient,
not to mention not portable to other DBMSes.

            regards, tom lane

On Wed, Nov 4, 2009 at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> silly8888 <silly8888@gmail.com> writes:
>> create type mytype as (x integer, y integer);
>
>> create table foo(
>>     a mytype primary key,
>>     b integer
>> );
>
>> create table bar(
>>     a mytype references foo
>> );
>
> While that probably ought to work, is there a really good reason that
> you're not doing this with a conventional two-column primary key and
> foreign key?  The composite type is going to be exceedingly inefficient,
> not to mention not portable to other DBMSes.
>
>                        regards, tom lane
>

You are right, the two-column solution is probably better. The only
reason I posted here was to see if I hit a bug (and it seems that I
might have).

BTW the composite might offer some small benefits in this case, namely
when combined with user defined DOMAINs it can simplify CHECK
constraints a lot.