I want to store organization data in PostgreSQL database using Enums. Since organization names sometimes change, I need to sometimes update the Enum values, and I want to automatize this using Python with sqlalchemy and psycopg2. However after altering the Enum, new values cannot be inserted.

I suspect that this is due to internal check() method of psycopg2, since the database do accept the new value. Do you know a method to update the types known by psycopg2?

Here are my tests for this script:

class DbconTestCase(unittest.TestCase):
    def setUp(self):
        self.engine, self.meta = db.get_connection('test_user', 'test_pass', 'testdb')
        # Create test table, which will be deleted later
        self.test_table = Table('test_table', self.meta,
                                Column('id', Integer, primary_key=True),
                                Column('type', Enum('number', 'text', 'image', name='type'), nullable=False),
                                Column('text', String(32)))
        self.meta.create_all()

    def test_add_enum_value(self):
        try:
            # Add new value to enum named 'type'
            db.add_enum_value(self.engine, 'type', 'object')
        except Exception as exp:
            self.assertTrue(False, msg=exp.__cause__)
        else:
            self.assertTrue(True)

    def test_bulk_copy(self):
        types = ['number', 'text', 'image', 'object']
        objects = [{'id': idx,
                    'type': types[idx % len(types)],
                    'text': 'random text to insert'} for idx in range(10000)]
        try:
            db.bulk_copy(self.engine, str(self.test_table.name), objects)
        except Exception as exp:
            self.assertTrue(False, msg=exp.__cause__)
        else:
            self.assertTrue(True)

    def tearDown(self):
        self.meta.drop_all()

And a little explanation: Basically I create a test table in a test db, and then extend an enum type with a new value. Then I try to insert a huge amount of data, with new value of the enum among them. I got the following error:

psycopg2.DataError: invalid input value for enum type: "object"