Postgres - How to create an alphanumeric sequence like AAAA0000 and so on

Question

I want to create an alphanumeric sequence like this:

AAAA0000
AAAA0001
AAAA0002
AAAA0003
.
.
.
AAAA9999
AAAB0000
AAAB0001

and so on

I have created this store procedure to do that but its too slow:

CREATE OR REPLACE FUNCTION public.fn_batch_seq()
  RETURNS text
  LANGUAGE plpgsql
AS
$body$
DECLARE
  v_sequence TEXT := '';
  v_next_sequence TEXT := '';
  v_existing_id BIGINT := 0;
BEGIN

  /*
  *  VARCHAR BATCH SEQUENCE FOR SIMCARDS
  */
  SELECT "sequence" FROM batch_sequence WHERE id = 1 INTO v_sequence;
  IF v_sequence = '' THEN
    RAISE NOTICE 'Error - No existe ningun registro en batch_sequence almacenado';
    RETURN -500;
  END IF;
  SELECT perl_increment(v_sequence) INTO v_next_sequence;

  IF v_next_sequence = '' THEN
    RAISE NOTICE 'Error - La siguiente secuencia generada devolvio null o vacio';
    RETURN -500;
  END IF;


  UPDATE batch_sequence SET "sequence" = v_next_sequence WHERE id = 1;
  RETURN v_next_sequence;

  EXCEPTION WHEN OTHERS THEN
  /*
  * Other errors
  */
  RAISE NOTICE 'Error General - Posibles causas: No existe la tabla batch_sequence o no existe ningun registro en la misma';
  RETURN -500;

END;
$body$
  VOLATILE
  COST 100;

This procedure uses a table to store the sequence:

CREATE TABLE batch_sequence
(
   id        serial   NOT NULL,
   sequence  text     DEFAULT 'AAAA0000'::text NOT NULL
);

-- Column id is associated with sequence public.batch_sequence_id_seq

ALTER TABLE batch_sequence
   ADD CONSTRAINT batch_sequence_pk
   PRIMARY KEY (id);

And for increment the sequence I use a perl procedure:

CREATE OR REPLACE FUNCTION public.perl_increment(text)
  RETURNS text
  LANGUAGE plperl
AS
$body$
my ($x) = @_;
    if (not defined $x) {
        return undef;
    }
    ++$x;
$body$
  VOLATILE
  COST 100;

It works very slow with large amount of data, because it must be executed before inserting every single row. Is there another way to do that with or without Perl?

Please help.

Simply use usual sequences. Create function that gets its underlying sequence nextval and then transcodes integer value to your alphanumeric one. — Akzhan Abdulin
– Akzhan Abdulin, Commented Sep 6, 2017 at 13:47
you got raw sequence number 121431. keep divide this number by 1000 and increment left part ("AAAA") while raw number is greater or equals to 1000. At finish concatenate left part ("ABCD") with remainder using zeroed left padding. — Akzhan Abdulin
– Akzhan Abdulin, Commented Sep 6, 2017 at 13:58

dani herrera · Accepted Answer · 2017-09-06 14:11:01Z

It works very slow with large amount of data, because it must be executed before inserting every single row. Is there another way to do that with or without Perl?

First option:

Create a sequence and decode it:

CREATE SEQUENCE my_serial START 11110000;

Translate to letters left part:

select nextval(''my_serial'') INTO v_nextSeq ;
select translate(
       left( trim(to_char(v_nextSeq,'9999')), 4),
       '12345','ABCDE')
       ||
       right( trim(to_char(v_nextSeq,'9999')), 4);

Second option:

You can persist all sequence numbers and then use it:

**Add used field to your schema, remember to create an index over sequence **:

CREATE TABLE batch_sequence
(
   id        serial   NOT NULL,
   sequence  text     DEFAULT 'AAA'::text NOT NULL,
   used      boolean  default false
);

Populate the whole table, here is simplified just 2 digits:

with s as (
  SELECT A.a || B.b  as sequence
  FROM unnest(string_to_array('A B C',' ')) A
  CROSS JOIN unnest(string_to_array('0 1 2 3 4 5 6 7 8 9',' ')) B
) 
insert into batch_sequence ( sequence )
select s.sequence
from s;

Check all is done:

select * from batch_sequence

Results:

| id | sequence |  used |
|----|----------|-------|
| 28 |       A0 | false |
| 29 |       A1 | false |
| 30 |       A2 | false |
| 31 |       A3 | false |
| 32 |       A4 | false |
| 33 |       A5 | false |
| 34 |       A6 | false |
| 35 |       A7 | false |
| 36 |       A8 | false |
| 37 |       A9 | false |
| 38 |       B0 | false |
| 39 |       B1 | false |
| 40 |       B2 | false |
| 41 |       B3 | false |
| 42 |       B4 | false |
| 43 |       B5 | false |
| 44 |       B6 | false |
| 45 |       B7 | false |
| 46 |       B8 | false |
| 47 |       B9 | false |
| 48 |       C0 | false |
| 49 |       C1 | false |
| 50 |       C2 | false |
| 51 |       C3 | false |
| 52 |       C4 | false |
| 53 |       C5 | false |
| 54 |       C6 | false |
| 55 |       C7 | false |
| 56 |       C8 | false |
| 57 |       C9 | false |

Create a function to get new sequence number and set it as used.

Collectives™ on Stack Overflow

Postgres - How to create an alphanumeric sequence like AAAA0000 and so on

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related