CODEWITHSUNDEEP
sqlpostgresql
Saurabh wagh
Saurabh wagh

Reputation: 61

ERROR: operator does not exist: boolean > numeric

I tried below query in community edition postgres

select count(*) 
from cdar_cpms_owner.hshldgrp_wkly_actvty s 
where s.wk_id between (extract(isoyear from now()-interval '9 week')
   || trim(to_char(extract(week from now()-interval '9 week'),'09')))::numeric
  and s.wk_id > (extract(isoyear from now()-interval '2 week')
   || trim(to_char(extract(week from now()-interval '2 week'),'09')))::numeric

for that I got an error:

ERROR:  operator does not exist: boolean > numeric
    LINE 2: and s.wk_id > (extract(isoyear from now()-interval '2 week')...
                        ^
    HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Where same query working fine with enterprise version of Postgres.

Can someone please suggest what modification requires in query to make it compatibcle with community version postgres?

Upvotes: 0

Views: 1598

Answers (3)

Belayer
Belayer

Reputation: 14861

I doubt the contention the statement runs in EnterpriseDB:

select count(*) 
from cdar_cpms_owner.hshldgrp_wkly_actvty s 
where s.wk_id between (extract(isoyear from now()-interval '9 week')
   || trim(to_char(extract(week from now()-interval '9 week'),'09')))::numeric
  and s.wk_id > (extract(isoyear from now()-interval '2 week')
   || trim(to_char(extract(week from now()-interval '2 week'),'09')))::numeric;

If it does then EnterpriseDB has a serious issue as this statement doesn't make any sense. If you resolve the just the date manipulation using 19-Apr-2020 as the date you reduce the statement to: 

select count(*) 
  from cdar_cpms_owner.hshldgrp_wkly_actvty s 
 where s.wk_id between 202007 and s.wk_id > 202014;

But presuming wk_id is a numeric the sub-expresion "s.wk_id > 202014" produces a boolean and the resulting where becomes:

where s.wk_id between a numeric and a boolean

And that IS or at least should be invalid. Perhaps the error was detected and corrected in one environment but not in the other.

Solution remove "s.wk_id >" from the AND phase of the between clause:

select count(*) 
  from cdar_cpms_owner.hshldgrp_wkly_actvty s 
    where s.wk_id between (extract(isoyear from now()-interval '9 week')
       || trim(to_char(extract(week from now()-interval '9 week'),'09')))::numeric
      and (extract(isoyear from now()-interval '2 week')
       || trim(to_char(extract(week from now()-interval '2 week'),'09')))::numeric;

BTW getting the ISO Year and ISO week can be retrived directly with to_char having format 'iyyyiw'. So the query becomes (IMHO) cleaner more easily understood (although I admit the format initially looks strange): 

select count(*) 
  from cdar_cpms_owner.hshldgrp_wkly_actvty s 
 where s.wk_id between to_char(now()-interval '9 week','iyyyiw')::numeric
                   and to_char(now()-interval '2 week','iyyyiw')::numeric;

Upvotes: 0

jjanes
jjanes

Reputation: 44167

The expression s.wk_id between (extract(isoyear from now()-interval '9 week') || trim(to_char(extract(week from now()-interval '9 week'),'09')))::numeric and s.wk_id is boolean.

Then you are doing > (extract...)::numeric against that first boolean expression. I don't see any other way to parse it that would be more valid. If the AND is a conjunction, then your BETWEEN is missing an AND. If the AND belongs to the between, then there is a type mismatch. Please add explicit parentheses to show how you think the BETWEEN, AND, and > should be divided up.

Upvotes: 1

Pavel Stehule
Pavel Stehule

Reputation: 45795

I don't know any enterprise version of Postgres. Maybe you mean EnterpriseDB? This database try to be more compatible with Oracle, than Postgres. Postgres is type strict database, Oracle is a type tolerant database. More Oracle doesn't know boolean datatype, there is integer only.

The error message is clean - you cannot to compare boolean datatype with numeric. Is strange so your s.wk_id is boolean - it looks like error on your side. You can fix it easy by explicit casting like s.wk_id::int, but it looks like total nonsense. You have to check data type of s.wk_id. Probably it should not be boolean.

Upvotes: 0

Related Questions