sui generis.

All writing here is my own, does not represent the views of my employers, and is made without AI assistance.

Posts by category

How to Set an Empty Dictionary/Hash as Default Value for a PostgreSQL HSTORE Column

July 30, 2024

postgresql, programming, databases, hstore, the-more-you-know, sqlalchemy, alembic, activerecord, ruby.on.rails

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: {}

In Python, using an Alembic migration, you would use:

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)