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

This article is about how POSTGRESQL's new field types are used. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

POSTGRESQL in the same type of database, the field type is the most abundant, in which POSTGRESQL has a field type called RANGE, range type. This type has not been found in the traditional three major databases.

Let's talk about, what problem does this new field type solve?

In finance, there are things like contracts, which generally have a start date and an end date. In general, even in the case of prepayment, although the original contract can be settled ahead of time, the initial date of the original contract cannot be modified at will.

If we do this in a traditional database, we might create two fields, for example, one of us is called START_TIME and the other is called END_TIME to record the start and end dates.

In the database to solve logical problems, seems to be accustomed to, but in fact deep mining, which is actually in the database to split the whole.

So what do we do in POSTGRESQL?

Let's just talk about the theory and get to the point.

We create a table with a self-incrementing column similar to other databases, a contract_no contract number, and a tsrange type that we have never seen before.

This is what we're going to talk about today, the type of time range. Here are all the test data

First we insert two pieces of data, in which we use the type function of tsrange

Obviously, this is a field that contains two times.

So what's the use of this? for example, we want to count the contracts in which our current contract initial date takes effect after 2019-01-01.

Select * from contract where contract_start_end_time > tsrange ('2019-01-01')

It is easy to find out the relevant information, of course, this does not reflect the advantages of such a field type

Then let's move on.

Next, I would like to check that the contract begins on 2019-01-30 and ends on 2020-12-20.

Select * from contract where contract_start_end_time > tsrange ('2019-01-30)

Obviously, it was found out all at once.

We are creating an index for such a field

Careful people may see that this index looks very strange, of course, there is also a universe, but also the ability that the other three major databases do not have, which is skipped here.

We are in a more difficult query, let's check the inclusion, which is also traditionally used in the design, we need to query the contract at 2019-02-01, and end at 2021-02-03 and all the contract numbers

Look, this is also held in the hand.

Some people may also ask, what if my query does not include it, even if one of the initial dates or the end dates is no longer included? of course, this is not a problem for POSTGRESQL.

Select * from contract where contract_start_end_time = =

< 但对这样的字段要小心，你要决定是 集合中的 （） 【】， {】 【） 的那种。 看不懂的同学，请补习一下 集合的概念 postgresqlpostgres@[local]=#5432=;insert into contract (contract_no,contract_start_end_time) values ('20190130VB',tsrange('2019-01-2','2021-02-2','[]')); INSERT 0 1 postgresqlpostgres@[local]=#5432=;insert into contract (contract_no,contract_start_end_time) values ('20190130VB',tsrange('2019-01-2','2021-02-2','()')); INSERT 0 1 postgresqlpostgres@[local]=#5432=;insert into contract (contract_no,contract_start_end_time) values ('20190130VB',tsrange('2019-01-2','2021-02-2','[)')); INSERT 0 1 postgresqlpostgres@[local]=#5432=;insert into contract (contract_no,contract_start_end_time) values ('20190130VB',tsrange('2019-01-2','2021-02-2','(]')); 上面的虽然数据一致，但如果不注意设置的集合性质，那是要出问题的，大家可以从下面的两个图中看结果，只是一个 >

The difference between = and >, the result set given is not the same, so be careful when dealing with such a query

Finally, a classmate asked, if I don't add the match of the collection later, what kind of format will I enter by default?

(.)

Yes, this is the collection format.

Sometimes from the type design of the field, you can see why POSTGRESQL adds an OBJECT database after the relational database.

Thank you for reading! This is the end of the article on "how to use the new field type of POSTGRESQL". I hope the above content can be of some help to you, so that 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.