CODEWITHSUNDEEP
postgresqltriggerssequence
MML1357
MML1357

Reputation: 157

How to generate custom code in postgresql

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.

Upvotes: 0

Views: 743

Answers (3)

MML1357
MML1357

Reputation: 157

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.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247260

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

Upvotes: 1

jira
jira

Reputation: 3944

You can use lpad function to pad the number with zeroes.

select 'E' || lpad (45::text, 5, '0')

Upvotes: 1

Related Questions