Postgres警告:pg_query():查询失败:ERROR:运算符不存在:boolean = integer LINE 1

时间:2021-03-09 23:07:41

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

我正在使用postgres,我想尝试date_diff,但我得到这个错误

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

警告:pg_query():查询失败:ERROR:运算符不存在: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?

我的查询有什么问题?

2 个解决方案

#1


Cast your value 0 to boolean:

将值0转换为布尔值:

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.

DATE_DIFF()不是PostgreSQL中的标准函数,但也许您使用此名称创建了一个函数。

#2


You have two errors in your query:

您的查询中有两个错误:

  1. you are comparing a number (0) to a boolean
  2. 您正在将数字(0)与布尔值进行比较

  3. there is no date_diff() function in Postgres
  4. Postgres中没有date_diff()函数

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

假设date_status是(实际)日期列,您的查询应如下所示:

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().

now()返回一个时间戳,但是你显然想要天数(不是间隔),你应该使用current_date而不是now()。

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

我不确定date_diff()到底是做什么的,也许你需要编写date_status - current_date来获得相同的行为。

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

而不是reject = false,你也可以使用不拒绝的地方

#1


Cast your value 0 to boolean:

将值0转换为布尔值:

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.

DATE_DIFF()不是PostgreSQL中的标准函数,但也许您使用此名称创建了一个函数。

#2


You have two errors in your query:

您的查询中有两个错误:

  1. you are comparing a number (0) to a boolean
  2. 您正在将数字(0)与布尔值进行比较

  3. there is no date_diff() function in Postgres
  4. Postgres中没有date_diff()函数

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

假设date_status是(实际)日期列,您的查询应如下所示:

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().

now()返回一个时间戳,但是你显然想要天数(不是间隔),你应该使用current_date而不是now()。

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

我不确定date_diff()到底是做什么的,也许你需要编写date_status - current_date来获得相同的行为。

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

而不是reject = false,你也可以使用不拒绝的地方