Postgresql query: "greater than" statement returns values less than

Question

I'm trying to select the rows where the value of one column is greater than (>) the other column. However, it returns rows where the values are less than the other column.

If I use the less than operator (<), it returns rows where the values are less than the other column, just like expected. The only difference is the order of the rows.

The values of the columns are "varchar", which I've casted into "integer" for the query.

Query

SELECT tripcode, CAST(expected AS int), CAST(capacity AS int) FROM super_plan WHERE expected > capacity;

This is the result with the greater than (>) operator:

expected	capacity
60	310
50	250
80	270
60	270
60	340
80	340
80	250
70	360
50	360
80	360
...	...

Query

SELECT tripcode, CAST(expected AS int), CAST(capacity AS int) FROM super_plan WHERE expected < capacity;

This is the result with the less than (<) operator:

expected	capacity
100	310
200	310
230	310
280	310
150	310
100	250
200	250
190	250
160	250
110	270
...	...

I don't know why this is happening. I'm new to sql. I've already searched the web and stackoverflow, but I couldn't find something usefull.

As a string '60' is greater than (ie lexicographically ordered later than) '300', just as 'foo' is greater than 'bar' — Bohemian
– Bohemian ♦, Commented Dec 11, 2021 at 2:51

Tim Biegeleisen · Accepted Answer · 2021-12-11 02:18:36Z

5

Actually the columns need to be cast to integers in the WHERE clause, where the comparisons are being made:

SELECT tripcode, expected, capacity
FROM super_plan
WHERE expected::int > capacity::int;

If you have a persistent need to use these columns as integers, perhaps consider changing the column types.

answered Dec 11, 2021 at 2:18

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Postgresql query: "greater than" statement returns values less than

1 Answer 1

Comments

Your Answer

Hot Network Questions

expected	capacity
100	310
200	310
230	310
280	310
150	310
100	250
200	250
190	250
160	250
110	270
...	...

expected	capacity
100	310
200	310
230	310
280	310
150	310
100	250
200	250
190	250
160	250
110	270
...	...

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related

expected	capacity
100	310
200	310
230	310
280	310
150	310
100	250
200	250
190	250
160	250
110	270
...	...