Customized ID in PostgreSQL using the user's input

Question

I need to create an ID that uses data from the user's input. For example, the first letter is "M" the second is the year of birth that the user inputs, then the two first characters of the province the user lives in (for example, ON for Ontario), and then a sequence of random numbers. Like this: M-1990-ON-0001 Do you have any idea how I can do that?

Answer 1

You can do that as a generated column. ie:

customId text GENERATED ALWAYS AS ('M-'||extract(year from birthdate)::char(4)||'-'||province) STORED

However, you should never make such info a primary key. Just use a key for yourself but do not as a primary key.

DBFiddle demo

If you think about it such a value wouldn't be even unique (same province, same year, two persons for example).