CODEWITHSUNDEEP
sqldatabasepostgresql
Ahmed Habib
Ahmed Habib

Reputation: 41

Postgresql how to create a custom id column formatted with character, date and number

CREATE SEQUENCE customers_seq;
CREATE TABLE customers (
  id INT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('customers_seq'),custom_id VARCHAR(100),test varchar(10)
)

I need the sequence to go like so:

CU22_001
CU22_002
CU22_003

Explanation

CU - Is Stable Character

22 - Todays year date format (yy)

001 - Increments by +1 when a new value is added.

Next year (2023)..

the sequence needs to be reset back to 001 but the 20 changes to 21 because of the year being 2022 :

CU23_001
CU23_002
CU23_003

Question

How can I create this custom ID column BEFORE INSERT the new row?

Upvotes: 2

Views: 3325

Answers (1)

D-Shih
D-Shih

Reputation: 46229

After Postgres version 12, we can try to use Generated Columns

The generation expression can only use immutable functions.

So that we need to create generate_custom_id immutable function which generate your expect custom_id format.

CREATE FUNCTION generate_custom_id(id INT)
returns text
as
$$
  SELECT 'CU' || TO_CHAR(now(), 'YY')|| '_' || TO_CHAR(id,'FM000');
$$
language sql
immutable;

then use that in your custom_id column

CREATE SEQUENCE customers_seq;
CREATE TABLE customers (
  id INT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('customers_seq'),
  custom_id VARCHAR(100) GENERATED ALWAYS AS ( generate_custom_id(id)) STORED,
  test varchar(10)
);

sqlfiddle

Upvotes: 3

Related Questions