CODEWITHSUNDEEP
sqlpostgresqlrange
Robert Wilson
Robert Wilson

Reputation: 669

Postgresql Range lower bound must be less than or equal to range upper bound

I have a query that checks for schedule time overlaps for schools in a Postgres database. The issue is, the query below throws this error ERROR: range lower bound must be less than or equal to range upper bound SQL state: 22000 in certain schools but works in others. Any help will be appreciated.

SELECT ls.* 
FROM lesson_schedules ls 
WHERE TSRANGE( CAST(CONCAT(ls.start_date, ' ', ls.start_time) AS TIMESTAMP) , CAST(CONCAT(ls.end_date, ' ', ls.end_time) AS TIMESTAMP), '()') 
      && TSRANGE( CAST(CONCAT('2021-01-15', ' ','09:00:00+00') AS TIMESTAMP), CAST(CONCAT('2021-01-15', ' ','09:40:00+00') AS TIMESTAMP), '()') 
AND ls.school_id = 6 
AND ls.grade_id = 58 
AND ls.parent = FALSE

Upvotes: 2

Views: 6217

Answers (1)

user330315
user330315

Reputation:

I think the error message is self-explanatory: you have rows where start_date + start_time is bigger than end_date + end_time.

To find those rows you can use:

select *
from lesson_schedules
where start_date + start_time > end_date + end_time;

(The above assumes that start_date and end_date are date columns and that start_time and end_time are time columns)

Once you have those rows, fix their dates.

One way to prevent such "corrupted" data in the future is to create a check constraint (using the above expression) or create a single tsrange column.

Upvotes: 4

Related Questions