Creating a partial unique index with SQLAlchemy in PostgreSQL

The goal

Recently I came across a need for a unique constraint which is only applied to a subset of the records in one table. I've found that this is sometimes referred to as a "partial unique constraint" or in the PostgreSQL case it is implemented as a "partial unique index". This note is about how to implement it based on the ORM features of SQLAlchemy.

In my case, I had a table structure which looked like the following attributes:

• id as a primary key
• object_type as a string
• object_id as a number
• is_primary as a boolean
• content as a string

Create an example table

Creating such a table can be done in the following steps. This is only useful if you want to experiment with partial indexes directly in PostgreSQL, if you've already SQLAlchemy set up, take the approach below and just initialize your database. In my case I was somewhat new to the concept of partial indexes and wanted to find out if this approach works for my needs before going ahead.

First start with an empty fresh database:

createdb example
psql example

Then create a simple example table structure as follows:

CREATE TABLE example_table (
    id INTEGER PRIMARY KEY,
    object_id INTEGER,
    object_type VARCHAR(50),
    is_primary BOOLEAN,
    content TEXT);

Check the table description by using \d:

example=# \d example_table
          Table "public.example_table"
  Column    |         Type          | Modifiers
-------------+-----------------------+-----------
id          | integer               | not null
object_id   | integer               |
object_type | character varying(50) |
is_primary  | boolean               |
content     | text                  |
Indexes:
    "example_table_pkey" PRIMARY KEY, btree (id)

I wanted to ensure that there can be at most one record flagged as is_primary for any given tuple of object_type and object_id. In SQL this looks somewhat like the following:

CREATE UNIQUE INDEX ix_unique_primary_content
    ON example_table (object_type, object_id) WHERE is_primary

Test it out

Enter the first example value:

example=# INSERT INTO example_table VALUES (1, 1, 'test', true, 'example');
INSERT 0 1

Then trying to enter another record for the same values of object_id and object_type should fail if is_primary is set to TRUE:

example=# INSERT INTO example_table VALUES (2, 1, 'test', true, 'example');
ERROR:  duplicate key value violates unique constraint "ix_unique_primary_content"
DETAIL:  Key (object_type, object_id)=(test, 1) already exists.

If is_primary is not TRUE, then it should be allowed to enter a duplicate:

example=# INSERT INTO example_table VALUES (2, 1, 'test', false, 'example');
INSERT 0 1

This is basically the desired behavior. I can at most enter one primary entry per object and as many non-primary ones as I want.

Model it in SQLAlchemy's declarative approach

So the question was how to get this done in SQLAlchemy with the declarative approach. After researching the documentation I found that the following Python code does produce the desired result:

class ExampleTable(Base):

    __tablename__ = 'example_table'
    __table_args__ = (
        Index(
            'ix_unique_primary_content',  # Index name
            'object_type', 'object_id',  # Columns which are part of the index
            unique=True,
            postgresql_where=Column('is_primary')),  # The condition
    )

    id = Column(Integer, primary_key=True)
    object_type = Column(Unicode(50))
    object_id = Column(Integer)
    is_primary = Column(Boolean)

The key insight was to use the keyword postgresql_where. This is an extension which is specific to the postgresql dialect.