Postgresql - Create sequence with order AAB, AA1, AA9, ABA

aru
October 22, 2022
243 views
2 votes
3 Answers

I am trying to create sequence that stored in db.
So just only with one service call I should get new sequence by order like AAB.
Next call should return AAC, next AAD…. AA9, ABA…
I tried to create three number sequences 0<=first_seq<36, also like this second_seq, third_seq.
I am using spring hibernate, postgresql.

Answers

Try this.

static final String DIGITS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
static final int LENGTH = 3;
static int SEED = 0;

public static String nextSequence() {
    int size = DIGITS.length();
    char[] buffer = new char[LENGTH];
    for (int n = ++SEED, i = LENGTH - 1; i >= 0; --i, n /= size)
        buffer[i] = DIGITS.charAt(n % size);
    return new String(buffer);
}

and

for (int i = 0; i < 8; ++i)
    System.out.println(nextSequence());

output

AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI

Well your question is ambiguous, you should indicate wanting a Java or a Postgres solution at a minimum. The following presents a fully generic Postgres solution given only the previous sequence and a string with the ordered set of digits. It creates 2 CTEs the first defines a the digits then the second ‘builds’ a working set of variables. Finally the main sql builds the next sequence. This is handled is a single statement, nested into a sql function. (see demo)

create or replace function strange_sequence_nextval(current_seq_in text)
     returns text 
    language sql
    immutable
as $$
   with avail_set (k_ent) as ( values('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') )
      , parms_set (k_ent, k_len, l_pos, l_len, l_val) as 
        (select k_ent
              , length(k_ent) 
              , position(right(current_seq_in, 1) in (k_ent) )
              , length(current_seq_in)
              , left(current_seq_in, length(current_seq_in)-1)
           from avail_set 
        ) 
    select case when current_seq_in is  null then left(k_ent,1) 
                when l_pos = k_len           then concat( strange_sequence_nextval(l_val), left(k_ent,1)) 
                when l_len < 2               then substr( k_ent, l_pos+1, 1)
                else                              concat( l_val, substr( k_ent, l_pos+1, 1)) 
           end
      from parms_set;
$$;

NOTE: As a SQL function it can be extracted and run as an independent statement. You will just need to pass the current sequence as a parameter.

EDIT: Reply to Vérace. I did not get the generate the entire sequence because as stated Next call should return AAC, next (call) AAD …. Further the sequence from ... AA9, ABA... has no ending. But without reworking the function you could use it within a recursive query.

with recursive seq_gen(seq, gen_no, stop_at) as
     ( select 'AAA', 1, 100         -- or any other random point
       union all 
       select strange_sequence_nextval(seq), gen_no+1, stop_at 
         from seq_gen 
        where gen_no < stop_at        
    )
select seq
  from seq_gen;

Firstly, I did this (all of the code below is available here).

SELECT
  SUBSTRING('ABCDE123', s.i, 1) AS c1,
  SUBSTRING('ABCDE123', t.j, 1) AS c2,
  SUBSTRING('ABCDE123', u.k, 1) AS c3
FROM
  GENERATE_SERIES(1, 8) AS s(i),
  GENERATE_SERIES(1, 8) AS t(j),
  GENERATE_SERIES(1, 8) AS u(k)
ORDER BY c1, c2, c3
LIMIT 10;

Result:

c1  c2  c3
1   1   1
1   1   2
1   1   3
1   1   A
1   1   B
...
... snipped for brevity
...

But, you want it sorted AAA, AAB… but 1 is before A in the ASCII table, so we have to do this:

WITH cte1 AS
(
  SELECT

    SUBSTRING('ABCDE123', s.i, 1) AS c1,
    SUBSTRING('ABCDE123', t.j, 1) AS c2,
    SUBSTRING('ABCDE123', u.k, 1) AS c3
  
  FROM
    GENERATE_SERIES(1, 8) AS s(i),
    GENERATE_SERIES(1, 8) AS t(j),
    GENERATE_SERIES(1, 8) AS u(k)
)
SELECT 
  c1 || c2 || c3 AS sequence
FROM
  cte1
ORDER BY 

  CASE
    WHEN ASCII(c1) < 58 THEN ASCII(c1) + 45 --  45 is arbitrary, as long as it gets
    ELSE ASCII(c1)                          --  (ASCII(c1) + 45 > 90). Check an ASCII table!
  END,
  
  CASE
    WHEN ASCII(c2) < 58 THEN ASCII(c2) + 45
    ELSE ASCII(c2)
  END,
  
  CASE
    WHEN ASCII(c3) < 58 THEN ASCII(c3) + 45
    ELSE ASCII(c3)
  END
LIMIT 10;

Result:

sequence
AAA
AAB
AAC
AAD
AAE
AA1
...
... snipped for brevity
...

So, now we have the correct sort order.

We can put all of this in a function as follows:

CREATE OR REPLACE FUNCTION seq(str TEXT)
RETURNS SETOF TEXT
LANGUAGE SQL IMMUTABLE
AS
$$
  WITH cte1 AS
  (
    SELECT

      SUBSTRING($1, s.i, 1) AS c1,
      SUBSTRING($1, t.j, 1) AS c2,
      SUBSTRING($1, u.k, 1) AS c3
  
    FROM
      GENERATE_SERIES(1, LENGTH($1)) AS s(i),
      GENERATE_SERIES(1, LENGTH($1)) AS t(j),
      GENERATE_SERIES(1, LENGTH($1)) AS u(k)  
  )

  SELECT 
    c1 || c2 || c3 AS sequence
  FROM
  cte1
  ORDER BY 

    CASE
      WHEN ASCII(c1) < 58 THEN ASCII(c1) + 45  -- <<= 45 is arbitrary, as long as it gets
      ELSE ASCII(c1)                           --     it above 90. Check an ASCII table!
    END,
  
    CASE
      WHEN ASCII(c2) < 58 THEN ASCII(c2) + 45
      ELSE ASCII(c2)
    END,
  
    CASE
      WHEN ASCII(c3) < 58 THEN ASCII(c3) + 45
      ELSE ASCII(c3)
    END
$$;

and the run:

SELECT
  seq('ABC1')
  LIMIT 10;

Result:

seq
AAA
AAB
AAC
AA1
ABA
ABB
...
... snipped for brevity
...

With thanks to @Belayer’s code for the inspiration on the function!

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Create sequence with order AAB, AA1, AA9, ABA

Answers