Thread: 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

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Now I want an sql statement that can fetch me a list of all these skipped
> sequence id...ie, from 11 to 15.

SELECT
  CASE WHEN start = finish THEN start::text
       ELSE start || '-' || finish END AS gap
FROM (
  SELECT hole.id AS start, MIN(f.id)-1 AS finish
  FROM fred f, (
    SELECT id+1 AS id FROM fred f1
    WHERE NOT EXISTS (SELECT 1 FROM fred f2 WHERE f2.id = f1.id+1)
  ) AS hole
  WHERE f.id > hole.id
  GROUP BY 1
) AS wilma;


Here is the table I used to test with:

CREATE TABLE fred (
  id INTEGER PRIMARY KEY
);

INSERT INTO fred VALUES (1);
INSERT INTO fred VALUES (2);
INSERT INTO fred VALUES (3);
INSERT INTO fred VALUES (5);
INSERT INTO fred VALUES (6);
INSERT INTO fred VALUES (7);
INSERT INTO fred VALUES (10);
INSERT INTO fred VALUES (16);
INSERT INTO fred VALUES (18);
INSERT INTO fred VALUES (30);


Of course, if skipped numbers are that important, you may want to use something
other than a sequence...


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307150953

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/FAhbvJuQZxSWSsgRAgbkAJ9e4UfZ0Q/5tm06tz+TBwRvJ5Z3rACglkjU
Nkus+/x16JBtv1avzJgIEw0=
=u0Hf
-----END PGP SIGNATURE-----