PostgreSQL Query Refactoring – Best Practices

postgresqlquery-refactor

I have a simple table called events

It has a date, start time, end time and status

I have multiple events for the same date, start and end.

To know if someone can make an appointment, I have to check if for that date and start, end I have a line that the status is false (not occupied).

The table definition with some example code:

CREATE SEQUENCE events_id_seq;

CREATE TABLE events(
  id int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
  event_date date NULL,
  status bool NULL DEFAULT false,
  event_start time NULL,
  event_end   time NULL
) ;

INSERT INTO events(event_date,status,event_start,event_end)
VALUES
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', FALSE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00');

select distinct event_date, event_start, event_end, (
    select count(*) from events e1
    where 
        e1.status = false and
        e1.event_date = e.event_date and
        e1.event_start = e.event_start and
        e1.event_end = e.event_end
) as free_slots from
events e
order by event_date, event_start;

enter image description here

http://sqlfiddle.com/#!15/698b7

It works, but since I'm not a SQL expert, I am just seeking advice on other forms to solve this, just to learn a little more.

Best Answer

If you just want to see the number of free slots, you can simplify your query using conditional aggregation:

select event_date, 
       event_start, 
       event_end,
       count(*) filter (where not status) as free_slots
from events e
group by event_date, event_start, event_end
order by event_date, event_start;

The filter() clause was introduced in Postgres 9.4. If you are still using an older version you can use a CASE statement instead:

select event_date, 
       event_start, 
       event_end,
       count(case when not status then 1 end) as free_slots
from events e
group by event_date, event_start, event_end
order by event_date, event_start;

Incidentally, status is not a very good name for the column. Something like is_taken or is_full would be better and likely cause less confusion.

Related Question