how to generate a sequence of values with differents formats in PostgreSQL I want to find rows missing from a sequence of values in my table with differents formats.
For an example, look at this.
I want to find the missing lines via c_order_id and documentno. the one with '_p' at the end, and the other without '_p', just the numbers.
CREATE TABLE documents (
  &quot;order_id&quot; VARCHAR(22),
  &quot;documentno&quot; VARCHAR(20)
);
INSERT INTO documents
  (&quot;order_id&quot;, &quot;documentno&quot;)
VALUES
  ('100001120', 'PROFS/2021/02050_P'),
  ('100001125' ,'PROFS/2021/02055_p'),
  ('100001127', 'PROFS/2021/02056'),
  ('100001135' ,'PROFS/2021/02060'),
  ('100001139' ,'AGB/2021/02040'),
  ('100001172' ,'AGB/2021/02047')


Query used:
WITH cte as (
SELECT LEFT(&quot;documentno&quot;,12) lpart , MIN(RIGHT(&quot;documentno&quot;,5)) minpart, MaX(RIGHT(&quot;documentno&quot;,5)) maxpart  FROM documents
groUP BY LEFT(&quot;documentno&quot;,12))
sELECT lpart || generate_series(minpart::INTEGER, maxpart::INTEGER)  as missing_documentno FROM cte
except
select documentno from documents
ORDER bY missing_documentno;

This query generates
ERROR:  invalid input syntax for type integer: &quot;055_p&quot;
You can use regexp_replace() to cleanup the numbers so that you can cast it to an integer value.
with cte as (
  select left(documentno,12) lpart, 
         regexp_replace(split_part(documentno, '/', 3), '[^0-9]', '', 'g')::int as num
  from documents
), minmax as (
  select lpart, min(num) minpart, max(num) maxpart
  from cte
  group by lpart
)
select m.lpart, t.missing_document_no
from minmax m
  cross join generate_series(m.minpart, m.maxpart) as t(missing_document_no)
order by t.missing_document_no

I have split up the &quot;cleaning&quot; of the data and the group into two CTEs to make the steps a bit more readable. Given your sample data, this returns:
lpart        | missing_document_no
-------------+--------------------
PROFS/2021/0 |                2050
PROFS/2021/0 |                2051
PROFS/2021/0 |                2052
PROFS/2021/0 |                2053
PROFS/2021/0 |                2054
PROFS/2021/0 |                2055
PROFS/2021/0 |                2056
PROFS/2021/0 |                2057
PROFS/2021/0 |                2058
PROFS/2021/0 |                2059
PROFS/2021/0 |                2060

To find the missing values, you can extend the final select to:
with (
...
), minmax as (
...
)
select lpart||t.doc_no as missing_doc_no
from minmax m
  cross join generate_series(minpart, maxpart) as t(doc_no) 
where not exists (select * 
                  from documents d
                  where regexp_replace(split_part(d.documentno, '/', 3), '[^0-9]', '', 'g')::int = t.doc_no)