Function quote_nullable(timestamp without time zone) is not unique

Question

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?

Answer 1

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