PostgreSQL function call

Question

I have PostgreSQL function named test(integer) taking an integer parameter and an overloaded function of the same name test(character varying).

When calling this function with a null value, Postgres always executes the function taking an integer parameter. Why does this happen? Why doesn't Postgres chose the function with a varchar parameter?

Function call example:

select test(null);

Answer 1

That's decided by the rules of Function Type Resolution. Detailed explanation in the manual. Related:

• Is there a way to disable function overloading in Postgres

NULL without explicit type cast starts out as type "unknown":

SELECT pg_typeof(NULL)

 pg_typeof
-----------
 unknown

Actually, I got suspicious and ran a quick test, just to find different results in Postgres 9.3 and 9.4. varchar is picked over integer (which oddly contradicts your findings):

SQL Fiddle.

I would think the according rule is point 4e in the list (none of the earlier points decide the match):

At each position, select the string category if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal looks like a string.)

If you added another function with input type text to the overloaded mix, text would be picked over varchar.

Personally I almost always use text instead of varchar. While being binary compatible (so almost but not quite the same), text is closer to the heart of Postgres in every respect.

I added that to the fiddle, as well as another example where Postgres cannot decide and throws a tantrum.

If you want to pick a particular function, add an explicit type cast (that's the way to go here!):

select test(null::int)     AS func_int
     , test(null::varchar) AS func_vc;