FW: operator is not unique: smallint[] @> smallint[] You might need to add explicit type casts (!) - Mailing list pgsql-general

> -----Original Message-----
> From: Pujol Mathieu [mailto:mathieu.pujol@realfusio.com]
> Sent: Dienstag, 15. Juli 2014 08:40
> To: Marc Mamin
> Subject: Re: [GENERAL] operator is not unique: smallint[] @>
> smallint[] You might need to add explicit type casts (!)
>
>
> Le 14/07/2014 13:32, Marc Mamin a écrit :
> > Hello,
> >
> > (Postgres 9.3.4)
> >
> > I don't know how to apply the tip provided along with the error
> > message :-)
> >
> > This issue may be related to the usage of intarray that seems to
> > have added an
> > _int4 @> _int4 operator to public:
> >
> >
> > set search_path=public;
> >
> > select '{1}'::int2[] @> '{1}'::int2[] operator is not unique:
> > smallint[] @> smallint[]
> >
> > but it works with int4[] and int8[]
> >
> >
> > The error does not ocure when ignoring public:
> >
> > set search_path=user;
> >
> > select '{1}'::int2[] @> '{1}'::int2[] true
> >
> >
> > --from  intarray (?)
> >
> > CREATE OPERATOR public.@>(
> >    PROCEDURE = _int_contains,
> >    LEFTARG = _int4,
> >    RIGHTARG = _int4,
> >    COMMUTATOR = <@,
> >    RESTRICT = contsel,
> >    JOIN = contjoinsel);
> >
> >
> > --from catalog
> >
> > CREATE OPERATOR @>(
> >    PROCEDURE = arraycontains,
> >    LEFTARG = anyarray,
> >    RIGHTARG = anyarray,
> >    COMMUTATOR = <@,
> >    RESTRICT = arraycontsel,
> >    JOIN = arraycontjoinsel);
> > COMMENT ON OPERATOR @>(anyarray, anyarray) IS 'contains';
> >
...
...
> >
> >
> Hi,
> When intarray extension is not loaded, problem does not occur.
> Intarray is only defined for int4 type. So I think that when you write int8[]
> @> int8[] it could only use generic operator, when you write int4[] @> int4[]
> it uses the dedicated operator from intarray, but when you write int2[] @>
> int2[] it has two choices, cast to int4[] and use the dedicated operator or
> use the generic one.
> So you could :
> - enforce the choice by casting in int4[] ('{...}'::int2[]::int4[] @>
> '{...}'::int2[]::int4[])
> - call method instead of operator
> _int_contains('{...}'::int2[],'{...}'::int2[]), in this case cast will be
> automatic Note that you will have performances issues with int8 compare to
> int2/int4 because it will use generic method that is less efficient than
> intarray one.
> Regards
> Mathieu Pujol

Hi,

this was my conclusion too.
as first measure to avoid the exception I'm using   '{...}'::int2[] @> '{...}'::int2[]::int4[]
whereas I'm not sure which @> operator is used here. Probably the one of intarray.

My next concern is that '{...}'::int4[] @> '{...}'::int4[] will always use the intarray operator and I don't know if it
behavesdifferently than the core @> 

Calling the method instead of the operator is not really an option:
Code modifications affecting only calls to intarray functionalities would be ok, but here it is the other way: call to
corefunctionalities need to be changed. 

I wonder a bit why there is no precedence rules for such cases.
IMHO the casting option  should only be envisaged when no operator could be found without it
with a lookup path like:

    1) int2[] @> int2[]
    2) anyarray @> anyarray
    3) cast @> cast


In order to avoid any ambiguity, I'll will probably replace the operator names from intarray to avoid collisions...

regards,

Marc Mamin