sqlpostgresqlidentifierreserved-wordspartial-index

DEFAULT is not allowed in this context error

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.

Solution

  • 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.