Hi Samuel, good point. I would still try to put everything into one database function that is used for that index. In that conversion function, you can make use of the full feature set of your database, regardless of what application uses it, because the application doesn't have to know the syntax or anything. And you could also customize this a bit more, and make more exceptions.
For example, there are languages with other variantions on this than just diacritics. Some consider certain sequences of characters as a single letter that you might want to compare to one other letter, and so on. Totally depends on the use case. With a function that you can tweak at any time (at the cost of re-creating the index) you have the most control. Besides, the question was for a UNIQUE index, which is typically not what you would use for searches, so I'm not sure case insensitive searching is what the original question was about. If that constraint is going to be used to ensure that each name is contained only once in the table, then two names with differing diacritics could very well be desired to be considered different in that sense. Maik > Am 27.03.2017 um 15:04 schrieb Samuel Pelletier <sam...@samkar.com>: > > Hi Maik, > > Works great for english but usually not enough for french or other languages > with accents. For example french Canadians expect case insensitive search and > ordering to consider all the variants of e (eEéÉèÈêÊëË) equals same for c and > ç, etc. > > This varie with language and I think even with countries for the same > language in some cases. This is why unicode created collations. > > Samuel > >> Le 27 mars 2017 à 04:52, Musall, Maik <m...@selbstdenker.ag >> <mailto:m...@selbstdenker.ag>> a écrit : >> >> Hi, >> >> I would just create a unique function based index, like this: >> >> CREATE UNIQUE INDEX indexname ON MyTable( UPPER(columnName) ); >> >> No extensions required. Works with every RDBMS that supports function based >> indexes. >> >> Maik >> >>> Am 27.03.2017 um 02:29 schrieb Paul Hoadley <pa...@logicsquad.net >>> <mailto:pa...@logicsquad.net>>: >>> >>> Hi Jeff, >>> >>> On 25 Mar 2017, at 04:16, Jeff Schmitz <jeffschm...@icloud.com >>> <mailto:jeffschm...@icloud.com>> wrote: >>> >>>> Just a quick question on how to create a case insensitive unique index in >>>> an ERXMigration? >>> >>> As Samuel mentioned, this is going to be database-dependent. We’ve been >>> using PostgreSQL’s CITEXT type for a year or so now, and it works as >>> designed. Because it’s an extension type, you need to run: >>> >>> CREATE EXTENSION IF NOT EXISTS citext; >>> >>> at some point—we do this in a migration upgrade(). You can then add and >>> alter columns and add indexes in the usual way. There’s a brief discussion >>> on performance here: >>> >>> http://stackoverflow.com/questions/31133603/in-postgresql-weird-issue-about-citext-performance >>> >>> <http://stackoverflow.com/questions/31133603/in-postgresql-weird-issue-about-citext-performance> >>> >>> though that’s not specific to indexing that column type. >>> >>> (Finally, if you are using PostgreSQL, and you do need to add this >>> extension to an existing database during a migration, there is a small >>> issue with the JDBC info not being available to EOF quite early enough, >>> which is easily fixed. I can dig up the thread if you need it.) >>> >>> >>> -- >>> Paul Hoadley >>> http://logicsquad.net/ <http://logicsquad.net/> >>> >>> >>> _______________________________________________ >>> Do not post admin requests to the list. They will be ignored. >>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com >>> <mailto:Webobjects-dev@lists.apple.com>) >>> Help/Unsubscribe/Update your Subscription: >>> https://lists.apple.com/mailman/options/webobjects-dev/maik%40selbstdenker.ag >>> >>> <https://lists.apple.com/mailman/options/webobjects-dev/maik%40selbstdenker.ag> >>> >>> This email sent to m...@selbstdenker.ag <mailto:m...@selbstdenker.ag> >> >> _______________________________________________ >> Do not post admin requests to the list. They will be ignored. >> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com >> <mailto:Webobjects-dev@lists.apple.com>) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com >> <https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com> >> >> This email sent to sam...@samkar.com <mailto:sam...@samkar.com>
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
