On 24 April 2012 16:15, Emi Lu <emilu@xxxxxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
