How to Set an Empty Dictionary/Hash as Default Value for a PostgreSQL HSTORE Column
Do you have an HSTORE column on your PostgreSQL database that you don't want to be `null` but need to have a default value? The syntax for this is a little irregular; so I'm posting it here for my own reference and yours:
my_column HSTORE DEFAULT '' NOT NULL
is the line in your `CREATE TABLE`command that you want.
In Ruby on Rails, using an ActiveRecord migration, you would use:
t.hstore :my_column, default: {}sa.Column('my_column', HSTORE(), nullable=False, server_default=sa.text("''")),Additionally, if you want your SQLAlchemy model object to initialize this column with said empty dictionary (instead of `None`), per this StackOverflow post you need take a couple of extra steps in your model:
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict
class Item(db.Model):
my_column = db.Column(MutableDict.as_mutable(HSTORE), nullable=False, default={}, server_default='')
def __init__(self, **kwargs):
kwargs.setdefault('my_column', {})
super(Item, self).__init__(**kwargs)