I'm using PostgreSQL 8.1.17, and I have a table with account numbers. The acceptable range for an account number is a number between 1 and 1,000,000 (a six digit number). The column "acctnum" contains the account number. Selecting all the numbers in use is easy (SELECT acctnum FROM tbl_acct_numbers ORDER BY acctnum). What I would like to do is select all the numbers in the acceptable range that are not in use, that is, they aren't found in any rows within the column acctnum.
+2
A:
select *
from generate_series(1, 1000000) as acctnum
where acctnum not in (select acctnum from tbl_acct_numbers);
Alex Howansky
2010-09-29 17:03:06
Alex, Your query would give all the account numbers in tbl_account_numbers which are not in the 1-1000000 range.
Rajesh
2010-09-29 17:08:24
Whoops @Rajesh, you're right -- I got the two sides of the query reversed, thanks for pointing that out. (Edited.)
Alex Howansky
2010-09-29 17:30:21
A:
You can generate the series of numbers from 1-1,000,000 and then MINUS the results of your query.
select * from generate_series(1,1000000)
EXCEPT
SELECT acctnum FROM tbl_acct_numbers;
Rajesh
2010-09-29 17:04:00
Thanks for pointing that out. MINUS is the Oracle version. The quivalent in postGreSQl is "EXCEPT" http://www.faqs.org/docs/ppbook/x5802.htm#USINGEXCEPT
Rajesh
2010-09-29 18:29:32
+1
A:
SELECT
new_number
FROM
generate_series(1, 1000000) AS new_number
LEFT JOIN tbl_acct_numbers ON new_number = acctnum
WHERE
acctnum IS NULL;
Frank Heikens
2010-09-29 17:07:53
This query took 519 seconds, but actually worked unlike some of the others on this page.
Pyrite
2010-09-29 17:31:43
Do you have an index on acctnum? And do you realy need ALL numbers or just 10 or 100? If so, use a LIMIT.
Frank Heikens
2010-09-29 17:43:31
I actually only need 9 numbers, but in a certain way. I have ranges of numbers, and I need the next available number for each range. The ranges are 100,000 - 200,000, 200,000 - 300,000, etc. So I could execute this query with LIMIT 1 for each range 9 times, which is what I did. I put this query in a function passing in the lower/upper bounds, and called it 9 times, and there I have it. Thanks!
Pyrite
2010-09-29 19:08:41
+1
A:
Are you sure you want to do this? I assume the reason you want to find unused numbers is so that you can use them.
But consider that the numbers might not be there because someone did use them in the past, and deleted the row from the database. So if you find that account number and re-use it for a new account, you could be assigning a number that was previously used and was deleted for a reason.
If there are any historical documents in other systems that reference that account number, they might mistakenly be associated with the new account using the same number.
However, as long as you have considered this, you can find unused id's in the following way:
SELECT t1.acctnum-1 AS unused_acctnum
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t2.acctnum = t1.acctnum-1
WHERE t2.acctnum IS NULL;
Granted, this doesn't find all the unused acctnums, only a set of them that are 1 less than a number that's in use. But that might give you enough to work with.
The answer from @Alex Howansky does give you all unused acctnums, but it may return a large set of rows.
Bill Karwin
2010-09-29 17:08:10
Your reasoning is correct, but on the application side, I am taking care not to use numbers that have been used before, so you are only seeing half of the picture. This query did not work for me however because my col type for acctnum is not integer but character varying. The error message says to use explicit type casts, but I don't know where to put them in the query (never done type casting in sql).
Pyrite
2010-09-29 17:43:06