CODEWITHSUNDEEP
sqlpostgresql
sibert
sibert

Reputation: 2228

next available value - postgresql

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.

Upvotes: 3

Views: 249

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use lead() to find where the gap starts:

select n.*
from (select n.*, lead(co_clno) over (order by co_clno) as next_num
      from co n
     ) n
where next_num is null or 
      n. co_clno::int <> (next_num::int) - 1
order by co_clno
limit 1;

You can get the next value with:

select to_char((n. co_clno::int) + 1, '000')
from (select n.*, lead(co_clno) over (order by co_clno) as next_num
      from co n
     ) n
where next_num is null or 
      n. co_clno::int <> next_num::int
order by co_clno
limit 1;

The only problem with this is that it won't get the first value if missing. Hmmm . . .

select (case when first_num <> '001' then '001'
             else min(to_char((n. co_clno::int) + 1, '000'))
        end)
from (select n.*, lead(co_clno) over (order by co_clno) as next_num,
             min(co_clno) over () as first_num
      from co n
     ) n
where next_num is null or 
      n. co_clno::int <> (next_num::int) - 1
group by first_num;

Upvotes: 1

Abelisto
Abelisto

Reputation: 15614

@GordonLinoff's idea is right, but I not sure about realization.

So here is my version of the query:

with 
  t(n) as (
    values
      ('001'),
      ('002'),
      ('005'),
      ('003'),
      ('006'),
      ('009'),
      ('010'),
      ('012')),
  t1 as (
    select
      n,
      lag(n, -1) over (order by n)::int - n::int - 1 as cnt
    from t)
select 
  to_char(generate_series(n::int+1, n::int+cnt), '000') as gap
from
  t1
where 
  cnt > 0;

And result is:

 gap  
------
  004
  007
  008
  011
(4 rows)

To solve problem with missing first value just use

select '000' union all <your data>

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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

Upvotes: 3

Related Questions