Tebbers Tebbers - 6 months ago 14
SQL Question

Query not returning records with a boolean column set to FALSE?

This one is driving me mad - I must be missing something very simple here but have been Googling for 2 hours and have got nowhere. I'm building a Rails app with Postgresql 9.5.1 that handles appointments for barbers. One of the columns is available - if the appointment is available to book or not - it's a boolean. I need to get the next 5 records for a given appointment ID.

select *
from appointments
where id > 13
and barber_id = 1
limit 5


This returns 5 records, but the available field is all true, even though records 14-18 exist with attributes of available = FALSE.

Screenshot from Postico of the table

If I use the query

select * from appointments where id > 13 and barber_id = 1 and (available = false) limit 5


Then it returns only records with available = FALSE, but I want all records that match, whether TRUE or FALSE.

I have also tried

select * from appointments where id > 13 and barber_id = 1 and (available = false or available = true) limit 5


But this also only returns available = TRUE records.

Tearing my hair out here - what am I missing?! A default or something?

Answer

If your id's are not in sorted order, you might want to sort them and return

    select * from appointments where id > 13 and barber_id = 1 and
           (available = false or available = true) order by id asc limit 5

And your boolean thing can be completely removable if it is a not null column, since you are checking for both true and false :)