I was working on seq number generation in PostgreSQL, where I needed to create INV0001, INV002, etc.
In Postgresql, I have created the sequence as below and I have added it to the Default value in the database
CREATE SEQUENCE invoice_number_seq;
CREATE TABLE invoices (
id SERIAL PRIMARY KEY,
invoice_number VARCHAR(20) UNIQUE
);
CREATE OR REPLACE FUNCTION generate_invoice_number()
RETURNS TRIGGER AS $$
BEGIN
NEW.invoice_number := 'INV-' || LPAD(nextval('invoice_number_seq')::TEXT, 3, '0');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_invoice_number
BEFORE INSERT ON invoices
FOR EACH ROW
EXECUTE FUNCTION generate_invoice_number();
So when I tried to add in Django db_default it did not take generate_invoice_number() as the default
it was adding an extra ’ in PostgreSQL and considering it as a string, not a function
invoice_number = models.Char(db_default='generate_invoice_number()')
Q1:
Are these features not available in Django? it was migrating but the generate_invoice_number() is not added as a function in the database default but is taken as a string. every time I inserted it was inserted as generate_invoice_number() in column value rather than running that function in db.
Q2:
If the above feature is not available, what would be the alternative, I don’t want to use a trigger. i want to use the default, if I do manual migration of run.sql it takes as function but the problem is the django invoice.save() is taking invoice_number as an empty string or null, so the default is not trigging in the database !! how do we solve this problem? How do we exclude and save invoice_number let the db take care of the database default rather than Django sending null or empty string
Hello there, please refer to the db_default
documentation.
This can be a literal value or a database function, such as Now
You passed a literal string to db_default
hence a literal string will be stored in your column.
You should use a Func(function="generate_invoice_number")
expression if you want to reference a database function or your own.
2 Likes
@charettes Thanks for you solution , it was working but when I pass parameter in a custom function it was not working, it was throwing error in migration itself,
Below details
I have a model
class Post(models.Model):
title = models.CharField(max_length=200)
content = models.TextField()
author = models.CharField(max_length=100)
created_at = models.DateTimeField(auto_now_add=True)
name = models.CharField(db_default=Func(function="generate_random_number"))
post_code =models.CharField(db_default=Func(function="get_formatted_seq_value('post_seq', 'POST-', 10, '0')"))
The “name” with default_db was working fine
but ‘post_code’ with default_db fucnation not working with params
post_code =models.CharField(db_default=Func(function="get_formatted_seq_value('post_seq', 'POST-', 10, '0')"))
--DB function in postgresql
CREATE OR REPLACE FUNCTION get_formatted_seq_value(sequence_name text, prefix text, pad_length int, pad_char text)
RETURNS VARCHAR AS $$
BEGIN
RETURN prefix || LPAD(nextval(sequence_name)::text, pad_length, pad_char);
END;
$$ LANGUAGE plpgsql;
Exception while migrating after adding post_code model
raise ex.with_traceback(None)
django.db.utils.ProgrammingError: syntax error at or near "("
LINE 1: ..._formatted_seq_value('post_seq', 'POST-', 10, '0')()) NOT NU...
Hi All,
It is working
post_code = models.CharField(
db_default=Func(
Value("post_seq"),
Value("POST-"),
Value(10),
Value("0"),
function="get_formatted_seq_value"
)
)