Working of “between” operator for Varchar and Char datatypes in postgresql

I would like to use “between” operator on “varchar” and “char(5)” columns in a table. However, I don’t see any effect of “between” operator in the results.

Please note that “code” is the column name

I have a table with a column of type VARCHAR and I apply cast operation to convert it into “CHAR(5)” type. When I apply between operator in the query on either of the columns, it doesn’t produce a filtered result.

For VARCHAR COLUMN

select code from diagnoses where code between '4254' and 
'5855'

For CHAR(5) COLUMN

 select cast(code as char(5)) as code
 from diagnoses where code between '4254' and '5855' 

I expect the output to have only records within this range but instead I get records which don’t meet this criteria as well. Please find a sample screenshot below

Answer

demo:db<>fiddle

The BETWEEN operator for character based types works for alphabetical order. That means it uses the order

4254
45829
486
...
58381
5845
5855

So the output is completely correct.

If you want a numeric order you have to cast it into an appropriate data type:

select 
    cast(code as char(5)) as code
from 
    diagnoses 
where 
    cast(code as int) between 4254 and 5855 -- cast here

Result:

4254
5119
5589
5845
5855