pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple way to get missing number
10+ messages / 6 participants
[nested] [flat]
* Simple way to get missing number
@ 2012-04-24 14:15 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 4 replies; 10+ messages in thread
From: Emi Lu @ 2012-04-24 14:15 UTC (permalink / raw)
To: pgsql-sql; " <pgsql-general@postgresql.org>
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
Thanks a lot!
Emi
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Simple way to get missing number
@ 2012-04-24 14:31 hubert depesz lubaczewski <depesz@depesz.com>
parent: Emi Lu <emilu@encs.concordia.ca>
3 siblings, 1 reply; 10+ messages in thread
From: hubert depesz lubaczewski @ 2012-04-24 14:31 UTC (permalink / raw)
To: Emi Lu <emilu@encs.concordia.ca>; +Cc: pgsql-sql; pgsql-general@postgresql.org
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Simple way to get missing number
@ 2012-04-24 14:36 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Emi Lu <emilu@encs.concordia.ca>
3 siblings, 1 reply; 10+ messages in thread
From: Andreas Kretschmer @ 2012-04-24 14:36 UTC (permalink / raw)
To: pgsql-sql
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)
`----
this?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Simple way to get missing number
@ 2012-04-24 14:41 Alban Hertroys <haramrae@gmail.com>
parent: Emi Lu <emilu@encs.concordia.ca>
3 siblings, 0 replies; 10+ messages in thread
From: Alban Hertroys @ 2012-04-24 14:41 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql; " <pgsql-general@postgresql.org>
On 24 April 2012 16:15, 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 ;
>
>
> Will return:
> ===============
> 37, 800, 8001
>
> Thanks a lot!
> Emi
You can use generate_series() for that, like so:
SELECT num AS missing FROM generate_series(1, 5000000) t(num)
EXCEPT
SELECT id AS missing FROM t1
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Simple way to get missing number
@ 2012-04-24 15:21 Emi Lu <emilu@encs.concordia.ca>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 10+ messages in thread
From: Emi Lu @ 2012-04-24 15:21 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-sql; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Simple way to get missing number
@ 2012-04-24 15:29 Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 10+ messages in thread
From: Raj Mathur (राज माथुर) @ 2012-04-24 15:29 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Simple way to get missing number
@ 2012-04-24 15:42 Steve Crawford <scrawford@pinpointresearch.com>
parent: Emi Lu <emilu@encs.concordia.ca>
3 siblings, 1 reply; 10+ messages in thread
From: Steve Crawford @ 2012-04-24 15:42 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql; " <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Simple way to get missing number
@ 2012-04-24 16:06 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
0 siblings, 0 replies; 10+ messages in thread
From: Andreas Kretschmer @ 2012-04-24 16:06 UTC (permalink / raw)
To: pgsql-sql
Raj Mathur (राज माथुर) <raju@linux-delhi.org> wrote:
>
> Nice one, but curious about how would this perform if the numbers in
> question extended into 7 figures or more?
<mode=David Fetter>
TIAS
(Try It And See)
</mode>
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Simple way to get missing number
@ 2012-04-24 18:10 Emi Lu <emilu@encs.concordia.ca>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 10+ messages in thread
From: Emi Lu @ 2012-04-24 18:10 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-sql; " <pgsql-general@postgresql.org>
I got it and thank you very much for everyone's help!!
It seems that "left join where is null" is faster comparing with
"except". And my final query is:
select num as missing
from generate_series(5000, #{max_id}) t(num)
left join t1 on (t.num = t1.id)
where t1.id is null;
Emi
On 04/24/2012 11:42 AM, Steve Crawford wrote:
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Simple way to get missing number
@ 2012-04-24 18:56 Steve Crawford <scrawford@pinpointresearch.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 0 replies; 10+ messages in thread
From: Steve Crawford @ 2012-04-24 18:56 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql; " <pgsql-general@postgresql.org>
On 04/24/2012 11:10 AM, Emi Lu wrote:
> I got it and thank you very much for everyone's help!!
>
> It seems that "left join where is null" is faster comparing with
> "except". And my final query is:
>
> select num as missing
> from generate_series(5000, #{max_id}) t(num)
> left join t1 on (t.num = t1.id)
> where t1.id is null;
>
BTW, there are many options. Two more of them include EXISTS:
select allnumbers from generate_series(1,15) as allnumbers where not
exists (select 1 from fooo where fooo.anumber=allnumbers.allnumbers);
And IN:
select allnumbers from generate_series(1,15) as allnumbers where
allnumbers not in (select anumber from fooo);
They all give you the same result. The "right" choice will depend on the
size of your table, how it is indexed, how fully it is populated and
even on your version of PostgreSQL. (Apologies for the funky field/table
naming.)
Cheers,
Steve
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2012-04-24 18:56 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-24 14:15 Simple way to get missing number Emi Lu <emilu@encs.concordia.ca>
2012-04-24 14:31 ` hubert depesz lubaczewski <depesz@depesz.com>
2012-04-24 15:21 ` Emi Lu <emilu@encs.concordia.ca>
2012-04-24 14:36 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-04-24 15:29 ` Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) <raju@linux-delhi.org>
2012-04-24 16:06 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-04-24 14:41 ` Alban Hertroys <haramrae@gmail.com>
2012-04-24 15:42 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-04-24 18:10 ` Emi Lu <emilu@encs.concordia.ca>
2012-04-24 18:56 ` Steve Crawford <scrawford@pinpointresearch.com>
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