The schema containing the citext operators must be in the current search_path (typically public) if it is not, the normal case-sensitive text operators will be invoked instead. In either situation, you will need two indexes if you want both types of searches to be fast. If you need case-insensitive behavior most of the time and case-sensitive infrequently, consider storing the data as citext and explicitly casting the column to text when you want case-sensitive comparison. The standard answer is to use the text type and manually use the lower function when you need to compare case-insensitively this works all right if case-insensitive comparison is needed only infrequently. However, citext is slightly more efficient than using lower to get case-insensitive matching.Ĭitext doesn't help much if you need data to compare case-sensitively in some contexts and case-insensitively in other contexts. ![]() Also, only text can support B-Tree deduplication. This may be changed in a future release so that both steps follow the input COLLATE specification.Ĭitext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. Currently, citext operators will honor a non-default COLLATE specification while comparing case-folded strings, but the initial folding to lower case is always done according to the database's LC_CTYPE setting (that is, as though COLLATE "default" were given). But if you have data in different languages stored in your database, users of one language may find their query results are not as expected if the collation is for another language.Īs of PostgreSQL 9.1, you can attach a COLLATE specification to citext columns or data values. Effectively, what this means is that, as long as you're happy with your collation, you should be happy with citext's comparisons. ![]() ![]() It is not truly case-insensitive in the terms defined by the Unicode standard. How it compares values is therefore determined when the database is created. Citext's case-folding behavior depends on the LC_CTYPE setting of your database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |