Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 9037A860809;
	Tue, 24 Apr 2012 12:42:48 -0300 (ADT)
Received: from cerberus.pinpointresearch.com ([66.7.238.130]
	helo=polaris.pinpointresearch.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scrawford@pinpointresearch.com>)
	id 1SMht5-0002hR-6Z; Tue, 24 Apr 2012 15:42:47 +0000
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id B1D91E00EC82;
	Tue, 24 Apr 2012 08:42:29 -0700 (PDT)
Message-ID: <4F96C9E9.7000005@pinpointresearch.com>
Date: Tue, 24 Apr 2012 08:42:33 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: emilu@encs.concordia.ca
CC: pgsql-sql@postgresql.org,
	"pgsql-general@postgresql.org >> \"pgsql-general@postgresql.org\""
	<pgsql-general@postgresql.org>
Subject: Re: [SQL] Simple way to get missing number
References: <4F96B57E.8080306@encs.concordia.ca>
In-Reply-To: <4F96B57E.8080306@encs.concordia.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201204/550
X-Sequence-Number: 186863

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