Postgres Compare string to number return correct result

Trying to understand how postgres compare string to number because following queries return true,

SELECT 1 WHERE '1' = 1
SELECT 1 WHERE '1' <= 10
SELECT 1 WHERE '1' BETWEEN 1 and 10
SELECT 1 WHERE 1 BETWEEN '1' and '10'

Can someone please explain?

Answer

PostgreSQL applies implicit conversion where necessary and possible. How it does this is no further explained as far as I know. (https://www.postgresql.org/docs/9.1/typeconv.html)

Anyway, we can play a little with your queries and find out what’s happening:

SELECT '12' BETWEEN 1 and 2 

would result in true for SELECT '12' BETWEEN '1' and '2' and false for SELECT 12 BETWEEN 1 and 2. Run it. It returns false.

Here is a further test:

SELECT 1 BETWEEN 'A' and '10'

This fails with ERROR: invalid input syntax for type integer: "A".

So, PostgreSQL tries to convert the strings to numbers, because it thinks you want to deal with numbers here.