CODEWITHSUNDEEP
pythonsqlalchemyenums
carlos rocha
carlos rocha

Reputation: 117

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"

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, CAST(:asset_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

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

Upvotes: 0

Views: 20

Answers (1)

Cool Breeze
Cool Breeze

Reputation: 955

In your SQL query, you are casting asset_type to assettype using CAST(:asset_type AS assettype). However, since you're passing the asset_type as a string ('xml'), PostgreSQL will attempt to cast it to the enum, and it might fail because PostgreSQL expects a valid enum value that matches exactly with its internal representation.

To resolve this, make sure you are passing the Enum member itself (not just the string value). SQLAlchemy should handle the conversion automatically when you use the Enum column, but in raw SQL, you might need to handle this more explicitly.

Update your SQL query to pass the enum value directly like this:

sql = text("""
INSERT INTO asset 
(id, title, source_id, asset_type, hash, created_by, updated_by) 
VALUES 
(:id, :title, :source_id, :asset_type, :hash, :created_by, :updated_by)
RETURNING id, created_at, updated_at
""")

params = {
    'id': asset_id,
    'title': title,
    'source_id': str(asset_id),
    'asset_type': CoinsEnum.xml,  # Use the enum member, not the string value
    'hash': hash_value,
    'created_by': created_by,
    'updated_by': created_by
}

By passing CoinsEnum.xml instead of the string 'xml', SQLAlchemy should handle the serialization properly.

Upvotes: 0

Related Questions