Storing a PostgreSQL ARRAY of ENUM values

Question

I have a table that can have a status:

statuses = ['unmoderated', 'nominee', 'finalist', 'winner']
status = db.Enum(
    *statuses, name='enum_nomination_status', metadata=db.metadata)


class Nomination(db.Model):
    status = db.Column(status, default='unmoderated')

I would now like to have a table that has a column that can contain multiple statuses:

class Judge(db.Model):
    statuses = db.Column(ARRAY(status, dimensions=1))

However the above approach leads me to this error:

ProgrammingError: (psycopg2.ProgrammingError) column "statuses" is of type enum_nomination_status[] but expression is of type text[]
LINE 1: ...4, 'Name', ARRAY['unm...
                  ^
HINT:  You will need to rewrite or cast the expression.

So I tried to create a custom type that did the cast to the enum type:

class STATUS_ARRAY(TypeDecorator):
    impl = ARRAY(status, dimensions=1)

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        else:
            return cast(array(value), ARRAY(status, dimensions=1))

But this causes a segfault.

I've also tried casting the individual items:

class STATUS_ARRAY(TypeDecorator):
    impl = ARRAY(status, dimensions=1)

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        else:
            return array(cast(s, status) for s in value)

But I get:

ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Cast' [SQL: 'INSERT INTO judge (statuses) VALUES (%(statuses)s)'] [parameters: {'statuses': [<sqlalchemy.sql.elements.Cast object at 0x7fc8bb69c710>]}]

I admit that I'm mostly trying different combinations of casting things without really knowing what's going on underneath the hood. I tried looking at the underlying ENUM implementation to see if I could get at some kind of native enum type without casting but I couldn't see anything. I'm grasping at straws.

Thanks for your help :)

I'd suggest you use the enum recipe, written by the author of sqlalchemy. — lrnzcig
– lrnzcig, Commented Jun 15, 2015 at 11:51
I just ran into this as well, and submitted this as issue 3467 to SQLAlchemy's issue tracker. — Wichert Akkerman
– Wichert Akkerman, Commented Jun 29, 2015 at 14:27
I'm having this issue too. I've basically tried everything that you've done too, but no joy. — J-bob
– J-bob, Commented Jul 22, 2015 at 14:53

flying sheep · Accepted Answer · 2021-03-25 11:04:51Z

3

As of 1.3.17, no workaround is needed anymore

The answer below ended up in the docs as ARRAY of ENUM. This docs page now says:

Changed in version 1.3.17: The combination of ENUM and ARRAY is now directly handled by SQLAlchemy’s implementation without any workarounds needed.

Old answer for historical purposes:

I looked at Issue 3467 posted by Wichert Akkerman, and this work-around was posted. Credit to Mike Bayer. Declare the following class in your code (with the necessary imports, of course):

from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy import cast

class ArrayOfEnum(ARRAY):
    def bind_expression(self, bindvalue):
        return cast(bindvalue, self)

    def result_processor(self, dialect, coltype):
        super_rp = super(ArrayOfEnum, self).result_processor(dialect, coltype)

        def handle_raw_string(value):
            if value==None:
                return []
            inner = re.match(r"^{(.*)}$", value).group(1)
            return inner.split(",")

        def process(value):
            return super_rp(handle_raw_string(value))
        return process

ArrayOfEnum is now a special column type that gets used in the model definition.

So instead of

class Judge(db.Model):
    statuses = db.Column(ARRAY(status))

Now you can do:

class Judge(db.Model):
    statuses = db.Column(ArrayOfEnum(status))

Now in your code you can assign values to statuses with a list and it will do the proper casting upon saving:

my_judge_object.status = ['unmoderated', 'nominee']

edited Mar 25, 2021 at 11:04

flying sheep

9,0315 gold badges62 silver badges81 bronze badges

answered Jul 22, 2015 at 15:30

J-bob

9,19413 gold badges57 silver badges92 bronze badges

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

4 Comments

WilliamMayor Over a year ago

I've not tested this personally yet but I'd be very surprised if it didn't work :) Cheers!

J-bob Over a year ago

I had to add some smalll changes from Mike's post, like adding if value==None: return [] and may have forgotten an import. If you find a bug or something missing, feel free to comment and I'll edit the answer.

Ndrslmpk Over a year ago

Instead of defining an array for my_judge_object.status am I able to define a class by importing from enum import Enum and applying class EnumName(Enum): to define key = 'value'-pairs of enums, like alternative ='Alternative' ?

Ndrslmpk Over a year ago

And what is the type of import i have to do to use ArrayOfEnum

Collectives™ on Stack Overflow

Storing a PostgreSQL ARRAY of ENUM values

1 Answer 1

As of 1.3.17, no workaround is needed anymore

Old answer for historical purposes:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

As of 1.3.17, no workaround is needed anymore

Old answer for historical purposes:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related