Re: "...integer[] references..." = error

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
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83