1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
joeyk16
  • 1,357
  • 22
  • 49

2 Answers2

5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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"')
Schwern
  • 153,029
  • 25
  • 195
  • 336
  • Hi @Schwern I did go down that route yesterday and I ended up with the same error. Erwin just explained above that Default is a reserved word. – joeyk16 Oct 08 '20 at 23:44
  • @joeyk16 Down which route? Validations? `where("default")` will also fail for the same reasons. – Schwern Oct 09 '20 at 00:01
  • The validation works of course. The `where("default")` doesn't. I must have it on the database level. – joeyk16 Oct 09 '20 at 00:05
  • 1
    @joeyk16 `where(default: true)` ensures Rails will quote the column name. And I've also provided both the SQL and Rails way to make the unique index. – Schwern Oct 09 '20 at 00:13
  • 1
    @joeyk16 Sorry, I got the quoting wrong. I've been hanging around MySQL too much. I'm with Erwin, consider a different column name. `is_default` perhaps. – Schwern Oct 09 '20 at 00:17