id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36088	Avoid unnecessary DEFAULT usage on bulk_create for models with db_default fields	Simon Charette	Simon Charette	"When `bulk_create` is used for models composed of fields with `db_default` the resulting `INSERT` statement use `DEFAULT` placeholders to signify that a field must use the database defined default.

For example, the following code

{{{#!python
class Author(models):
    name = models.CharField(max_length=100)
    created_at = models.DateTimeField(db_default=Now())

Author.objects.bulk_create([Author(name=""foo""), Author(name=""bar"")])
}}}

Will result in the following SQL

{{{#!sql
INSERT INTO author (name, created_at) VALUES (%s, DEFAULT), (%s, DEFAULT)
}}}

But in cases where no `db_default` is provided for all bulk-created instances there is no point in specifying `DEFAULT` for each row as that's what the database will do if the column is not specified at all. In other words the above SQL is equivalent to

{{{#!sql
INSERT INTO author (name) VALUES (%s), (%s)
}}}

but the latter query simplification provide benefits:

Firstly, it would allow the `UNNEST` optimization introduced in #35936 (a16eedcf9c69d8a11d94cac1811018c5b996d491) to be enabled for models that define `db_default` fields. Alas since `DEFAULT` is an expression and the optimization must be disabled in their presence no models making use of `db_default` can take advantage of it.

In other words, on Postgres, the SQL could be

{{{#!sql
INSERT INTO author (name) SELECT * FROM unnest([%s, %s])
}}}

which has [https://forum.djangoproject.com/t/speeding-up-postgres-bulk-create-by-using-unnest/36508 demonstrated benefits].

Secondly, pruning the field would avoid having to provide the `db_default` expression for all model instances on backends that don't support `DEFAULT` in bulk-inserts such as Oracle and SQLite. In other words the following SQL would be avoided

{{{#!sql
INSERT INTO author (name, created_at) VALUES (%s, NOW()), (%s, NOW())
}}}

Lastly, it just make the query smaller as no `DEFAULT` has to be provided for each row for each columns with a defined `db_default` which surely reduce the parsing time on the backend."	Cleanup/optimization	closed	Database layer (models, ORM)	dev	Normal	fixed	unnest insert db_default default bulk_create		Ready for checkin	1	0	0	0	0	0