Take the 2-minute tour ×
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.

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;

Error:

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

1 Answer 1

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) . –  Innnh Mar 12 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 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 ? –  Innnh Mar 12 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 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. –  Innnh Mar 12 at 3:20

Your Answer

 
discard

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.