[Python] PostgreSQL Enum Type Not Accepting Valid Value in SQLAlchemy ORM Application

I'm experiencing a persistent InvalidTextRepresentationError when trying to insert a record with a specific enum value ("xml") into a PostgreSQL database using SQLAlchemy. The error occurs despite confirming that "xml" is a valid enum value in the database schema. Error message:

invalid input value for enum assettype: "xml"
​

• PostgreSQL 13
• SQLAlchemy 1.4
• Python 3.9
• FastAPI application framework

Database Schema My PostgreSQL database has an enum type called assettype with values including 'epub', 'pdf', and 'xml'. This enum is used in the asset table.

Code SQLAlchemy Model:

class Asset(BaseTable):
__tablename__ = 'asset'

id = Column(UUID, primary_key=True)
title = Column(String(256), nullable=False)
source_id = Column(String(256), nullable=False, unique=True)
asset_type = Column(Enum(CoinsEnum.asset_type), nullable=False)
hash = Column(String(32), nullable=False)
created_at = Column(DateTime, nullable=False, server_default=func.now())
updated_at = Column(DateTime, nullable=False, server_default=func.now(), onupdate=func.now())
created_by = Column(String(256), nullable=False)
updated_by = Column(String(256), nullable=False)


Repository Method (that fails):

async with self.postgres_client.session(is_write_operation=True) as postgres_session:
# Check if asset with same hash exists
existing_asset = (
await postgres_session.execute(
select(AssetModel)
.filter(AssetModel.hash == hash_value)
)
).scalar_one_or_none()

if existing_asset:
raise APIException(
f"File already uploaded with asset_id: {existing_asset.id}",
400
)

# Generate a new UUID for the asset
asset_id = uuid.uuid4()

try:
# Try using a direct SQL query with explicit casting
sql = text("""
INSERT INTO asset
(id, title, source_id, asset_type, hash, created_by, updated_by)
VALUES
id, :title, :source_id, CASTasset_type AS assettype), :hash, :created_by, :updated_by)
RETURNING id, created_at, updated_at
""")

params = {
'id': asset_id,
'title': title,
'source_id': str(asset_id),
'asset_type': asset_type.value,
'hash': hash_value,
'created_by': created_by,
'updated_by': created_by
}

result = await postgres_session.execute(sql, params)
row = result.fetchone()

if row:
await postgres_session.commit()
asset = await postgres_session.get(AssetModel, row[0])
return asset
else:
raise Exception("SQL query didn't return an ID")

except Exception as e:
await postgres_session.rollback()
raise e

Questions

• 
  Why would SQLAlchemy reject a valid enum value when inserted through SQLAlchemy?
  
  
• 
  Could there be a discrepancy between how SQLAlchemy serializes enums and how PostgreSQL expects them?
  
  
• 
  Is there a configuration setting I'm missing for proper enum handling?
  
  
• 
  What's the proper way to debug this type of error beyond what I've already tried?
  

Any insights or solutions would be greatly appreciated!

Full traceback here: ❌ Error Traceback

Error Summary


Date: 2025-03-01 01:37:30.141191Z
Request ID: 9680068a-e6d4-4c0f-8b2b-3331904f815a
Service: CoinsFunction
Function: services.service
Location: /var/task/utils/log.py:83 in _get_extra

Error Details

PostgreSQL Error


Error Type: asyncpg.exceptions.InvalidTextRepresentationError
Message: invalid input value for enum assettype: "xml"

Failed SQL Query


INSERT INTO asset (
id, title, source_id, asset_type, hash, created_by, updated_by
) VALUES (
$1::UUID, $2::VARCHAR, $3::VARCHAR, $4::assettype, $5::VARCHAR, $6::VARCHAR, $7::VARCHAR
) RETURNING asset.created_at, asset.updated_at


Parameters


(
UUID('c17aeb26-b515-40fc-9db8-fa7ec3e993d1'), # id
'test.xml', # title
'c17aeb26-b515-40fc-9db8-fa7ec3e993d1', # source_id
'xml', # asset_type (INVALID)
'4479f05f81f0871fab60f4de36fb9c93', # hash
'system', # created_by
'system' # updated_by
)


Context


{
"title": "test.xml",
"asset_type": "xml"
}


Lambda Execution Stats


Init Duration: 0.26 ms
Execution Duration: 11945.32 ms
Billed Duration: 11946 ms
Memory Size: 128 MB
Memory Used: 128 MB (100%)

More Information


Background on this error: https://sqlalche.me/e/20/dbapi

Continue reading...