Update a row when excluded value is different the new value while doing an upsert ing Postgres

everyone

I'm currently facing the following problem: I have a table like this:

        id         operation_state    last_usage_timestamp           timestamp
W1A4533911K458161        GRAY                  NULL         2022-09-22T07:24:52.077+0000

Whenever new data comes, I have to upsert the row and, if the operation_state changes from GRAY or RED to something else, I have to set a last_usage_timestamp.

So, for example, if the new data is:

        id         operation_state    last_usage_timestamp             timestamp
W1A4533911K458161        GREEN              NULL             2022-09-22T07:24:55.077+0000

The desired output should be:

        id         operation_state          last_usage_timestamp             timestamp
W1A4533911K458161        GREEN           2022-09-22T07:24:52.077+0000    2022-09-22T07:24:55.077+0000

However, if the operation_state in the incoming data is still GRAY or RED, I don't set a last_usage_timestamp.

I'm currently trying to do something like this (the important part is the CASE statement):

INSERT INTO my_table(id, operation_state, last_usage_timestamp, timestamp)
                
                VALUES
                (%(id)s, %(operation_state)s, %(last_usage_timestamp)s, %(timestamp)s
                
                ON CONFLICT (id) DO 
                  UPDATE
                    SET
                      operation_state = %(operation_state)s,
                      last_usage_timestamp = (SELECT CASE
                                                      WHEN (EXCLUDED.operation_state = 'GRAY'
                                                            OR EXCLUDED.operation_state = 'RED') AND
                                                            %(operation_state)s NOT IN ('GRAY', 'RED') THEN CAST(%(event_timestamp)s AS TIMESTAMP)
                                                      ELSE NULL END),
                      timestamp = %(timestamp)s
                      
                    WHERE my_table.id = %(id)s

However, this is not working. I only insert NuLL values in the last_usage_timestamp column.

I can think of work arounds for this, but I'd like to know if it's possible to do this with a single statement.

Thanks in advance for any help.