Postgres Pro
Downloads
Home   >   mailing lists

Thread: Failed to autoconvert '1' to text.

Failed to autoconvert '1' to text.

From
Szymon Guz
Date:
Hi,
why isn't 'aa' always treated as string?

While testing function for levenshtein distance I've noticed that:

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

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

 levenshtein | length 
-------------+--------
           1 |      1
(1 row)


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

thanks,
Szymon

Re: Failed to autoconvert '1' to text.

From
Richard Huxton
Date:
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');

--
   Richard Huxton
   Archonet Ltd

Re: Failed to autoconvert '1' to text.

From
Szymon Guz
Date:
On 6 September 2013 10:33, Richard Huxton <dev@archonet.com> wrote:
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');



Yep, I can use literals without any problem, as this function is  levenshtein(text, text).