From: Emi Lu <emilu@encs.concordia.ca>
To: depesz@depesz.com
Cc: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: Simple way to get missing number
Date: Tue, 24 Apr 2012 11:21:41 -0400
Message-ID: <4F96C505.6050207@encs.concordia.ca> (raw)
In-Reply-To: <20120424143139.GA7555@depesz.com>
References: <4F96B57E.8080306@encs.concordia.ca>
<20120424143139.GA7555@depesz.com>
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@encs.concordia.ca +1 514 848-2424 x5884
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: emilu@encs.concordia.ca, depesz@depesz.com, pgsql-general@postgresql.org
Subject: Re: Simple way to get missing number
In-Reply-To: <4F96C505.6050207@encs.concordia.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox