Re: Comparing arrays of composite types

On Fri, Aug 21, 2009 at 2:59 PM, Alban
Hertroys<dalroi(at)solfertje(dot)student(dot)utwente(dot)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