PostgreSQL sequence based on another column

user_257493 0
Viewed 20215

Lets say I have a table as such:

Column   |     Type    |                        Notes
---------+------------ +----------------------------------------------------------
 id      | integer     | An ID that's FK to some other table
 seq     | integer     | Each ID gets its own seq number
 data    | text        | Just some text, totally irrelevant.

id + seq is a combined key.

What I'd like to see is:

ID  | SEQ   |                        DATA
----+------ +----------------------------------------------
 1  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 2  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.

As you can see, a combination of id and seq is unique.

I'm not sure how to set up my table (or insert statement?) to do this. I'd like to insert id and data, resulting in seq being a sub-sequence dependent on id.

postgresql sql
8 Answers

I had the same need to dynamicaly store a tree-like structure, not to add all IDs at once.
I prefer not use sequence table for each group as there could be thousands of them.
It run in an intensive multi-processing environment, so it has to be race-condition-proof.
Here the insert fonction for the 1st level. Other levels follow the same principle.

Each group as independent non-reusable sequencial IDs, the function receives a group name & sub-group name, gives you the existing ID or creates it & returns the new ID.
I tried a loop to have a single select, but the code is as long & harder to read.

CREATE OR REPLACE FUNCTION getOrInsert(myGroupName TEXT, mySubGroupName TEXT)
  RETURNS INT AS
$BODY$
DECLARE
   myId INT;
BEGIN -- 1st try to get it if it already exists
   SELECT id INTO myId FROM myTable
      WHERE groupName=myGroupName AND subGroupName=mySubGroupName;
   IF NOT FOUND THEN
      -- Only 1 session can get it but others can read
      LOCK TABLE myTable IN SHARE ROW EXCLUSIVE MODE; 
      -- 2nd try in case of race condition
      SELECT id INTO myId FROM myTable
         WHERE groupName=myGroupName AND subGroupName=mySubGroupName;
      IF NOT FOUND THEN -- Doesn't exist. Get next ID for this group.
         SELECT COALESCE(MAX(id), 0)+1 INTO myId FROM myTable
            WHERE groupName=myGroupName;
         INSERT INTO myTable (groupName, id, subGroupName)
            VALUES (myGroupName, myId, mySubGroupName);
      END IF;
   END IF;
   RETURN myId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

To try it: 

CREATE TABLE myTable (GroupName TEXT, SubGroupName TEXT, id INT);
SELECT getOrInsert('groupA', 'subgroupX'); -- Returns 1
...
SELECT * FROM myTable;
 groupname | subgroupname | id 
-----------+--------------+----
 groupA    | subgroupX    |  1
 groupA    | subgroupY    |  2
 groupA    | subgroupZ    |  3
 groupB    | subgroupY    |  1
user_14310790
Related