How about something like:
given a table:
tbl {
pk int4
}
SELECT ((SELECT max(pk) as lub FROM tbl AS t WHERE t.pk<tbl.pk).lub+1)::text || ' to ' || tbl(tbl.pk-1)::text FROM tbl WHERE NOT EXISTS (SELECT pk FROM tbl AS tb WHERE tb.pk=tbl.pk-1)
hth,
- Stuart
P.S. Sorry about the format change the disclaimer adder forces :(
> -----Original Message-----
> From: Madhavi Daroor [mailto:madhavi@zoniac.com]
> Sent: 15 July 2003 09:45
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to find Missing Sequences
>
>
> I have a problem here.....I need to get a list of sequence ids of a
> particular sequence. What I mean by this is....I have a table that has
> say...15 records....and I'm using a sequence for the primary
> key of that
> table. Now the values of the primary key is 1 to 10 for the
> 1st 10 records.
> And then since the sequence has beed skipped or some other
> problem...the
> 11th record begins with 16 as the value for the primary key.
> So the next 5
> records have primarykeys with values 16 to 20 instead of 11 to 15.
>
> Now I want an sql statement that can fetch me a list of all
> these skipped
> sequence id...ie, from 11 to 15. How do I do that? Or Is
> there any other way
> (other than looping through the numbers)to find these values?
>
> Thanx
> Madhavi
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so
> that your
> message can get through to the mailing list cleanly
>
DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.