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.

Clodoaldo Neto · Accepted Answer · 2016-02-12 13:59:50Z

3

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

answered Feb 12, 2016 at 13:59

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sibert Over a year ago

Thank you! Simple and clean. It worked like expected.

Abelisto · Accepted Answer · 2016-02-12 14:17:23Z

1

@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>

edited Feb 12, 2016 at 14:17

answered Feb 12, 2016 at 14:02

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2016-02-13 12:59:43Z

1

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;

edited Feb 13, 2016 at 12:59

answered Feb 12, 2016 at 13:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

sibert Over a year ago

It will find '009' and not the first available number '004'. sqlfiddle.com/#!15/1e48d/10

sibert Over a year ago

The second query will give '002' as available. sqlfiddle.com/#!15/1e48d/11

Gordon Linoff Over a year ago

@sibert . . . The query works fine. Your SQL Fiddle is wrong: sqlfiddle.com/#!15/1e48d/10. I don't know where your CTE came from.

sibert Over a year ago

Without CTE (AFAIK): ERROR: relation "numbers" does not exist?

Gordon Linoff Over a year ago

@sibert . . . I just used the naming conventions from your select . . . I didn't realize the table had other names.

Collectives™ on Stack Overflow

next available value - postgresql

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related