CODEWITHSUNDEEP
databasepostgresqlsequenceidentifier
Jeroen Vermunt
Jeroen Vermunt

Reputation: 880

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

enter image description here

Upvotes: 0

Views: 230

Answers (1)

jian
jian

Reputation: 4867

  • 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;

Upvotes: 1

Related Questions