ERROR: operator does not exist: boolean > numeric

Question

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?

Please explain what you are trying to do. Sample data and desired results would help. There is probably a simpler method — Gordon Linoff
– Gordon Linoff, Commented Apr 19, 2020 at 14:51
Please provide sample data to reproduce the case. This is probable data related. For some unexplained reason it magically works in one database and not the other. PostgreSQL is helping you by converting from VARCHAR to INTEGER behind the scenes, as long as the data is decent enough. However, it seems the data is bad on the second database and PostgreSQL is unable to convert it anymore. — The Impaler
– The Impaler, Commented Apr 19, 2020 at 15:09

jjanes · Accepted Answer · 2020-04-19 19:00:02Z

1

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.

answered Apr 19, 2020 at 19:00

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pavel Stehule · Accepted Answer · 2020-04-19 15:13:55Z

0

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.

answered Apr 19, 2020 at 15:13

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

2 Comments

Saurabh wagh Over a year ago

Yes . s.wk_id is numeric, I tried this query in both EDB and community Postgress, not sure but it works with EDB but not with community postgres. Is there any way in which we can compare boolean with numeric?

Pavel Stehule Over a year ago

@Saurabhwagh - jjanes has true - there is broken ordering - you miss parenthesis in expression. It is hard to understand what you want, but it is good to use explicitly parenthesis instead default operator priority. Maybe there should be parenthesis like `(s.wk_id > (...))

Belayer · Accepted Answer · 2020-04-19 23:30:59Z

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;

Collectives™ on Stack Overflow

ERROR: operator does not exist: boolean > numeric

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related