Re: Failed to autoconvert '1' to text. - Mailing list pgsql-general

On 06/09/13 09:13, Szymon Guz wrote:

Hi,
why isn't 'aa' always treated as string?

with x as (
   select
   '1' a,
   '2' b
)
SELECT levenshtein(a, b), length(a)
FROM x;

ERROR:  failed to find conversion function from unknown to text

Why should I cast '1' to '1'::TEXT to satisfy a function (TEXT, TEXT)?

I think it's to do with the CTE. Presumably its types get fixed separately from the SELECT levenshtein() call. A quoted literal is type "unknown" until it has a context. It could be a date, point, hstore etc.

If you use the literals directly the context lets PostgreSQL figure it out.
   SELECT levenshtein('1','2');