gap in sequence numbers

From: "Picavet Vincent" <Vincent(dot)Picavet(at)mediapost(dot)fr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: gap in sequence numbers
Date: 2009-09-18 14:00:41
Message-ID: EB18254270D1FD429047C987937D4A120496B7A0@s92e07497.ad.mediapost.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
Please consider the following query.
The reftable inner query generates a table with 11 elements, 7 of them
having the groupkey 1, and the 4 others the groupkey 2. This set is then
grouped by the groupkey field, and after that a generate_series and a
sequence are used to create an ordered id.

My question is : why do the ids created by the sequence include a gap of
1 between the two groups ?
ie, why the id 5 is not present in the result set ?

I know I already ran into this kind of problem, and solved it by using
nextval outside of the query, but i'd like to understand the real reason
of this behaviour.

create sequence temp_seq2 start with 1;

select
nextval('temp_seq2') as id,
groupkey,
generate_series(1, groupnb) as rang
from (
select
groupkey
, count(*) as groupnb
from (
select
id
, case
when id <= 7 then 1
else 2
end as groupkey
from
generate_series(1, 11) as id
) as reftable
group by
groupkey
) as foo;

drop sequence if exists temp_seq2;

Thanks for your help,
Vincent

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-09-18 14:16:03 Re: Does PG cache results of an aggregate function, (and results of non-volatile functions)?
Previous Message Merlin Moncure 2009-09-18 13:08:26 Re: Does PG cache results of an aggregate function, (and results of non-volatile functions)?