PostgreSQL - Check value is not the same in multiple columns
To ensure that the e-mail field has priority, use ORDER BY:
SELECT password
FROM account_users
WHERE username = $1
   OR email = $1
ORDER BY email = $1 DESC
LIMIT 1;

Alternatively, use UNION ALL, which might be easier to understand:
SELECT password FROM account_users WHERE email = $1
UNION ALL
SELECT password FROM account_users WHERE username = $1
LIMIT 1;


Is there any reason to allow @ in the user name?
username      text not null unique check (username !~ '@'),
email         text not null unique check (email    ~  '@'),

(You might allow the user name to be an e-mail address if it is the user's own.)

A CHECK constraint is not allowed to look at other rows. So you would have to use triggers (on any INSERT or UPDATE, check that no other row with the same value exists).