I want to create a code for a table in postgresql. The model could looks like: event {id, code, name, created_by} The code will start at E0001 and finish at E9999. The idea is to check at insert, if there's no code generate it, else insert the provided code. I assume I have to use a trigger, but I don't know how to generate that code with that sequence and check if exists or not. Help.
2
-
What happens when you insert the 10000th row?Laurenz Albe– Laurenz Albe2021-09-13 02:27:41 +00:00Commented Sep 13, 2021 at 2:27
-
Lets assume will never insert that, although the code could be E10000. The thing here is to maintain the format with zeros, at least until the 9999th row.MML1357– MML13572021-09-13 04:21:38 +00:00Commented Sep 13, 2021 at 4:21
Add a comment
|
3 Answers
You can use lpad function to pad the number with zeroes.
select 'E' || lpad (45::text, 5, '0')
Comments
You can use a DEFAULT clause in your column definition that uses a sequence:
CREATE SEQUENCE seq;
ALTER TABLE mytab ALTER code SET DEFAULT 'E' || CAST(nextval('seq') TO text);
Comments
I finally solved by doing this:
Creating a sequence
CREATE SEQUENCE seq;
and setting the DEFAULT value of code with
('E'::text || lpad((nextval('seq'::regclass))::text, 4, '0'::text))
Thanks both of you since I used the two answers.