Subject RE: [firebird-support] Firebird get the list with all available id
Author Svein Erling Tysvær
>> In a table I have records with id's 2,4,5,8. How can I receive a list
>> with values 1,3,6,7. I have tried in this way
>> SELECT + 1
>> FROM table t1
>> FROM table t2
>> WHERE = + 1
>> )
>> but it's not working correctly. It doesn't bring all available positions.
>The SQL looks OK and should work.
>What is the datatype of the ID column of the table?

No Sean, that would return 3 and 6, 1 and 7 wouldn't be returned by such a statement (1 because that is less than the lowest value in table and 7 because 6 isn't in the table). The best way to do something like this, is (in my opinion) to use EXECUTE BLOCK (it is possible to do similar things using a recursive CTE, but that's more complex, slower and quickly fails due to too many recursions):

execute block returns (id integer) as
declare variable idmax integer;
id = 1;
select max(id) from table into :idmax;
while (id < idmax) do
if (not exists(select * from table where id = :id)) then suspend;
id = id + 1;

Though, of course, if ID is a primary key, it sounds like a very bad idea to use this logic to find free keys (if two users simultaneously used this, they risk getting the same value back).