DEFAULT is not allowed in this context error

Question

Im trying to add an index to the database but I keep getting an error:

PG::SyntaxError: ERROR: DEFAULT is not allowed in this context

After reading the documentation for hours I can't seem to fix the issue.

I'm running this:

"CREATE UNIQUE INDEX index_uniq_service_models_default ON service_models(zone_id) WHERE default"

My table looks like this:

create_table :service_models do |t|
  t.string :name, null: false
  t.jsonb :configuration, null: false, default: "{}"
  t.boolean :default, null: false
  t.json :metadata, null: false, default: "{}"
  t.references :zone, foreign_key: true, null: false, index: { name: idx_name(:service_models, :zones) }

  t.timestamps
end

What I want to do is for a ServiceModel to only have 1 default for a zone.

A zone can have many ServiceModels but it can only have 1 default one.

Answer 1

To create a partial index that only includes rows where the column "default" is true:

CREATE UNIQUE INDEX index_uniq_service_models_default ON service_models(zone_id)
WHERE "default";

default is a reserved word and has to be double-quoted when used as identifier.

Better yet, don't use reserved words as identifier to begin with.

Answer 2

What I want to do is for a ServiceModel to only have 1 default for a zone.

Since you're using Rails, this may be better done with validations.

class ServiceModel
  belongs_to :zone

  validates_uniqueness_of :zone, conditions: -> { where(default: true) }
end

---

A where clause can be added to an index to create a partial index of only the rows which match. But where default is not a valid where clause because default is a SQL keyword. Since default is a SQL keyword it must be quoted as a column.

create unique index service_models_default_zone_idx
  on service_models("zone_id")
  where "default"

Or in your create_table block...

t.index(:zone_id, unique: true, where: '"default"')