postgresql - 无法选择最佳候选函数。您可能需要添加显式类型转换

Question

select
  c_elementvalue.value AS "VALUE",
  c_elementvalue.name AS "NAME",
  rv_fact_acct.postingtype AS "POSTINGTYPE",
  sum(rv_fact_acct.amtacct) AS "AMNT",
  'YTDB' AS "TYPE",
  c_period.enddate AS "ENDDATE",
  max(ad_client.description) AS "COMPANY"
from
  adempiere.c_period,
  adempiere.rv_fact_acct,
  adempiere.c_elementvalue,
  adempiere.ad_client
where
  (rv_fact_acct.ad_client_id =  ad_client.ad_client_id ) and
  (rv_fact_acct.c_period_id = c_period.c_period_id) and
  (rv_fact_acct.account_id = c_elementvalue.c_elementvalue_id) and
  (rv_fact_acct.dateacct BETWEEN  to_date( to_char( '2017-03-01' ,'YYYY') ||'-04-01', 'yyyy-mm-dd')  AND '2017-03-31'  ) AND
  (rv_fact_acct.ad_client_id = 1000000) and
  (rv_fact_acct.c_acctschema_id = 1000000 )and
  (rv_fact_acct.postingtype = 'B')and
  (rv_fact_acct.accounttype in ('R','E') )
group by  c_elementvalue.value , c_elementvalue.name , rv_fact_acct.postingtype , c_period.enddate
order by 5 asc, 1 asc

在执行上面的 sql 语句(postgres)时，我收到一条错误消息。

错误信息:

[Err] ERROR:  function to_char(unknown, unknown) is not unique
LINE 68:  (rv_fact_acct.dateacct BETWEEN  to_date( to_char( '2017-03-...
                                                   ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts. 

Answer 1

您的这部分查询有问题:

to_date( to_char( '2017-03-01' ,'YYYY') ||'-04-01', 'yyyy-mm-dd')

没有任何函数to_char，第一个参数是string。

postgres=# \df to_char                                   List of functions┌────────────┬─────────┬──────────────────┬───────────────────────────────────┬────────┐│   Schema   │  Name   │ Result data type │        Argument data types        │  Type  │╞════════════╪═════════╪══════════════════╪═══════════════════════════════════╪════════╡│ pg_catalog │ to_char │ text             │ bigint, text                      │ normal ││ pg_catalog │ to_char │ text             │ double precision, text            │ normal ││ pg_catalog │ to_char │ text             │ integer, text                     │ normal ││ pg_catalog │ to_char │ text             │ interval, text                    │ normal ││ pg_catalog │ to_char │ text             │ numeric, text                     │ normal ││ pg_catalog │ to_char │ text             │ real, text                        │ normal ││ pg_catalog │ to_char │ text             │ timestamp without time zone, text │ normal ││ pg_catalog │ to_char │ text             │ timestamp with time zone, text    │ normal │└────────────┴─────────┴──────────────────┴───────────────────────────────────┴────────┘(8 rows)

You can cast string 2017-03-01 to date type. PostgreSQL cannot do it self, because there are more variants: numeric,timestamp, ...

postgres=# select to_date( to_char( '2017-03-01'::date ,'YYYY') ||'-04-01', 'yyyy-mm-dd');
┌────────────┐
│  to_date   │
╞════════════╡
│ 2017-04-01 │
└────────────┘
(1 row)

通常，将字符串操作用于日期时间操作是错误的。 PostgreSQL(和所有 SQL 数据库)有很好的 functions用于日期算术。

例如 - 任务“获取下个月的第一个日期”可以用表达式完成:

postgres=# select date_trunc('month', current_date + interval '1month')::date;
┌────────────┐
│ date_trunc │
╞════════════╡
│ 2017-05-01 │
└────────────┘
(1 row)

您可以用 SQL 语言(宏)编写自定义函数:

postgres=# create or replace function next_month(date) 
           returns date as $$
             select date_trunc('month', $1 + interval '1month')::date $$
           language sql;
CREATE FUNCTION
postgres=# select next_month(current_date);
┌────────────┐
│ next_month │
╞════════════╡
│ 2017-05-01 │
└────────────┘
(1 row)