Thread: "...integer[] references..." = error
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
If I remove the array "[]", it works fine, but I need the array
to refer to more than one "roleid".
TIA.
- Joel
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.
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
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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
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
On Fri, 6 Sep 2002, Joel Rodrigues wrote: > 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. Not really. There are some performance issues and such surrounding it. Arrays are positional and not multisets, so you get some wierdness at the conversion. For example: I have (3,4) in an array. I update it to (4). For foreign key purposes, is this effectively a delete of 3 (ie no check required) or a delete of 4 and a change of 3->4. If the array has 100 elements and I remove the first one, do I do 99 foreign key checks or do I try to determine that's all that happened. What if the rest of the elements were randomly assorted?
On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote: > > On Fri, 6 Sep 2002, Joel Rodrigues wrote: > >> 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. > > Not really. There are some performance issues and such surrounding > it. Arrays are positional and not multisets, so you get some > wierdness at the conversion. > > For example: > I have (3,4) in an array. I update it to (4). For foreign > key purposes, is this effectively a delete of 3 (ie no > check required) or a delete of 4 and a change of 3->4. > If the array has 100 elements and I remove the first one, > do I do 99 foreign key checks or do I try to determine > that's all that happened. What if the rest of the elements > were randomly assorted? Hello Stephan, I understand what you're saying. I didn't see that. Thanks. But would there be any problem with allowing subselects within CHECK expressions ? - Joel
On Sat, 7 Sep 2002, Joel Rodrigues wrote: > On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote: > > > Not really. There are some performance issues and such surrounding > > it. Arrays are positional and not multisets, so you get some > > wierdness at the conversion. > > > > For example: > > I have (3,4) in an array. I update it to (4). For foreign > > key purposes, is this effectively a delete of 3 (ie no > > check required) or a delete of 4 and a change of 3->4. > > If the array has 100 elements and I remove the first one, > > do I do 99 foreign key checks or do I try to determine > > that's all that happened. What if the rest of the elements > > were randomly assorted? > > Hello Stephan, I understand what you're saying. I didn't see > that. Thanks. But would there be any problem with allowing > subselects within CHECK expressions ? You might want to read past discussions. The big problem is that check constraints with subselects act as constraints on the tables mentioned in the subselects as well and we currently have no good way to manage that automatically. Imagine a check constraint like: create table table1 ( a int check ((select max(col1) from table2)>a); ); What can make that check constraint violated? Adding a new row to table1 with a>=max(col1), Updating table1 row to a>=max(col1), Deleting the max row from table2 such that a>=max(col1) Updating table2's max row such that a>=max(col1). The first two are easy, it's the second two that are wierd. Note that in this case inserting to table2 I don't believe can violate the constraint, but switching the > to a < would make delete safe and insert possibly violate. If you don't care about the second pair of constraints there, you can hide alot of the functionality in a trigger or function.
On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote: > > On Fri, 6 Sep 2002, Joel Rodrigues wrote: > >> 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. > > Not really. There are some performance issues and such surrounding > it. Arrays are positional and not multisets, so you get some > wierdness at the conversion. > > For example: > I have (3,4) in an array. I update it to (4). For foreign > key purposes, is this effectively a delete of 3 (ie no > check required) or a delete of 4 and a change of 3->4. > If the array has 100 elements and I remove the first one, > do I do 99 foreign key checks or do I try to determine > that's all that happened. What if the rest of the elements > were randomly assorted? > Forgot to comment: By "not really", you're referring to implementing the feature in PostgreSQL, right ? Because what I referred to by "obvious" is the need for this feature. And thinking about what you elaborate on in your example, it seems to me that some restricted use of such a construct (...integer[] references someID...) should be included in the feature set. And yes I think the best and most sensible solution would be to do in your example of a 100 element array, would be to do 99 foreign key checks. I don't see what the problem could be, other than a minuscule performance hit perhaps depending on the machine you're running on. By the same token, CHECK could be allowed some restricted use of subselects. But that item on the TODO list probably does deal with this issue - I'll ask hackers about that. Anyway, I'll mention this on pgsql-hackers, and shift to trying to find a fudge for achieving some kind of referential integerity/ constraint checking within a stock PostgreSQL 7.2.2 install, i.e. no contrib modules. Cheers, Joel
On Sun, 8 Sep 2002, Joel Rodrigues wrote:
> Forgot to comment:
>
> By "not really", you're referring to implementing the feature in
> PostgreSQL, right ? Because what I referred to by "obvious" is
> the need for this feature. And thinking about what you elaborate
> on in your example, it seems to me that some restricted use of
> such a construct (...integer[] references someID...) should be
> included in the feature set. And yes I think the best and most
> sensible solution would be to do in your example of a 100
> element array, would be to do 99 foreign key checks. I don't see
> what the problem could be, other than a minuscule performance
> hit perhaps depending on the machine you're running on.
Mostly on implementation, however there are some specification
level things as yet left unsaid dealing with arrays of arrays
and multi-column keys. I'm not sure whether
{{1,2},{3,4}} -> {2,1},{3,4} (two rows in the pk table,
one in the fk) satisfies the constraint or not. As arrays
not really, but since we're already treating an array
as a multiset here, do we do that on both sides? Also,
for multi-column foreign keys say a key like ({1,2},{3,4})
is that 2 combinations or 4? If 4 then the do all the checks
become problematic as it does a number of checks equal to all of
the array lengths (well, distinct values) multiplied together if there
were multiple arrays and doing the more intelligent check (or
deciding to do it for arrays over a certain length or something) could
lower that greatly. It's likely to be done, probably not for a few
versions as for many cases there is a workaround (using a details
table) and there are bugs or at least misfeatures to kill first.
> By the same token, CHECK could be allowed some restricted use of
> subselects. But that item on the TODO list probably does deal
> with this issue - I'll ask hackers about that.
For some subset, you can get the general effect using trigger
functions. You'd need to determine which actions on the tables
involved could violate and write the appropriate functions.