SQL: Query to insert records into the table if there is not even one record with certain condition

Question

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 queued or 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.

Joining 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.

Thanks

Note: If there is any better title for this question please add an edit suggestion.

Answer 1

Removed superfluous joins and criteria

WITH ts AS (
        SELECT ts.id
         FROM train_statuses ts
         WHERE ts.status IN ('queued','executed')
        EXCEPT
        SELECT ts.id
         FROM train_statuses ts
         WHERE ts.status Not IN ('queued','executed')
)
    INSERT INTO train_statuses (id, updated_at, status) SELECT id, now(), 'cancelled' FROM ts