How to set a column default to a PostgreSQL function using SQLAlchemy?

Question

All of my tables defined in SQLAlchemy have an id column of type UUID.

from sqlalchemy.ext.declarative import declarative_base
from uuid import uuid4
Base = declarative_base()

class MyTable(Base):
    id = Column(UUIDtype, default=uuid4, primary_key=True)

Where UUIDtype is a special column type for the PostgreSQL UUID type. This successfully creates the UUID column in PostgreSQL, but only generates a default UUID value when inserting using SQLAlchemy. This is fine for some of my use cases, but I want to assign the column's default on the PostgreSQL side of things in case an insert occurs outside of SQLAlchemy.

The following SQL command places the proper default on the column:

ALTER TABLE my_table ALTER COLUMN id SET DEFAULT uuid_generate_v4()

How can I assign this uuid_generate_v4() function as the column default through SQLAlchemy model definition? If the only option is through executing a raw SQL statement, is there a way for me to hook that statement's execution into the table creation process?

Paul P · Accepted Answer · 2022-01-09 20:53:17Z

52

You should use server_default parameter for this.

id = Column(UUIDtype, server_default=text("uuid_generate_v4()"), primary_key=True)

See Server Side Defaults for more information.

edited Jan 9, 2022 at 20:53

Paul P

4,0372 gold badges15 silver badges28 bronze badges

answered Dec 12, 2013 at 7:41

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mozillazg Over a year ago

Install uuid-ossp module with create extension uuid-ossp

mozillazg Over a year ago

Or use gen_random_uuid() instead of uuid_generate_v4() and install pgcrypto with create extension pgcrypto

thesmart Over a year ago

gen_random_uuid() is now part of PostgreSQL native as of version 13, and so doesn't require a module. postgresql.org/docs/13/functions-uuid.html

Collectives™ on Stack Overflow

How to set a column default to a PostgreSQL function using SQLAlchemy?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related