Add Postgres serial field to contrib.postgres

[Johannes Maron]

Since we have the beautiful contrib.postgres package now, we can add a couple more Postgres specific fields, that are not supported by MySQL but handy to all the Postgres fan boys.

That being said, I guess it's a good idea to start with Serial.

The PostgreSQL documentation: ​https://www.postgresql.org/docs/9.1/static/datatype-numeric.html describes the field as follows:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

Use Cases:

1. Anywhere an automatic incrementing value is required, but a primary key is often (mis)used.
2. Providing additional (user controlled) auto-incrementing values.

The primary benefit is that is isolates the primary key for the sole use of maintaining referential integrity.

[Johannes Maron]

I opened a ​PR with an implementation that I am already using somewhere. It added a couple of tests and documentation too.
I guess this is a good starting point for a discussion.

[Johannes Maron]

I closed my PR for now, due to a lack of interest. Since Django does support returning values since v3.0 everyone who needs such a field can build it. Yet, I don't have the time get the feature into Django for now.

[InvalidInterrupt]

Replying to Johannes Hoppe:

I closed my PR for now, due to a lack of interest. Since Django does support returning values since v3.0 everyone who needs such a field can build it. Yet, I don't have the time get the feature into Django for now.

Do you mind if I continue working on this using your branch? Handling sequence resets after loading fixtures will require changes to the DB backend.

[Claude Paroz]

Latest comment from Johannes is clearly an invitation to help :-)

[InvalidInterrupt]

​New PR

[InvalidInterrupt]

​New PR for Default Expression

[Johannes Maron]

Hi, the new default patch depends on ​https://github.com/django/django/pull/11783. Maybe you would care to review this one, to get things moving along faster? Best Joe

[Claude Paroz]

Does this still make sense now that Django is using identity columns for *AutoFields?

[Simon Charette]

Claude, yes I think that the move to identity columns on Postgres made this feature even more desirable (e.g. #34131).

[Simon Charette]

FWIW ​someone was able to implement an IdentityField which is kind of the equivalent of GeneratedField if it supported AS IDENTITY. It's not the equivalent of SerialField though but it demonstrates that all the pieces are there to implement it based on db_returning and pre_save returning a Default expression.

[Csirmaz Bendegúz]

I'm going to push this forward because we need an alternative to #8576. Please review my ​PR.

[Florian Apolloner]

Hi folks, I am wondering if we really want & need something like this in Django. What are the arguments that this has to be in Django as opposed to a third party library. The issue exists since 8 years with little to no comments so I would argue that using multiple serial fields on a model or having explicit access to a serial field is not a feature that is needed often. So even if this is suggested for contrib I kinda wonder if we have to support every postgresql feature out there.

[Csirmaz Bendegúz]

Replying to Florian Apolloner:

Hi folks, I am wondering if we really want & need something like this in Django. What are the arguments that this has to be in Django as opposed to a third party library. The issue exists since 8 years with little to no comments so I would argue that using multiple serial fields on a model or having explicit access to a serial field is not a feature that is needed often. So even if this is suggested for contrib I kinda wonder if we have to support every postgresql feature out there.

My goal with this one is to have a field that can be used as a surrogate key in composite primary keys (#373), when/if that feature lands.

If we can't have AutoFields in composite primary keys (#8576), then it would be nice to have SerialFields at least.

[Florian Apolloner]

To be honest I'd be slightly in favor of removing the existing limitations of AutoFields where databases can support it (if we are able to decide that it makes sense semantically at least). Adding a new field that basically does the same thing as an existing field kinda feels wrong. Granted, an explicit Serial/IdentityField would allow to specifiy more options (like adjusting the sequence name), but I am not sure if that would be a common enough usecase to add it to django itself.

[Csirmaz Bendegúz]

Replying to Florian Apolloner:

To be honest I'd be slightly in favor of removing the existing limitations of AutoFields where databases can support it (if we are able to decide that it makes sense semantically at least). Adding a new field that basically does the same thing as an existing field kinda feels wrong. Granted, an explicit Serial/IdentityField would allow to specifiy more options (like adjusting the sequence name), but I am not sure if that would be a common enough usecase to add it to django itself.

I was thinking the same thing initially, but this ticket was already Accepted, while #8576 received a lot of push back and was closed as wontfix.

IMO, another legit use-case is an "order column" - an auto-generated sequence that can be changed by the users. Semantically, it makes more sense to use SERIAL over IDENTITY for this.

[Csirmaz Bendegúz]

There's a forum discussion here: ​https://forum.djangoproject.com/t/serialfields-yay-or-nay/32541 .

[Sarah Boyce]

Based off the forum discussion, there doesn't appear to be a strong consensus that this should be added to Django core. Hence, closing as wontfix

[Csirmaz Bendegúz]

Here's the follow-up ticket: #35957