Thread: org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml

gajendra s v wrote
> Hi All,
>
>
> I have added one column with xml type ,after adding I am getting following
> error.
>
>
> org.postgresql.util.PSQLException: ERROR: could not identify an equality
> operator for type xml
>
> If I have removed column following query works fine
>
> select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID
> from
>  (select  OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from  KM_REL_OBJ_PER_ACTION
> where OBJ_TYPE='COURSETYPE') g where  PERFORMER_TYPE='GROUP' and
> PERFORMER_ID in ((WITH RECURSIVE parents as ( select PARENT_ID from
> KM_REL_SELF_GROUP where CHILD_ID in ( SELECT GROUP_ID FROM KM_REL_GRP_USER
> WHERE USER_ID=46467) UNION select a.PARENT_ID FROM KM_REL_SELF_GROUP a
> ,parents p where a.CHILD_ID = p.PARENT_ID ) select PARENT_ID from  parents
> order by PARENT_ID asc)union SELECT GROUP_ID FROM KM_REL_GRP_USER WHERE
> USER_ID=46467)) union select * from KM_COURSE_MAST where
> CREATED_BY='46467') KM_COURSE_MAST   order by DISPLAYORDER
>
> Thanks,
> Gajendra

XML has no concept of equality though I do not see any obvious use of an XML
column in the supplied query.  Is there maybe a view involved you are not
telling us about?  Are you positive it's this query that has the problem?
Your report is short on specifics - like the name of the XML column you
added and on what table, for instance.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/org-postgresql-util-PSQLException-ERROR-could-not-identify-an-equality-operator-for-type-xml-tp5779042p5779044.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

gajendra s v <svgajendra@gmail.com> wrote:

> I have added one column with xml type ,after adding I am getting
> following error.
>
> org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type xml

The UNION operation requires comparing all columns in the result
record to remove duplicates.  To identify duplicates, there must be
a notion of equality defined (getting technical here) by a default
btree opclass for the type.  XML does not have one, so UNION of a
result set including an XML column is not allowed.

Do you need the duplicate removal?  If not, use UNION ALL.  If you
need that you may need to cast the XML to text for intermediate
steps and then cast back to XML at the outermost level.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner-5 wrote
> gajendra s v <

> svgajendra@

> > wrote:
>
>> I have added one column with xml type ,after adding I am getting
>> following error.
>>
>> org.postgresql.util.PSQLException: ERROR: could not identify an equality
>> operator for type xml
>
> The UNION operation requires comparing all columns in the result
> record to remove duplicates.  To identify duplicates, there must be
> a notion of equality defined (getting technical here) by a default
> btree opclass for the type.  XML does not have one, so UNION of a
> result set including an XML column is not allowed.
>
> Do you need the duplicate removal?  If not, use UNION ALL.  If you
> need that you may need to cast the XML to text for intermediate
> steps and then cast back to XML at the outermost level.

The other option is to only union the PK field(s) on KM_COURSE_MAST then as
a final outer query join that union-ed result back with KM_COURSE_MAST using
the PK and attach whatever additional columns your query desires.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/org-postgresql-util-PSQLException-ERROR-could-not-identify-an-equality-operator-for-type-xml-tp5779042p5779269.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.