Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When I run this query on "enterprisedb 9.2", it raises an error:

select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;


LINE 1: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::...
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Error **********

ERROR: function quote_nullable(timestamp without time zone) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Character: 8

In Postgresql 9.1, this query above run ok.

Please tell me what is happening here. Why is the function quote_nullable(timestamp without time zone) not unique?

share|improve this question
up vote 3 down vote accepted

Works as is in my installation (Postgres 9.3).

You probably can fix this by casting to text.

SELECT quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy')::text);

And do not cast the result to date. It would be pointless to call quote_nullable() if you try to cast the result back to date. It would also fail for NULL input.

Postgres allows function overloading. My installation has one instance of quote_nullable() taking text and another one taking anyelement (polymorphic type). The latter takes care of the date.

As for timestamp without time zone: Postgres first looks for an exact match (date in your case), then considers other options like types that can be cast implicitly or polymorphic types. The manual has the exact procedure for Function Type Resolution.

Not sure about EnterpriseDB. Run this to diagnose:

SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  proname = 'quote_nullable';

More about function overloading in this related answer on SO:
ERROR: function addgeometrycolumn is not unique

share|improve this answer
@Er Brand: thanks, I run "SELECT quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::text;" in Enterprisedb 9.2, I still get error. And with your second query it return two rows (quote_nullabe: anyelement, text) . – Luan Huynh Mar 12 '14 at 2:45
@luannnh: Sorry, my mistake. The cast needs to go after the first closing parenthesis. Consider updated answer. Would have been an "impossible" diagnosis otherwise. The error message is still odd. – Erwin Brandstetter Mar 12 '14 at 3:04
@Er Brand: it's ok . But when I run "select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;" in POSTGRESQL, it work fine ^^! (but in EDB it get error) . Could you explain me this problem, please ? – Luan Huynh Mar 12 '14 at 3:08
@luannnh: Sorry, I don't work with EnterpriseDB directly. The different behavior is puzzling me, too, since you seem to have the same two functions. Did you get it to work, then? – Erwin Brandstetter Mar 12 '14 at 3:12
@Er Brand: Until now, I don't clearly know about my problem. I think this is a EDB's bug. I will use another function (same quote_nullable). Thanks a gain Mr ERwin Brandstetter. – Luan Huynh Mar 12 '14 at 3:20

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.