[SOLVED] Create a serial number using characters and sequential number in PostgreSQL

Create a serial number using characters and sequential number in PostgreSQL

I would like to create a serial number/code that looks something like 22LX346789. The left 2 digits representing the year (of manufacture/registration), the 2 characters indicate product type. The right numbers increment, and not required to reset when a new year begins. So if the last serial number for 2021 was say 21LX346789, then the first serial number for 2022 would be 22LX346790. The 346789 .... would increment regardless of year or product type.

How could I merge this using a PostgreSQL sequence generator and store the result in a column in a PostgreSQL table? Ideally I would like to keep the process contained within PostgreSQL.

And this value would not be used as the primary index.

Thanks in advance

Solution

You can create a sequence like so:

CREATE SEQUENCE product_seq;

This creates a 64-bit sequence. You can then get the next value of the sequence by calling next_val(product_seq). When you insert a row in your table you can concatenate the various parts of your product id:

INSERT INTO my_table (product_id)
VALUES(concat(extract(year from current_date) - 2000,
              product_code,
              lpad(next_val(product_seq)::text, 6, '0')
             )
      )

The lpad() function pads a string with a character up to the length specified.