How do you add a Unique case insensitive Index to a PostgresSQL table using EF Fluent API-CodePudding

I have recently been using PostgreSQL rather than SQL, so finding a lot of little nuances between the two.

I want to be able to make a string value unique in a table, so using the EF code first fluent API, I have this code.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyTable>()
        .HasIndex(u => u.UniqueValue)
        .IsUnique();
    
    base.OnModelCreating(modelBuilder);
}

When creating a migration, it will generate this.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateIndex(
        name: "IX_MyTable_UniqueValue",
        table: "MyTable",
        column: "UniqueValue",
        unique: true);
}

This will then add the index to the PostgreSQL table, and work when the word is of the same case.

e.g. Try and insert "Hello" twice, and it will not work.

It does allow for variations of the word though, so I can insert "Hello", "HEllo", "HELlo", etc...

It looks like it is possible to force the case on the index in PostgreSQL using something like

CREATE UNIQUE INDEX UniqueValue ON MyTable (UPPER(UniqueValue));

However I am not sure how to do this via EF Fluent API and create the migration from that?

CodePudding user response：

It seems like for now you'll have to set up a raw SQL migration. Support for that still hasn't been added (yet). You could also set up a generated (computed) column that holds the result of upper(UniqueValue), then add a unique index on that.

There's no way to add an expression-based unique constraint or to add the expression to an existing unique index key. You can build a new index using your definition as is:

CREATE UNIQUE INDEX UniqueValue ON MyTable (UPPER(UniqueValue));

Or add an exclusion constraint which ends up doing pretty much the same (as of now, quite far from being supported in EF):

create table test(txt text);
alter table test 
  add constraint case_insensitive_unique 
  exclude using gist(upper(txt) with =);

insert into test(txt) select 'hello';
--INSERT 0 1
insert into test(txt) select 'Hello';
--ERROR:  conflicting key value violates exclusion constraint "case_insensitive_unique"
--DETAIL:  Key (upper(txt))=(HELLO) conflicts with existing key (upper(txt))=(HELLO).

Demo