PostgreSQL check constraint for foreign key condition

The only way I can think of is to add a unique constraint on (id, superuser) to the users table and reference that from the user_has_job table by "duplicating" the superuser flag there:

create table users (
    id serial primary key,
    name text not null,
    superuser boolean not null default false
);

-- as id is already unique there is no harm adding this additional 
-- unique constraint (from a business perspective)
alter table users add constraint uc_users unique (id, superuser);

create table job (
    id serial primary key,
    description text
);

create table user_has_job (
    user_id integer references users (id),
    -- we need a column in order to be able to reference the unique constraint in users
    -- the check constraint ensures we only reference superuser
    superuser boolean not null default true check (superuser), 
    job_id integer references job(id),
    constraint user_has_job_pk PRIMARY KEY (user_id, job_id),
    foreign key (user_id, superuser) references users (id, superuser)
);


insert into users 
 (id, name, superuser)
values 
  (1, 'arthur', false),
  (2, 'ford', true);

insert into job 
  (id, description)
values   
  (1, 'foo'),
  (2, 'bar');

Due to the default value, you don't have to specify the superuser column when inserting into the user_has_job table. So the following insert works:

insert into user_has_job 
  (user_id, job_id)
values
  (2, 1);

But trying to insert arthur into the table fails:

insert into user_has_job 
  (user_id, job_id)
values
  (1, 1);

This also prevents turning ford into a non-superuser. The following update:

update users 
  set superuser = false 
where id = 2;

fails with the error

ERROR: update or delete on table "users" violates foreign key constraint "user_has_job_user_id_fkey1" on table "user_has_job"
Detail: Key (id, superuser)=(2, t) is still referenced from table "user_has_job".

Create a separate superuser table that inherits from the user table:

CREATE TABLE "user" (
    id serial PRIMARY KEY,
    name text NOT NULL,
);

CREATE TABLE superuser () INHERITS ("user");

The user_has_job table can then reference the superuser table:

CREATE TABLE user_has_job (
    user_id integer REFERENCES superuser (id),
    job_id integer REFERENCES job(id),
    PRIMARY KEY (user_id, job_id)
);

Move users around between the tables as needed by inserting and deleting:

WITH promoted_user AS (
    DELETE FROM "user" WHERE id = 1 RETURNING *
) INSERT INTO superuser (id, name) SELECT id, name FROM promoted_user;

I don't know if this is a good way to do it but it seems to work

    INSERT INTO user_has_job (user_id, job_id) VALUES (you_user_id, your_job_id)
    WHERE EXIST (
        SELECT * FROM user WHERE id=your_user_id AND superuser=true
    );