CODEWITHSUNDEEP
pythonsqlpostgresqlenumssqlalchemy
Hassan Jalil
Hassan Jalil

Reputation: 1194

Applying array_agg on Enum column returns ""{" is not among the defined enum values"

For my project, I am using Postgres DB and SQLAlchemy as an ORM. I have defined a few enums that we are using across different models.

I have a enum defined

class TestEnum(MultiValuedEnum):
   STATE1 = 'state-1', (
        'State 1', 'Waiting state 2')

   STATE2 = 'state-2', (
        'State 2', 'In state 2')

   STATE3 = 'state-3', (
        'State 3', 'In state 3')

    @property
    def display_name(self):
        return self.metadata[0]

    @property
    def description(self):
        return self.metadata[1]

This enum is used in a class 

class ExampleClass(db.Model):
    id = db.Column(db.BigInteger, primary_key=True, nullable=False)
    product_id = db.Column(db.BigInteger, nullable=False)
    state = db.Column(db.Enum(constants.TestEnum), nullable=False)

Now if I write a query 

query = session.query(
        ExampleClass.product_code,
        func.array_agg(ExampleClass.state)
    ).group_by(ExampleClass.product_code).all()

I get the following error

"{" is not among the defined enum values

If i run the generated query in dbeaver (caught using sqltap ), i get the correct result, and it looks like this

+---+------------+----------------+
|   |product_code|      State     |
+---+------------+----------------+
| 1 | A          |{STATE1,STATE2} |
| 2 | B          |{STATE2,STATE3} |
| 3 | C          |{STATE1,STATE3} |
+---+------------+----------------+

It seems like, SQL Alchemy is trying to parse the list of state to the State object and is failing due to { in the start denoting it as a list I suppose. Should it not convert it into a List of states, why am I getting this error and whats the fix or work around.

array_agg seems to work fine on other fields in the project, and I am able to loop over the returned Array, but for ENUMs it seems to be failing. Thank you

Upvotes: 2

Views: 555

Answers (1)

Hassan Jalil
Hassan Jalil

Reputation: 1194

So, while trying different things, I tried json_agg instead and it seems to work. So now my query looks like 

query = session.query(
        ExampleClass.product_code,
        func.json_agg(ExampleClass.state)
    ).group_by(ExampleClass.product_code).all()

And it seems to return me the ENUMS in form of a list.

Upvotes: 3

Related Questions