Re: string_agg distinct order by

I believe that you could define an enumerated type to use for those status
colors such that the ordering is defined as you like without two separate
columns for the name and sort_value or whatever.

https://www.postgresql.org/docs/current/datatype-enum.html

Example in the documentation expanded a little to demonstrate-

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
INSERT INTO person VALUES ('Joe', 'sad');
INSERT INTO person VALUES ('Roe', 'ok');

SELECT * FROM person order by current_mood;
SELECT * FROM person order by current_mood desc;

Note- using enum may complicate other things in your usage, so I am not
suggesting this is ideal, just one option.

*Michael Lewis | Database Engineer*
*Entrata*