SQL Query for Events table

I have an event table in Postgres, with this structure:

event
--------
id: serial, pk
item_id: text, fk
status: text, an enum such as CREATED, IN_PROGRESS, DONE...

This works as a log, so all entries are immutable, change is status will be another INSERT.

I want to efficiently extract all events which have status CREATED, but not DONE.

Answer

I assume that you want ITEMs that meet the conditions. If so:

select item_id
from events
where status in ('CREATED', 'DONE')
group by item_id
having max(status) = 'CREATED';

This uses the fact that 'CREATED' < 'DONE' alphabetically. You can also use conditional aggregation:

having count(*) filter (where status = 'DONE') = 0;