next available value - postgresql

Question

I have client numbers that are three characters. '001' to '999'. Sometimes there will be gaps that can be reused. I am trying to fill this gaps. So I am searching for a way to find first available gap.

CREATE TABLE co
( co_clno varchar(3));

INSERT INTO co
VALUES 
('001'),
('002'),
('003'),
('005'),
('006'),
('007'),
('008');

The available gap here is '004'

I have tried to first create a list of available number with no sucess:

WITH numbers AS
     (SELECT to_char(generate_series(1,9),'000') num)
SELECT num FROM numbers 
     WHERE num NOT IN(SELECT co_clno FROM co)

The final code should be something like:

WITH numbers AS
     (SELECT to_char(generate_series(1,9),'000') num)
SELECT min(num) FROM numbers 
     WHERE num NOT IN(SELECT co_clno FROM co)

SQLFiddle: http://sqlfiddle.com/#!15/1e48d/1

Thanks in advance for any clue.

Answer 1

select substring(to_char(n,'000') from 2) as num
from generate_series(1,9) gs(n)

except

select co_clno
from co

order by 1
limit 1