Thread: Simple way to get missing number
Good morning, May I know is there a simple sql command which could return missing numbers please? For example, t1(id integer) values= 1, 2, 3 .... 5000000 select miss_num(id) from t1 ; Will return: =============== 37, 800, 8001 Thanks a lot! Emi
On Tue, Apr 24, 2012 at 10:15:26AM -0400, Emi Lu wrote: > May I know is there a simple sql command which could return missing > numbers please? > For example, > t1(id integer) > values= 1, 2, 3 .... 5000000 > select miss_num(id) > from t1 ; select generate_series( (select min(id) from t1), (select max(id) from t1)) except select id from t1; Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
On 24 April 2012 16:15, Emi Lu <emilu@encs.concordia.ca> wrote: > Good morning, > > May I know is there a simple sql command which could return missing numbers > please? > > For example, > > t1(id integer) > > values= 1, 2, 3 .... 5000000 > > select miss_num(id) > from t1 ; > > > Will return: > =============== > 37, 800, 8001 > > Thanks a lot! > Emi You can use generate_series() for that, like so: SELECT num AS missing FROM generate_series(1, 5000000) t(num) EXCEPT SELECT id AS missing FROM t1 -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Aha, generate_series, I got it. Thank you very much!! I also tried left join, it seems that left join explain analyze returns faster comparing with except: select num as missing from generate_series(5000, 22323) t(num) left join t1 on (t.num = t1.id) where t1.id is null limit 10; Emi On 04/24/2012 10:31 AM, hubert depesz lubaczewski wrote: > On Tue, Apr 24, 2012 at 10:15:26AM -0400, Emi Lu wrote: >> May I know is there a simple sql command which could return missing >> numbers please? >> For example, >> t1(id integer) >> values= 1, 2, 3 .... 5000000 >> select miss_num(id) >> from t1 ; > > select generate_series( (select min(id) from t1), (select max(id) from > t1)) > except > select id from t1; > > Best regards, > > depesz > -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 emilu@encs.concordia.ca +1 514 848-2424 x5884
On 04/24/2012 07:15 AM, Emi Lu wrote: > Good morning, > > May I know is there a simple sql command which could return missing > numbers please? > > For example, > > t1(id integer) > > values= 1, 2, 3 .... 5000000 > > select miss_num(id) > from t1 ; > > > Will return: > =============== > 37, 800, 8001 > > T select generate_series(1,5000000) except select id from t1; Example select anumber from fooo; anumber --------- 1 3 5 7 9 11 13 15 select generate_series(1,15) except select anumber from fooo order by 1; generate_series ----------------- 2 4 6 8 10 12 14 Cheers, Steve
I got it and thank you very much for everyone's help!! It seems that "left join where is null" is faster comparing with "except". And my final query is: select num as missing from generate_series(5000, #{max_id}) t(num) left join t1 on (t.num = t1.id) where t1.id is null; Emi On 04/24/2012 11:42 AM, Steve Crawford wrote: > On 04/24/2012 07:15 AM, Emi Lu wrote: >> Good morning, >> >> May I know is there a simple sql command which could return missing >> numbers please? >> >> For example, >> >> t1(id integer) >> >> values= 1, 2, 3 .... 5000000 >> >> select miss_num(id) >> from t1 ; >> >> >> Will return: >> =============== >> 37, 800, 8001 >> >> T > > select generate_series(1,5000000) except select id from t1; > > Example > > select anumber from fooo; > anumber > --------- > 1 > 3 > 5 > 7 > 9 > 11 > 13 > 15 > > select generate_series(1,15) except select anumber from fooo order by 1; > generate_series > ----------------- > 2 > 4 > 6 > 8 > 10 > 12 > 14 > > Cheers, > Steve
On 04/24/2012 11:10 AM, Emi Lu wrote: > I got it and thank you very much for everyone's help!! > > It seems that "left join where is null" is faster comparing with > "except". And my final query is: > > select num as missing > from generate_series(5000, #{max_id}) t(num) > left join t1 on (t.num = t1.id) > where t1.id is null; > BTW, there are many options. Two more of them include EXISTS: select allnumbers from generate_series(1,15) as allnumbers where not exists (select 1 from fooo where fooo.anumber=allnumbers.allnumbers); And IN: select allnumbers from generate_series(1,15) as allnumbers where allnumbers not in (select anumber from fooo); They all give you the same result. The "right" choice will depend on the size of your table, how it is indexed, how fully it is populated and even on your version of PostgreSQL. (Apologies for the funky field/table naming.) Cheers, Steve