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

Because int[] and int are not comparable types (in this case meaning
that there is not an = operator between them) which is one of the
requirements (IIRC) for making a foreign key between two columns.

I could imagine that it might be reasonable to do all elements must
exist for array type -> base type, although I'd think it might get
wierd for multidimensional arrays, and without an intelligent index
on that column the reverse checks for update/delete on the pk table
would probably be rather painful.

In general the workaround is to normalize with a details table.