Re: "...integer[] references..." = error - Mailing list pgsql-general

On Friday, September 6, 2002, at 12:08 , Oleg Bartunov wrote:

> On Thu, 5 Sep 2002, Nigel J. Andrews wrote:
>
>> On Thu, 5 Sep 2002, Joel Rodrigues wrote:
>>
>>> Hello,
>>>
>>> Does anyone know why trying to create this column:
>>>
>>> "role    INTEGER[] REFERENCES role (roleid)"
>>>
>>>
>>> returns this error:
>>>
>>>
>>> ERROR:  Unable to identify an operator '=' for types 'integer[]'
>>> and 'integer'
>>>          You will have to retype this query using an explicit cast
>>>
>>> psql:individual.sql:22: ERROR:  Unable to identify an
>>> operator '=' for types 'integer[]' and 'integer'
>>>          You will have to retype this query using an explicit cast
>>>
>>
>> Let me guess, roleid is a plain integer?
>>
>>>
>>> If I remove the array "[]", it works fine, but I need the array
>>> to refer to more than one "roleid".
>>
>> Sounds like it.
>>
>> Well I believe there is something in contrib, intarr may be,
>> that provides a
>> set of operators for integer arrays. If so I think I also saw
>> mention on the
>> list that it's unstable in 7.3beta.
>
> contrib/intarray is your friend.
> it's broken (in 7.3 beta1) due to some changes in main source tree.
> We hope we'll submit a fix next week. But 7.2.X are solid in used for
> long time in many project.
>
>>
>> However, I'm thinking you've got an entirely different problem
>> since you don't
>> need an equality operator defined you need something entirely
>> different that
>> defined like an int[]/int equality operator but knows to check
>> each element
>> against the referenced column. Best solution would seem to be a custom
>> function. As there obviously isn't such an equality operator
>> already defined
>> won't cause problems elsewhere, however, it would probably have to be
>> specialised to your exact situation, although...
>
> He needs contains operator.  Something like
> select message.mid from message where message.sections @ '{1,2}';
>
>     Regards,
>         Oleg


Thanks for the info & hints guys. Sad really that the most
obvious construct does not work. So, though I can get away with
skipping the REFERENCES bit by using a VIEW.  I'd still like to
have some sort of referential integrity checking. I'm puzzled
about how to achieve this. I thought I'd do a CHECK with a
subquery expression, but, "Currently, CHECK expressions cannot
contain subselects". And it's not even on the TODO list. Foiled
again !


A bit of searching on Google Groups reveals that at least a few
people have attempted to use "...integer[] references...". Hate
to use the "o" word again, but it is really such an obvious
construct both in it's conception and (optimistic)
implementation.

I'll do a bit more thinking now. Any hints welcome.

- Joel