Skip site navigation (1) Skip section navigation (2)

Re: Simple way to get missing number

From: Emi Lu <emilu(at)encs(dot)concordia(dot)ca>
To: depesz(at)depesz(dot)com
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: Simple way to get missing number
Date: 2012-04-24 15:21:41
Message-ID: 4F96C505.6050207@encs.concordia.ca (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
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(at)encs(dot)concordia(dot)ca        +1 514 848-2424 x5884

In response to

pgsql-sql by date

Next:From: Raj Mathur ( राज =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)Date: 2012-04-24 15:29:40
Subject: Re: Simple way to get missing number
Previous:From: Alban HertroysDate: 2012-04-24 14:41:26
Subject: Re: Simple way to get missing number

pgsql-general by date

Next:From: Andreas KretschmerDate: 2012-04-24 15:28:53
Subject: Re: how robust are custom dumps?
Previous:From: rihadDate: 2012-04-24 15:21:18
Subject: Re: Fractions of seconds in timestamps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group