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

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.

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...

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants