Finding the lowest available number in PostgreSQL database column

Question

i have this PostgreSQL table

---------------------------------
|  id   |   tag   |  name  |
---------------------------------
|  1    |    1    |  name1 |
|  3    |    3    |  name3 |
|  4    |    4    |  name4 |
|  6    |    6    |  name6 |
|  7    |    7    |  name7 |

this table hold customer numbers : id is auto increment primary key. tag is a unique integer ,it act as an easy way to find a person in the database . because of some practical issues ,persons regularly leave the area and never comes again. total number of customers never exceed 200 at any given time but there is relatively rapid turn over. because tag number is entered manually in the program and has to be memorized by customers ,i want to keep the tag number very small so when i delete one customer i want to reuse its tag number.

in the table above customer with id #2 and 5 was deleted some time ago. now i am about to insert a new customer , and want the database to find for me the lowest tag number available (in this case it will be #2) so that i can reuse it for the next customer.

how ?

latest NpgSQL 3.0.5 and PostgreSQL 9.5

Possible duplicate: http://stackoverflow.com/questions/174532/how-to-find-holes-in-a-table — jdabrowski, Mar 08 '16 at 12:59

score 2 · Answer 1 · answered Mar 08 '16 at 13:25

With ROW_NUMBER(), you should be able to do it fairly quickly;

SELECT rn tag                                                   
FROM (SELECT tag, ROW_NUMBER() OVER (ORDER BY tag) rn FROM Table1) z 
WHERE rn != tag                                                                 
ORDER BY rn OFFSET 0 ROW FETCH NEXT 1 ROW ONLY;

It basically gets the tags in numeric order along with its row number, and returns the first row number that does not match its row tag (ie if tags 1,2,4 exist, they'll get row number 1,2,3 and 3 is the first row number that does not match its tag, 4)

An SQLfiddle to test with.

A word of warning though, set the tag unique in the database and be prepared to retry, if you have 2 people adding customers at the same time, both people will get the lowest tag back but only one will be allowed to set it on their customer. Otherwise two customers may end up with the same tag, which is probably bad.

that worked ,i had to change "OFFSET 0" to "OFFSET 1" because tags start at 0 and row numbers start at 1 — EKanadily, Mar 08 '16 at 19:55
@docesam I think `...(ORDER BY tag) - 1 rn FROM...` may be better in that case. — Joachim Isaksson, Mar 08 '16 at 19:58
@docesam See [this example](http://sqlfiddle.com/#!15/a967b/1) you can test with. OFFSET 1 does not give a correct result. — Joachim Isaksson, Mar 09 '16 at 08:38

Clodoaldo Neto · Answer 2 · 2016-03-08T14:44:58.997

1

select tag
from (
    select generate_series (1, (select max(tag) from customer)) as tag
    except
    select tag from customer
) s
order by tag
limit 1

SQL Fiddle

edited Mar 08 '16 at 14:44

answered Mar 08 '16 at 14:33

Clodoaldo Neto

118,695
26
233
260

Finding the lowest available number in PostgreSQL database column

2 Answers2