How to find values missing from a sequence?

Question

I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing.

I want to find the missing lines via c_order_id and documentno.

I tried this but it didn't return the expected result:

select * from
(SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no
FROM adempiere.c_order
ORDER BY documentno, c_order_id) as f

enter image description here

In this example many rows missing: from 'DGPOS2003030' to 'DGPOS2003068'.

How do I write a query to retrieve them?

enter image description here

• Franck White: All rows have same format, i want to get missing rows
• Franck White: I tried code select * from (SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no FROM adempiere.c_order ORDER BY documentno, c_order_id) as f but it is not finich
• Franck White: c_order_id is primary key
• Darwin von Corax: Does the record YD4BC2006008 exist, or are you trying to find the records that don't exist? If that's what you're after, then Colin's answer below should serve.
• Franck White: i want to find record that existed but was deleted like YD4BC2006008 or DGPOS2003030, DGPOS2003031,DGPOS2003032.......DGPOS2003068 for example

Answer 1

A more dynamic approach would be , getting the lowest Number and the highest and building the m missing from there.

If you don't have a leading 2 like in the example 2006004 you need to pad the generated part

CREATE TABLE documents (
 "order_id" VARCHAR(22),
 "documentno" VARCHAR(12)
);
INSERT INTO documents
 ("order_id", "documentno")
VALUES
 ('100001110', 'VD4BC2006004'),
 ('100001114' ,'VD4BC2006005'),
 ('100001135' ,'YD4BC2006006'),
 ('100001166' ,'YD4BC2006007'),
 ('100001215' ,'YD4BC2006009'),
 ('100001256' ,'YD4BC2006010'),
 ('100001289' ,'YD4BC2006011'),
 ('100001332' ,'VD4BC2006013'),
 ('100001334' ,'VD4BC2006014'),
 ('100001432' ,'VD4BC2006015'),
 ('100000616' ,'DGP0S2003028'),
 ('100000617' ,'DGP0S2003029'),
 ('100001034' ,'DGP0S2003069');

WITH cte as (
SELECT LEFT("documentno",5) lpart , MIN(RIGHT("documentno",7)) minpart, MaX(RIGHT("documentno",7)) maxpart FROM documents
groUP BY LEFT("documentno",5))
sELECT lpart || generate_series(minpart::INTEGER, maxpart::INTEGER) as mdocumentno FROM cte
except
select documentno from documents
ORDER bY mdocumentno;

| mdocumentno |
| :----------- |
| DGP0S2003030 |
| DGP0S2003031 |
| DGP0S2003032 |
| DGP0S2003033 |
| DGP0S2003034 |
| DGP0S2003035 |
| DGP0S2003036 |
| DGP0S2003037 |
| DGP0S2003038 |
| DGP0S2003039 |
| DGP0S2003040 |
| DGP0S2003041 |
| DGP0S2003042 |
| DGP0S2003043 |
| DGP0S2003044 |
| DGP0S2003045 |
| DGP0S2003046 |
| DGP0S2003047 |
| DGP0S2003048 |
| DGP0S2003049 |
| DGP0S2003050 |
| DGP0S2003051 |
| DGP0S2003052 |
| DGP0S2003053 |
| DGP0S2003054 |
| DGP0S2003055 |
| DGP0S2003056 |
| DGP0S2003057 |
| DGP0S2003058 |
| DGP0S2003059 |
| DGP0S2003060 |
| DGP0S2003061 |
| DGP0S2003062 |
| DGP0S2003063 |
| DGP0S2003064 |
| DGP0S2003065 |
| DGP0S2003066 |
| DGP0S2003067 |
| DGP0S2003068 |
| VD4BC2006006 |
| VD4BC2006007 |
| VD4BC2006008 |
| VD4BC2006009 |
| VD4BC2006010 |
| VD4BC2006011 |
| VD4BC2006012 |
| YD4BC2006008 |

db<>fiddle here

Answer 2

One easy way is to generate a list of all potential documentnos and remove from that all the ones that exist:

select 'YD4BC' || generate_series(2006004, 2006015) as missing_documentno
except
select documentno from documents;