Domain for a char with only 5 digits. Postgresql-CodePudding

I want to create a domain in PostgreSQL for the insert of a code of only 5 digits (from 00000 to 99999). I want to keep all 0 before(int deletes 0 before other numbers) so that I can insert values like 00001.

CodePudding user response：

Do a padding by the left of your numeric value that must be stored in a varchar field

select lpad(1::text,5,'0');

CodePudding user response：

Use a check constraint with the appropriate regular expression:

create domain five_digit_code as text
check (value ~ '^\d{5}$');

See regexr.com for a regex explanation.