> -----Original Message----- > From: Pujol Mathieu [mailto:mathieu.pujol@xxxxxxxxxxxxx] > 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 behaves differently 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 core functionalities 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
