Обсуждение: 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