Re: How to find Missing Sequences - Mailing list pgsql-general

From greg@turnstep.com
Subject Re: How to find Missing Sequences
Date
Msg-id 5c0f83bf47618a8c57db69cfc92a2318@biglumber.com
Whole thread Raw
In response to How to find Missing Sequences  ("Madhavi Daroor" <madhavi@zoniac.com>)
List pgsql-general
-----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-----



pgsql-general by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: migrating data from 7.3.x down to 7.2.x
Next
From: Scott Cain
Date:
Subject: insert bug