Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 8FAE6145EA46;
	Tue, 24 Apr 2012 11:31:59 -0300 (ADT)
Received: from andy.depesz.com ([88.198.47.100] helo=depesz.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <depesz@depesz.com>)
	id 1SMgmX-0007Rs-Qk; Tue, 24 Apr 2012 14:31:58 +0000
Received: from andy.depesz.com ([88.198.47.100] helo=depesz.com)
	by depesz.com with esmtpa (Exim 4.77)
	(envelope-from <depesz@depesz.com>)
	id 1SMgmJ-0002YH-GA; Tue, 24 Apr 2012 16:31:39 +0200
Date: Tue, 24 Apr 2012 16:31:39 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
To: Emi Lu <emilu@encs.concordia.ca>
Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Simple way to get missing number
Message-ID: <20120424143139.GA7555@depesz.com>
Reply-To: depesz@depesz.com
References: <4F96B57E.8080306@encs.concordia.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <4F96B57E.8080306@encs.concordia.ca>
User-Agent: Mutt/1.5.21 (2010-09-15)
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201204/542
X-Sequence-Number: 186855

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/