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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
