CODEWITHSUNDEEP
sqlpostgresql
Amir
Amir

Reputation: 1

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

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.

Upvotes: 0

Views: 5535

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

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.

Upvotes: 4

Related Questions