How to efficiently combine user authentication with token authentication?
Apparently this use case is fairly specific and thus it is quite a challenge to write an &quot;upsert&quot; statement what covers all the steps as described in the question. Therefor I see the following procedure as the only way out.
create or replace function user_auth(p_user text, p_pass text)
returns token_response as
$$
declare
    v_user_id bigint;
    v_token_id bigint;
    v_updated_on timestamp with time zone;
    v_access_token text;
    v_refresh_token text;
begin
    select id
    into v_user_id
    from users
    where username = p_user
        and password = validate_password_hash(p_pass, password)
    limit 1;

    if (v_user_id is not null) then
        select id, updated_on, access_token, refresh_token
        into v_token_id, v_updated_on, v_access_token, v_refresh_token
        from tokens
        where user_id = v_user_id
        order by updated_on desc
        limit 1;

        if (v_token_id is null) then
            insert into tokens (user_id)
            values (v_user_id)
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;

        if (v_updated_on &lt; current_timestamp - interval '1 day') then
            update tokens
            set access_token = generate_random_token(),
                refresh_token = generate_random_token(),
                updated_on = current_timestamp
            where id = v_token_id
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;
    end if;

    return (v_access_token, v_refresh_token)::token_response;
end;
$$ language plpgsql;

Here is a working DB fiddle