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

This article introduces how to use the special functions of Postgresql to solve the problems that are difficult to solve in other databases. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

PostgreSQL database has many special functions that other databases do not have. In the process of learning, some SQL writing methods have completely overturned the rules of traditional DBA in writing sentences. Here are some problems that other relational databases are difficult to solve. Let's see how POSTGRESQL can handle it easily.

For example, we have some fields in the database that are comments comments or customer feedback. For example, if we want to find unsatisfactory comments in customer feedback, we cannot read them one by one. If the traditional old three databases can only be written as like'% dissatisfied%', and so on, and the amount of such query data is small, it is easy to say that the amount of data is hundreds of thousands or millions. I guess I won't be happy.

PG is resolved in a special way, and let's see how to do such a thing.

Let's first set up a few pieces of test data, and here we use letters instead of the customer's injected information for convenience.

2 below we include the records of aaa through a special query of PG

By solving the problem with two functions, phrasetto_tsquery and to_tsvector, we can see that the database can easily query the records containing aaa in the fields.

3 We continue to increase the difficulty, here we need to find the data containing ccc and eedb in the content.

This difficult query is estimated to be ORACLE, SQL SERVER, MYSQL muscle weakness, some people say FULLTEXT, I do not comment on this. Postgresql can continue to solve the above problems without FULLTEXT.

4 it is estimated that at this time, someone said, since POSTGRESQL is so powerful, let's make it more difficult.

I need to find either aerd or eedb in the field, and if you want to write it in traditional SQL, it looks like this.

Select test from test where test like'% aerd%' or test like 'aerd% or test like'% aerd' or test like'% eedb%' or test like 'eedb%' or test like'% eedb'

This SQL OK does not OK probably need not DBA to say, the development has been thoroughly scolded, what is it? Performance must also be poor to the horizon, why everyone knows!

Let's see what POSTGRESQL does, So easy.

This database, must be satisfied, other databases in the character processing are sidelined, sidelined.

In fact, POSTGRESQL's handling of characters is only less than 6% of its default features.

There are some other things that we will use, such as whether two large strings are included or not, which are also operated with special operators. I will not talk about it here (too sleepy). These are all things that that pile of databases cannot do at present, and the functions I mentioned

It has been owned by POSTGRESQL 9.6 (since 2016, if I remember correctly). You should have some idea whether your fees are reasonable for that pile of paid databases. POSTGRESQL will reach 12 version soon, so how much potential this database has is a bottomless pit at present.

On how to use Postgresql special functions to solve other database difficult to solve the problem is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.