Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable(user_id int, enabled boolean);
create materialized view myview as 
	select 
		user_id, 
		enabled, 
		(row_number() over(partition by user_id, enabled) - 1) % 2 rn 
	from mytable
create unique index on myview(user_id, rn) where(enabled);
create or replace function refresh_myview()
returns trigger language plpgsql
as '
begin
    refresh materialized view myview;
    return null;
end';
create trigger refresh_myview
after insert or update
on mytable for each row
execute procedure refresh_myview();
insert into mytable values
    (123, true),
    (123, true),
    (234, false),
    (234, true);
4 rows affected

select * from myview;


user_id
enabled
rn


123
t
0

123
t
1

234
f
0

234
t
0


insert into mytable values(123, true);
ERROR:  could not create unique index "myview_user_id_rn_idx"
DETAIL:  Key (user_id, rn)=(123, 0) is duplicated.
CONTEXT:  SQL statement "refresh materialized view myview"
PL/pgSQL function refresh_myview() line 3 at SQL statement


select * from myview;


user_id
enabled
rn


123
t
0

123
t
1

234
f
0

234
t
0


insert into mytable values(234, true);
1 rows affected

select * from myview;


user_id
enabled
rn


123
t
0

123
t
1

234
f
0

234
t
0

234
t
1