Thread: Comparing arrays of composite types
Hello all,
I'm running into a small problem (while comparing tokenised unit
strings in case you're interested) with said topic.
I defined a type:
CREATE TYPE unit_token AS (
base_unit TEXT,
unit_base INT
);
In my table I have:
CREATE TABLE unit (
unit TEXT NOT NULL PRIMARY KEY,
tokens unit_token[] NOT NULL
);
If I try to join on tokens or try to create an index over that column
I get: "ERROR: could not identify a comparison function for type
unit_token".
I can compare columns of type unit_token (not the array) just fine,
and even joining on tokens[1] works as expected - and not just for
equality but also less-than (didn't test the remainder, I assume they
work).
I have created operators on unit_token for =, <, <=, > and >=, but
either I did something wrong defining my operators or the error is
pointing to some other problem.
I noticed casting both sides to text works just fine, but I'd prefer
to do this 'properly' if possible. Any pointers as to what is going
wrong here?
One other thing of note is that the error doesn't appear to be about a
missing comparison operator for unit_token, but rather for unit_token[].
Alban Hertroys
--
Screwing up is the correct approach to attaching something to the
ceiling.
!DSPAM:737,4a8eee7b10131407718702!
On Fri, Aug 21, 2009 at 2:59 PM, Alban Hertroys<dalroi@solfertje.student.utwente.nl> wrote: > Hello all, > > I'm running into a small problem (while comparing tokenised unit strings in > case you're interested) with said topic. > > I defined a type: > CREATE TYPE unit_token AS ( > base_unit TEXT, > unit_base INT > ); > > In my table I have: > CREATE TABLE unit ( > unit TEXT NOT NULL PRIMARY KEY, > tokens unit_token[] NOT NULL > ); > > If I try to join on tokens or try to create an index over that column I get: > "ERROR: could not identify a comparison function for type unit_token". > > I can compare columns of type unit_token (not the array) just fine, and even > joining on tokens[1] works as expected - and not just for equality but also > less-than (didn't test the remainder, I assume they work). > > I have created operators on unit_token for =, <, <=, > and >=, but either I > did something wrong defining my operators or the error is pointing to some > other problem. > > I noticed casting both sides to text works just fine, but I'd prefer to do > this 'properly' if possible. Any pointers as to what is going wrong here? > > One other thing of note is that the error doesn't appear to be about a > missing comparison operator for unit_token, but rather for unit_token[]. This works in 8.4. Can you upgrade? postgres=# select array(select foo from foo) = array(select foo from foo) ; ?column? ---------- t (1 row) merlin
Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
> I defined a type:
> CREATE TYPE unit_token AS (
> base_unit TEXT,
> unit_base INT
> );
> If I try to join on tokens or try to create an index over that column
> I get: "ERROR: could not identify a comparison function for type
> unit_token".
As noted, there is a built-in solution as of 8.4.
> I have created operators on unit_token for =, <, <=, > and >=, but
> either I did something wrong defining my operators or the error is
> pointing to some other problem.
The mere fact that the operator is named '=' means nothing to Postgres.
You need to create an operator class or family that shows the operator
is equality in a btree opclass. Array comparison looks for the default
btree opclass for the element data type to decide what to do.
regards, tom lane
On 21 Aug 2009, at 22:12, Tom Lane wrote: > Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes: >> I have created operators on unit_token for =, <, <=, > and >=, but >> either I did something wrong defining my operators or the error is >> pointing to some other problem. > > The mere fact that the operator is named '=' means nothing to > Postgres. > You need to create an operator class or family that shows the operator > is equality in a btree opclass. Array comparison looks for the > default > btree opclass for the element data type to decide what to do. I see, that's the kind of answer I was looking for, thanks. I didn't see any mention to this effect in the places I looked at in the fine manual (mostly type creation and operator related stuff), maybe there's some room for improvement there? No longer needed for 8.4 probably, but many people don't install .0- releases so I expect 8.3 will be commonly in use for a while yet (which is also the reason I'd prefer my unit conversion database to work in 8.3). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a8f1e7610131363520008!