Thread

1. Failed to autoconvert '1' to text.

   Szymon Guz <mabewlun@gmail.com> — 2013-09-06T08:13:43Z

   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
   

2. Re: Failed to autoconvert '1' to text.

   Richard Huxton <dev@archonet.com> — 2013-09-06T08:33:18Z

   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
   
   
   

3. Re: Failed to autoconvert '1' to text.

   Szymon Guz <mabewlun@gmail.com> — 2013-09-06T08:39:38Z

   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).