I have a table train_statuses. With the schema as below:
CREATE TABLE public.train_statuses ( id uuid NOT NULL, status text NOT NULL, updated_at timestamp without time zone NOT NULL ); Indexes: "train_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at) "idx_train_statuses_id_updated_at" btree (id, updated_at)
This table can have multiple entries for the same id.
The status can be one among these values :
'cancelled', 'queued','executed', 'failed', 'succeeded'
Now I need to write a query that inserts entries into
train_statuses for all ids that has their status only as either
executed with status as 'cancelled'
I came up with this query
WITH ts AS ( SELECT DISTINCT ON (ts.id) ts.id, ts.status FROM train_statuses ts INNER JOIN train_statuses t ON ts.id = t.id WHERE ts.status IN ('queued','executed') AND ts.status NOT IN ('failed', 'succeeded','cancelled') ) INSERT INTO train_statuses (id, updated_at, status) SELECT id, now(), 'cancelled' FROM ts
This technically works. But I feel there would be a more optimal way to get this done.
train_statuses again with itself doesn't seem that great since the table size is around
150GB and we
don't have index on status.
Please go through and let me know if there is a better way to get the desired result.
Note: If there is any better title for this question please add an edit suggestion.