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