How do you create an auto-incrementing revision number unique to a key in PGSQL?

Question

Assuming I have the following tables.

PARENT: PARENT_ID serial, DESCRIPTION character varying(50)

CHILD: PARENT_ID integer, CHILD_ID integer, DESCRIPTION character varying(50)

What I would like to see is each row in CHILD having a CHILD_ID that starts at 1 and increments by 1, unique per PARENT_ID. It would be similar to a revision number. For example..

PARENT_ID 1, CHILD_ID 1
PARENT_ID 1, CHILD_ID 2
PARENT_ID 1, CHILD_ID 3
PARENT_ID 2, CHILD_ID 1
PARENT_ID 3, CHILD_ID 1
PARENT_ID 3, CHILD_ID 2

Is there any way to have the CHILD_ID value assigned automatically, such as a sequence or constraint, only with the ability to reuse a CHILD_ID that has been deleted? The only way I can figure out is something to the effect of this SQL.

INSERT INTO child SELECT parent_id, MAX(child_id)+1, 'description' FROM child WHERE parent_id = :PARENT_ID GROUP BY parent_id

That's a bit of a hack though. I realize that database normalization suggests you should not have one key related to another, but I don't have that option for some other reasons. Any ideas?

EDIT: The title's ugly. If any of you high-scoring folks can think of one that's more accurate, please feel free to change it.

Answer 1

That insert isn't the whole story though. You'll also need to handle deletes to close the gap that was created if you really want the numbers to be contiguous.

My suggestion would be to derive this value as you need it. What determines the order of the number? If it's the date entered into the system then add that date to your table and put your PK over the parent_id and that date, then you can pretty easily come up with the number either through SQL or in the front end as you need it.

Answer 2

You could use an incrementing version number on the parent table and set the child id to that value and increment it. You will probably need to update the parent row and insert the child row in a single transaction.

BEGIN
-- Get and hold onto parent_id and version values.
SELECT PARENT_ID, VERSION FROM PARENT WHERE PARENT_ID = :PARENT_ID;
-- Use the values to insert into the child table
INSERT INTO CHILD (PARENT_ID, CHILD_ID) VALUES (:PARENT_ID, :VERSION);
-- Update the version using an optimistic lock.
UPDATE PARENT SET VERSION = VERSION + 1 WHERE PARENT_ID = :PARENT_ID AND 
                                              VERSION = :VERSION_ID
-- If no rows are updated rollback the transaction and try again.
END

This will ensure the child ids are strictly ascending, but won't reuse id values after a deletion. If you can avoid the constraint of reusing old ids, it will simplify your solution (and the solution will be more efficient). If you have to reuse ids then you have 2 options, firstly the solution you specified above, but on deletion renumbering all of the values that occur after the one you deleted. The other option is to have some sort of function that scans the child ids in order and compares them to a set of sequential numbers and returns the value when the first one is not found. Both of these solutions are more complex and will be slow as you will need to take out a row lock to prevent concurrent updates and either insertions or both insertions and deletions will incur an O(n) penalty.

Answer 3

I'd suggest using:

CHILD: PARENT_ID integer, CHILD_ID serial, DESCRIPTION character varying(50)

When you need to get a desired result:

• You can count rows on client side.

• When selecting rows where PARENT_ID=? you can use temporary sequence.

• In soon to be released Postgresql 8.4 you can use windowing functions like this:

  $ create table child (parent_id integer, child_id serial);
  NOTICE:  CREATE TABLE will create implicit sequence "child_child_id_seq" for serial column "child.child_id"
  CREATE TABLE
  
  
  $ insert into child (parent_id) values (1), (1), (1), (2), (3), (3);
  
  
  $ select * from child;
   parent_id | child_id 
  -----------+----------
           1 |        1
           1 |        2
           1 |        3
           2 |        4
           3 |        5
           3 |        6
  (6 rows)
  
  
  $ select parent_id, row_number() over (partition by parent_id order by child_id) from child;
   parent_id | row_number 
  -----------+------
           1 |          1
           1 |          2
           1 |          3
           2 |          1
           3 |          1
           3 |          2
  (6 rows)
  

It is very fast, easy to implement and will scale very well as there will be no concurrency issues to worry about.