Django 5.x db_defaullt for a colunm in model not taking custom db function

Using Django Using the ORM
1

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

2

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
3

@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...
4

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