Shulou(Shulou.com)06/01 Report--

In this issue, Xiaobian will bring you about PostgreSQL and what is the pit of case. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

Every database has its own characteristics, and PostgreSQL is no exception, and if you pay attention to PostgreSQL, one of the most frequently asked questions is about case. Today's discussion will not cover database names, table names, or case, but only the case of values in fields.

Let's start with an example,

1 We create a table

create table Case_insensitive

( id serial not null primary key,

address varchar(50),

comment text);

2 Enter the database into it.

insert into case_insensitive (address,comment) values ('Person1@em.com','Thanks for your help!! ');

insert into case_insensitive (address,comment) values ('TaTk@bb.com','I hate it');

insert into case_insensitive (address,comment) values ('ttKbb@cc.com','Sorry I am understanding now, little slowly');

So what's the problem with capitalization? Let's look up some data, and you'll understand.

You can clearly see what the problem is. If the value in your field contains English case, you must match the case carefully to find the relevant value.

According to the Chinese way of thinking, or used to other mainstream databases, such input must be matched, which is unfriendly to the Chinese.

Although the title mentions "pit", in fact, this is not a pit, or strictly speaking, PostgreSQL should be correct in this way. And many times preconceived, to judge POSTGRESQL in this regard is a pit, which is not fair.

So how do you solve this problem of the conventional wisdom that postgresql should be used the same way as any other database? Here's what I'm going to say.

Method: 1

Uniform rules:

We change the fields we query and the data we need to query into lowercase, and use the lower function to perform uniform conversion.

As can be seen from the above figure, we can find the data in the case of pure lowercase input.

The question arises again, can you go through the index in this case?

The answer is of course not, the calculation of the function in the case of the left side of the condition most databases are not indexed, oracle of course has similar functions, can let some of these cases go index. PG can it, of course, the target is ORACLE, of course, can also be calculated when the function in the left case, continue to go index.

How???

Change the idea, we will index inside the characters are smaller on it, see the following picture.

Of course, the reason why the implementation plan has not gone after the index is that the data volume is only three, which is not enough to support the cost of the index.

So this is the end of the way to deal with this situation? No no no We have other options for you.

2 There is a case where this column is e-mail address, for example, and if it is e-mail address, it has a property that the data is unique. What if (see picture)

If that happens, it's not a pretty thing. How to check the uniqueness of email addresses. We can create a unique index for this column in advance.

With this index case inconsistency, entering the same character can be controlled.

Of course, if you have any doubts, see if there are other ways to deal with such things. answer is YES

3 Use of ilike

Query case-sensitive questions by using ilike queries.

In fact, if there is a plan, the problem can be handled clearly at the insert stage by converting the case input at the time of input, and assisting in some constraints. This way the problem is easier to solve.

The above is what the PostgreSQL and case pits shared by Xiaobian are. If you happen to have similar doubts, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.