Postgres Compare string to number return correct result

Question

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?

My advice is: Don't do this. Either deal with strings or with numbers. Don't mix the two. Don't use a varchar column for storing numbers either, but use a numeric datatype. If you are forced to deal with such a table, then either treat the column as a string in your queries or use some function / expression to ensure the content is numeric and cast / convert. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 19, 2019 at 8:13
I think you are right, treating numbers is not correct, as per your answer '12' BETWEEN '1' and '2' will return true as well. — Adil
– Adil, Commented Nov 19, 2019 at 8:17

Thorsten Kettner · Accepted Answer · 2019-11-19 08:03:47Z

5

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.

answered Nov 19, 2019 at 8:03

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I want to throw in that Postgres's behavior is SQL standard -- as far as I know -- and this is how all databases behave (or at least should behave).