Re: Missing numbers

In article <429C7B9B(dot)1040705(at)lamundial(dot)hn>,
josue <josue(at)lamundial(dot)hn> writes:

> Hello list,
> I need to track down the missing check numbers in a serie, table
> contains a column for check numbers and series like this:

> dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
> dbalm-# and doc_tipo='CHE' order by doc_numero;
> doc_numero | doc_ckseriesfk
> ------------+----------------
> 19200 | 856938
> 19201 | 856938
> 19215 | 856938
> 19216 | 856938
> 19219 | 856938

> Missing numbers are:
> from 19202 to 19214 and 19217,19218

> Does anyone knows a way to get that done in SQL or plpgsql, thanks in
> advance

You could use something like that:

SELECT g.num
FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
(SELECT max(doc_numero) FROM bdocs)) AS g(num)
LEFT JOIN bdocs ON bdocs.doc_numero = g.num
WHERE bdocs.doc_numero IS NULL