Обсуждение: Failed to autoconvert '1' to text.
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
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
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 textI 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.Why should I cast '1' to '1'::TEXT to satisfy a function (TEXT, TEXT)?
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).