Обсуждение: operator does not exist: smallint <> smallint[]

Hi,

I saw a report at .br mailing list [1] complaining about the message's title.
I do not try to investigate it.  Am I missing something?

euler=# select attname from pg_attribute where attnum > 0 and attnum <>
ALL(select conkey from pg_constraint where conrelid = attrelid and contype = 'p');
ERROR:  operator does not exist: smallint <> smallint[]
LINHA 1: ...ame from pg_attribute where attnum > 0 and attnum <> ALL(sel...
               ^
 
DICA:  No operator matches the given name and argument type(s). You might need
to add explicit type casts.
euler=# select 1::smallint <> ALL(array[2::smallint, 3::smallint]);?column?
----------t
(1 registro)

euler=# select 1::smallint <> ALL(array[1::smallint, 2::smallint,3::smallint]);?column?
----------f
(1 registro)

euler=# select 1::smallint <> ALL(array[1, 2, 3]);?column?
----------f
(1 registro)


[1] http://listas.postgresql.org.br/pipermail/pgbr-geral/2008-December/013277.html


--  Euler Taveira de Oliveira http://www.timbira.com/

Euler Taveira de Oliveira <euler@timbira.com> writes:
> I saw a report at .br mailing list [1] complaining about the message's title.
> I do not try to investigate it.  Am I missing something?

> euler=# select attname from pg_attribute where attnum > 0 and attnum <>
> ALL(select conkey from pg_constraint where conrelid = attrelid and contype = 'p');
> ERROR:  operator does not exist: smallint <> smallint[]

It's entirely right: there's no such operator.



Oh, you wanted a way to write the query correctly?  I think what this
person wants might be something like

select attname from pg_attribute where attnum > 0 and not attisdropped
and not exists (select 1 from pg_constraint where attnum = ANY(conkey)               and conrelid = attrelid and
contype= 'p');
 

... although that produces quite a lot of rows, so some additional
constraint is probably wanted too.
        regards, tom lane

Tom Lane escreveu:
> Euler Taveira de Oliveira <euler@timbira.com> writes:
>> I saw a report at .br mailing list [1] complaining about the message's title.
>> I do not try to investigate it.  Am I missing something?
> 
>> euler=# select attname from pg_attribute where attnum > 0 and attnum <>
>> ALL(select conkey from pg_constraint where conrelid = attrelid and contype = 'p');
>> ERROR:  operator does not exist: smallint <> smallint[]
> 
> It's entirely right: there's no such operator.
> 
Out of curiosity, why the other queries work?

euler=# select 1::smallint <> ALL(array[2::smallint, 3::smallint]);?column?
----------t
(1 registro)

euler=# select 1::smallint <> ALL(array[1::smallint, 2::smallint,3::smallint]);?column?
----------f
(1 registro)

euler=# select 1::smallint <> ALL(array[1, 2, 3]);?column?
----------f
(1 registro)


--  Euler Taveira de Oliveira http://www.timbira.com/

Euler Taveira de Oliveira <euler@timbira.com> writes:
> Tom Lane escreveu:
>> It's entirely right: there's no such operator.
>> 
> Out of curiosity, why the other queries work?

The behavior is different depending on whether the argument of ANY/ALL
is a sub-SELECT or not.  If it is, then the comparisons are between the
LHS and the successive values produced by the sub-SELECT.  If it isn't,
then the RHS has to produce an array and the comparisons are to the
array elements.
        regards, tom lane