SQL Error [22P02] does not recognise varchar converted to integer in the where clause

user_8870782 0
Viewed 199

I have table TROQ which does have a field named cod defined as Varchar(13) (Postgres 11.8)

When the first four characters of cod are numeric it means it is "special troq". Special Troqs, according to this numeric first four characters can be "Production" when this four numeric characters form a code that is less than 5000 and "Development" when the code is over 5000. This is just for the example, in the real problem there are many more cathegories to special troqs, but each of them forming a numeric range as for the example

So I tried the following query:

select cod, pref
from 
(select cod, substr(cod, 1, 4)::integer pref
from troq
where isnumeric(substr(cod,1, 4))) CT
where pref < 5000

Implementation for function isnumeric:

CREATE OR REPLACE FUNCTION public.isnumeric(text)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
DECLARE x NUMERIC;
BEGIN
       x = $1::NUMERIC;
       RETURN TRUE;
EXCEPTION WHEN others THEN
       RETURN FALSE;
END;
$function$
;

And I got error:

    SQL Error [22P02]: ERROR: la sintaxis de entrada no es válida para integer: «INFD» 
... Meaning about: Input sintax not valid for integer <<INFD>>

If I take off the where from the outer query:

select cod, pref
from 
(select cod, substr(cod, 1, 4)::int pref
from troq
where isnumeric(substr(cod,1, 4))) CT

Then it throws no error message. It shows all TROQ's whose first four characters have a numeric code. But I find no way to apply any condition on this pref casted field.

Next thing I tried was all kind of castings on the where, some making sense others not, but with no improvement on the result... Meaning things like:

... where pref::varchar < '5000'
... where pref::numeric < 5000

Just in case the matter was about the casting, I tried to_number with same result (The query works with no error if I remove the where clause and gives this 22P02 error when I try to add a condition on casted field pref):

select cod, pref
from 
(select cod, to_number(substr(cod, 1, 4), '0000') pref
from troq
where isnumeric(substr(cod,1, 4))) CT
where pref < 5000

Any explanation or help on the matter would be highly appreciated. Thank you in advance

Script to reproduce the exercise with just four records:

CREATE TABLE public.troq (
    cod varchar(13) NOT NULL
);

INSERT INTO public.troq (cod) VALUES 
('1234Trala')
,('Tururu')
,('4532Vargas')
,('n4567Titi')
;

(Troq records that should be recovered would be 1234Trala and 4532Vargas)
casting postgresql
3 Answers

The problem is that PostgreSQL flattens the subquery and optimizes the query so that the condition pref < 5000 is evaluated first, because PostgreSQL thinks that that is cheaper:

EXPLAIN (COSTS OFF)
select cod, pref
from 
(select cod, substr(cod, 1, 4)::integer pref
from troq
where isnumeric(substr(cod,1, 4))) CT                  
where pref < 5000;
                                     QUERY PLAN
════════════════════════════════════════════════════════════════════════════════════
 Seq Scan on troq
   Filter: (((substr(cod, 1, 4))::integer < 5000) AND isnumeric(substr(cod, 1, 4)))
(2 rows)

That is documented and as it to be expected:

The order of evaluation of subexpressions is not defined.

One trick you can do is to tell PostgreSQL that the function is very cheap (I set it IMMUTABLE at the same time, because it is):

ALTER FUNCTION isnumeric COST 1 IMMUTABLE;

That changes the execution plan, so that your function is evaluated first, and the error is avoided:

EXPLAIN (COSTS OFF)
select cod, pref
from 
(select cod, substr(cod, 1, 4)::integer pref
from troq
where isnumeric(substr(cod,1, 4))) CT
where pref < 5000;
                                     QUERY PLAN
════════════════════════════════════════════════════════════════════════════════════
 Seq Scan on troq
   Filter: (isnumeric(substr(cod, 1, 4)) AND ((substr(cod, 1, 4))::integer < 5000))
(2 rows)

But that is of course not totally reliable (the other expression might also be very “cheap”), and a better solution would be to add an optimizer barrier like OFFSET 0 to the subquery that will prevent it from being flattened:

EXPLAIN (COSTS OFF)
select cod, pref
from 
(select cod, substr(cod, 1, 4)::integer pref
from troq
where isnumeric(substr(cod,1, 4))
OFFSET 0) CT
where pref < 5000;
                  QUERY PLAN                  
══════════════════════════════════════════════
 Subquery Scan on ct
   Filter: (ct.pref < 5000)
   ->  Seq Scan on troq
         Filter: isnumeric(substr(cod, 1, 4))
(4 rows)
user_64643080

Another option is to use a regular expression to validate the first 4 characters are digits, and forgo the is_numeric function all together.

select cod, pref
from 
    (select cod, to_number(substr(cod, 1, 4), '0000') pref
       from troq
      where  cod ~ '^\d{4}' 
    ) s
where pref < 5000;

Now, regular expressions tend to be slower. But is it slower than substring, function call, type conversion, assignment, exception handling? Only testing would tell.

user_76238560

I just tried doing it in a different way and it worked. Don´t understand why though. The solution that did work:

with CT as
(select cod, to_number(substr(cod, 1, 4), '0000') pref
from troq
where isnumeric(substr(cod,1, 4)))
select * from CT 
where pref < 5000;

I guess this way I make more sure that the optimiser executes first the inner query, but as short as I do know from SQL I did not think I would be having to make sure of that in any event.

user_88707820
Related