Combine two identifiers in PostgreSQL

I am creating a database in postgreSQL

I have two tables, one containing details about a house and another for each room in the house. The house_id is set up using house_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

I want the room_id to be 1, 2, .. n for n rooms in the house. Which starts at 1 again for a new house_id.

This mean I have to combine these two identifiers somehow, and create a sequence for room_id which only counts upwards for a new house_id.

Is this possible in postgreSQL? Or should I settle for a room_id integer where I basically check the max room_id for each house_id and add one to it to form the new room_id

Solution

Only deal with insert operation. Since update can be update to any integer value. delete can also delete any value.
I enforce the not null constraint. Otherwise we need to deal with null case.
demo
all raise notice is for debugging.
one special case, when table room have zero row, then assign room_id value to 1 and set the sequence nextval to 2.
if house_id is new value then set the room_id to 1 and set the sequence nextval to 2.

CREATE OR REPLACE FUNCTION restart_seq ()
    RETURNS TRIGGER
    AS $$
BEGIN
    RAISE NOTICE 'new.house_id: %', NEW.house_id;
    RAISE NOTICE 'all.house_id: %', (
        SELECT
            array_agg(house_id)
        FROM
            room);
    RAISE NOTICE 'new.house_id already exists: %', (
        SELECT
            NEW.house_id IN (
                SELECT
                    house_id
                FROM
                    room));
    IF (NEW.house_id IN (
        SELECT
            house_id
        FROM
            room)) IS FALSE THEN
        NEW.room_id = 1;
        ALTER SEQUENCE room_room_id_seq
            RESTART WITH 2;
        RAISE NOTICE 'currval(''room_room_id_seq''): %', currval('room_room_id_seq');
        RETURN new;
    END IF;
    IF (
        SELECT
            count(house_id)
        FROM
            room) = 0 THEN
        NEW.room_id = 1;
        ALTER SEQUENCE room_room_id_seq
            RESTART WITH 2;
        RETURN new;
    END IF;
    RETURN new;
END;
$$
LANGUAGE plpgsql;