Combine two identifiers in PostgreSQL

Question

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

Are you looking for an insert statement i.e. how to get the value of next room_id based on existing house_id and room_id in rooms table? — Pankaj
– Pankaj, Commented Jun 23, 2022 at 14:40
I would preferably add it as a contraint or function in the table, but an insert statement would work aswell. — Jeroen Vermunt
– Jeroen Vermunt, Commented Jun 23, 2022 at 14:47
Your approach looks fine for an insert to get max and add 1 based on house_id. Refer following fiddle as per this approach — Pankaj
– Pankaj, Commented Jun 23, 2022 at 14:53

jian · Accepted Answer · 2022-06-23 16:00:21Z

1

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;

answered Jun 23, 2022 at 16:00

jian

5,0131 gold badge29 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeroen Vermunt Over a year ago

I added this to trigger to my database, thanks a lot!

Collectives™ on Stack Overflow

Combine two identifiers in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related