From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Simple way to get missing number
Date: Tue, 24 Apr 2012 20:59:40 +0530
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-686-pae; KDE/4.6.5; i686; ; )
Cc: Andreas Kretschmer <akretschmer@spamfence.net>
References: <4F96B57E.8080306@encs.concordia.ca> <20120424143655.GA7501@tux>
In-Reply-To: <20120424143655.GA7501@tux>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201204242059.40709.raju@linux-delhi.org>

On Tuesday 24 Apr 2012, Andreas Kretschmer wrote:
> Emi Lu <emilu@encs.concordia.ca> 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 ;
> 
> something like
> 
> ,----[  code  ]
> 
> | test=# select * from emi_lu ;
> | 
> |  i
> | 
> | ---
> | 
> |  1
> |  2
> |  3
> |  5
> |  6
> |  8
> |  9
> | 
> | (7 rows)
> | 
> | Time: 0,246 ms
> | test=*# select * from generate_Series(1,10) s left join emi_lu on
> | (s=emi_lu.i) where i is null;
> | 
> |  s  | i
> | 
> | ----+---
> | 
> |   4 |
> |   7 |
> |  
> |  10 |
> | 
> | (3 rows)

Nice one, but curious about how would this perform if the numbers in 
question extended into 7 figures or more?

Regards,

-- Raj
-- 
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F