I have a SQLAlchemy model like this:

class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    column: Mapped[int] = mapped_column(nullable=True, unique=True)

How can I make it so column column can be null, and will be an autoincremented integer value otherwise?

user1 = User() # here the `column` should be null
user2 = User() # missing code to make `column` autoincremented value, so for example if the previous user had it as NULL here it would be the first value, 0

I am using PostgreSQL and Flask-SQLAlchemy if this helps.

I have a SQLAlchemy model like this:

class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    column: Mapped[int] = mapped_column(nullable=True, unique=True)

How can I make it so column column can be null, and will be an autoincremented integer value otherwise?

user1 = User() # here the `column` should be null
user2 = User() # missing code to make `column` autoincremented value, so for example if the previous user had it as NULL here it would be the first value, 0

I am using PostgreSQL and Flask-SQLAlchemy if this helps.

• python
• postgresql
• flask
• sqlalchemy
• flask-sqlalchemy

• What you are requesting does not make any since. If you provide a value you want it overridden an with auto generated value but if not provided (null) then let it be null. Beside that the code your attempting is impossible. A column specified as the primary key must be defined as not null and unique. I do not know if Flask-SQLAchmy will accept the above but the database would not. – Belayer Commented Feb 17 at 18:30
• @Belayer I perfectly explained what I want. It does make sense to me, and I'm sure it would to you too if you'd read more carefully. What you explained later is a possible solution for this. The code is fully valid, because when primary_key is set to True, nullable=False and unique=False parameters are set like so automatically. This is not only for Flask-SQLAlchemy, but for vanilla SQLAlchemy too. – roundedrectangle Commented Feb 17 at 19:18
• Can you explain why you want to do this? Perhaps there are other ways to achieve your goal. – snakecharmerb Commented Feb 18 at 7:01
• In my real code I have additional column username, and I want user to be able to choose to have a username or a special number. I don't want users with username to have that number, thus I want that column to be nullable. The number should be choosen by the next free one available, which is why I need autoincrement. – roundedrectangle Commented Feb 18 at 7:08
• 1 What you're asking for is a actually a sequence type: postgresql./docs/current/… - you didn't specify which RDBMS you're using, but serial types (which is a sequence you can ask for the next value from) will work and be safe as to avoid duplicates as they're atomic (your answer can have issues with race conditions). Another option is to use uuid or something similar instead of an autoincrementing value. A primary key is a key, so it needs to be unique. – MatsLindh Commented Feb 18 at 9:31

2 Answers 2

Thanks to @MatsLindh who recommended a better solution:

from sqlalchemy import Sequence
seq = Sequence('user_column_seq', optional=True)
class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    column: Mapped[int] = mapped_column(nullable=True, unique=True)

user1 = User() # NULL
user2 = User(column=seq.next_value()) # next free value

UPD: see the better answer based on @MatsLindh's comment

This is not a complete answer to the question but a workaround I found and will use.

This is what I came up with:

from sqlalchemy import func

# tip: this can be decorated as a @property in a class
def next_value():
    v = User.query.with_entities(func.max(User.column)).scalar()
    return v+1 if v is not None else 0

Usage:

user1 = User() # NULL
user2 = User(column=next_value()) # next free value