khorvat wrote:
I have manually put citext in the persistenceinfo and everything is working properly, so I think that citext is the proper value for this if the filed type in the database is citext.
What do you mean by read from DB meta data ? I think DB should return citext as field type ?
Reverse engineering the schema will make the driver see 'text' fields and not 'citext' fields, as the driver currently doesn't know anything about citext. I think they currently fall back to the varchar type as the type is unknown.
I see it's an extension type, which we don't support by default.
I also think it's not really the right place to state the field is a 'Citext' field, as the Citext directive is query specific. What if you need to query the field later on with case sensitive data?
In my opinion if the field is citext then default behavior should be to operate on the field with case-insensitivity and if one want to do a case-sensitive search I'll do a cast or apply your suggestion.
That's true, but as currently we don't support citext, there's not a lot left to do.
the best way to solve this IMHO is to use what's available for that: set the CaseInsensitive flag on the predicate and use an uppercased value to compare with.
This can be used as an alternative, but has two drawback in my opinion, one is that I need to change / handle the case-insensitivity manually and the second, index on citext field won't be used (not 100% sure this is correct I'll try to find a source of this info).
The postgresql docs on citext indeed state the upper() (or in their case 'lower()') usage is less efficient, as it doesn't use an index, unless you define the index with the function. See https://www.postgresql.org/docs/9.1/static/citext.html
I see you have text option in dropdown on edit field dialog in the designer so is there a chance to read the meta data from the db and get the citext in there ?
Yes, but you need to alter the driver for that. The driver contains tables to convert from a DB type (e.g. 'text') to .NET type and the NpgsqlDbType enum value and to string for display purposes. Adding a line to these tables for citext is enough to support it.
I'll see if it's easy to install citext on a postgresql server, if not, then you unfortunately have to use the workaround.
BTW it seems to me that ORM profiler doesn't know how to handle citext when I try to execute the query and plan. My application is returning the result when I manually set citext in persistence but ORM Profile returns zero rows.
That's because orm profiler uses DbType, as that's all it has. (ado.net provider specific types convert to dbtype under the hood. orm profiler works with DbCommand/DbConnection objects only, so doesn't know of any ado.net provider specific type and can't set it to citext)