Wagonlicious

Recently, I needed to write a function which would find the next available value in a sequence of user IDs.  My data is in a PostgreSQL table called agtab within a scheme called accounts.  There is a column named uid and a column named gid for each account.  The table was populated using the aggregate of several “legacy” systems, and the existing IDs are not contiguous.  I need to have a mechanism which can quickly find the smallest unused ID.  The brute force method would be to start at the lowest possible value and just start incrementing a value until I found one that’s unused.  But that’s inelegant and won’t scale real well.  I also need to skip over a chunk in the middle of the range which is allocated for another use.  I ended up with the idea of calculating the difference of the list of all IDs and the same list with all IDs incremented by one; the lowest value in “ids plus one” which isn’t also in “ids” is the lowest free ID.  Enter the “EXCEPT” clause.

Read the rest of this entry »