-1

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?

Melika02
  • 9
  • 2

1 Answers1

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).

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • Thank you. would you please explain why I can't use it as a primary key? – Melika02 Oct 24 '22 at 15:28
  • @Melika02, as in my edit, having two persons from same province, same year is just one reason. Even if you didn't have such a possibility, that field is dependent upon 2 other "user input" fields, which is very dangerous to use as a primary key. It is safer to use values that are not input by user (except maybe very special values like a VIN - Vehicle Identification Number, passport number + authority etc.) If you check the sample there is a serial which is good for a primary key, and you could use this generated field for your own purposes. – Cetin Basoz Oct 24 '22 at 15:32
  • What if the random sequence of int numbers (at the end) is unique? It's going to be for people but each person may not have a passport or when they change their passport, it's number will change as well and it makes problems again. – Melika02 Oct 24 '22 at 15:36
  • @Melika02, it is your table, your application. You might decide taking the risk yourself. As I said, I would use a computer generated value (a serial or UUID are best examples) as primary key and any other as a candidate or regular key. With a random sequence of integers at the end, you are anyway reinventing some sort of UUID. – Cetin Basoz Oct 24 '22 at 15:45