Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table "user"
(
    id                            serial                   not null,
    username                      varchar(30)              not null,
    is_active                     boolean                  not null,
    company_id                    integer                  not null
)
CREATE UNIQUE INDEX idx ON "user"(company_id, 
 (CASE WHEN username LIKE '%-support' THEN 'support' ELSE username END)) 
WHERE is_Active = True;
INSERT INTO "user"(username, is_active, company_id)
VALUES ('a', True, 3)
1 rows affected

INSERT INTO "user"(username, is_active, company_id)
VALUES ('a-support', True, 3)
1 rows affected

INSERT INTO "user"(username, is_active, company_id)
VALUES ('b-support', False, 3)
1 rows affected

INSERT INTO "user"(username, is_active, company_id)
VALUES ('c-support', True, 3)
ERROR:  duplicate key value violates unique constraint "idx"
DETAIL:  Key (company_id, (
CASE
    WHEN username::text ~~ '%-support'::text THEN 'support'::character varying
    ELSE username
END))=(3, support) already exists.