Postgres Warning: pg_query(): Query failed: ERROR: operator does not exist: boolean = integer LINE 1

Question

i'm using postgres and i want tried date_diff, but i get this error

Warning: pg_query(): Query failed: ERROR: operator does not exist: boolean = integer LINE 1

and then this is my query

select id_mitra
      ,company_name 
from ref_mitra
where reject=0 and DATE_DIFF(now(),date_status) > 3 and active='N'

what wrong in my query?

Show your table description please. Maybe reject is a boolean. — Jens
– Jens, Commented May 11, 2015 at 5:41
If you want to use the value 0 (zero), you have to cast this value to a boolean: reject= CAST(0 as boolean) — Frank Heikens
– Frank Heikens, Commented May 11, 2015 at 5:43
You need to compare a boolean to a boolean where reject = false. There is also no date_diff function in Postgres. — user330315
– user330315, Commented May 11, 2015 at 5:44

score 2 · Accepted Answer · 2015-05-11 05:47:48Z

2

You have two errors in your query:

you are comparing a number (0) to a boolean
there is no date_diff() function in Postgres

Assuming date_status is a (real) date column, your query should look like this:

select id_mitra
      ,company_name 
from ref_mitra
where reject = false 
  and current_date - date_status > 3 
  and active='N';

now() returns a timestamp but as you apparently want the number of days (not an interval) you should use current_date instead of now().

I am not sure what date_diff() really does, maybe you need to write date_status - current_date to get the same behaviour.

Instead of reject = false you can also use where not reject

edited May 11, 2015 at 5:47

answered May 11, 2015 at 5:45

user330315

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

2 Comments

Vivek S. Over a year ago

this is what I was going to answer :)

Frank Heikens Over a year ago

Using NOT for a boolean is a nice trick, but only when NOT isn't a query parameter: You can't use a prepared statement where NOT depends on user input.

Frank Heikens · Accepted Answer · 2015-05-11 05:46:25Z

1

Cast your value 0 to boolean:

select id_mitra
      ,company_name 
from ref_mitra
where reject = CAST(0 as boolean) 
and DATE_DIFF(now(),date_status) > 3 
and active='N';

Or use a boolean:

select id_mitra
      ,company_name 
from ref_mitra
where reject = false 
and DATE_DIFF(now(),date_status) > 3 
and active='N';

DATE_DIFF() isn't a standard function in PostgreSQL, but maybe you created a function using this name.

edited May 11, 2015 at 5:46

answered May 11, 2015 at 5:44

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

2 Comments

Vivek S. Over a year ago

Are you sure that date_diff is avail. in PostgreSQL?

Frank Heikens Over a year ago

@vivek: No I'm not, but you could create it yourself using CREATE FUNCTION

Collectives™ on Stack Overflow

Postgres Warning: pg_query(): Query failed: ERROR: operator does not exist: boolean = integer LINE 1

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related