ILIKE is a PostgreSQL-specific operator that works just like LIKE, but is case-insensitive. Unlike collations, citext does not allow the same column to be compared case-sensitively in some queries, and and insensitively in others.Using a function that isn't overloaded will result in a regular, case-sensitive match. Several PostgreSQL text functions are overloaded to work with citext as expected, but others aren't.While citext allows case-insensitive comparisons, it doesn't handle other aspects of collations, such as accents.Some limitations (others are listed in the PostgreSQL docs): ModelBuilder.Entity().Property(b => b.Name) Protected override void OnModelCreating(ModelBuilder modelBuilder) Specifying that a column should use citext is simply a matter of setting the column's type:
DIFFERENCE BETWEEN LIKE AND ILIKE INSTALL
The PostgreSQL docs provide more information on this type.Ĭitext is available in a PostgreSQL-bundled extension, so you'll first have to install it: modelBuilder.HasPostgresExtension("citext") The older PostgreSQL method for performing case-insensitive text operations is the citext type it is similar to the text type, but operators are functions between citext values are implicitly case-insensitive. The end result of the above is very similar to specifying a database collation: instead of telling PostgreSQL to implicit apply a collation to all columns, EF Core will do the same for you in its migrations. To work around this limitation, you can use EF Core's pre-convention model configuration feature: protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)ĬonfigurationBuilder.Properties().UseCollation("my_collation") Īll columns created with this configuration will automatically have their collation specified accordingly, and all existing columns will be altered.
Unfortunately, the database collation is quote limited in PostgreSQL it notably does not support non-deterministic collations (e.g. PostgreSQL also allows you to specify collations at the database level, when it is created: protected override void OnModelCreating(ModelBuilder modelBuilder) This will cause all textual operators on this column to be case-insensitive. ModelBuilder.Entity().Property(c => c.Name) ModelBuilder.HasCollation("my_collation", locale: "en-u-ks-primary", provider: "icu", deterministic: false) Once a collation has been created in your database, you can specify it on columns: protected override void OnModelCreating(ModelBuilder modelBuilder) Consult the ICU docs for more information on supported features and keywords.
ICU collations are very powerful, and allow you to specify precise rules with regards to case, accents and other textual aspects. The rest of the parameters instruct PostgreSQL to create a non-deterministic, case-insensitive ICU collation. This creates a collation with the name my_collation: this is an arbitrary name you can choose, which you will be specifying later when assigning the collation to columns.
To create a collation, place the following in your context's OnModelCreating: modelBuilder.HasCollation("my_collation", locale: "en-u-ks-primary", provider: "icu", deterministic: false) In PostgreSQL, collations are first-class, named database objects which can be created and dropped, just like tables. It is not yet possible to use pattern matching operators such as LIKE on columns with a non-deterministic collation.
0 kommentar(er)
