Update with distinct on violates unique key in postgres?
As Akina hinted at, without a relational predicate between your subquery and the outer parent query, this is going to try to UPDATE all rows in your schema.current_status table to the same value from your schema.daily table, violating the unique key constraint. This would only work if there was only a single record in your schema.current_status table. What is the relationship between schema.current_status and schema.daily?...as that's what you're currently missing in a predicate clause (such as a WHERE clause).