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

This article mainly introduces how to deal with library, table, field, value case in PostgreSQL, has a certain reference value, interested friends can refer to, hope that you can learn a lot after reading this article, the following let Xiaobian take you to understand.

What needs to be tested is as follows

1 database case

2 Table name case

3 field case

4 fields are worth capitalization

Some related case functions and data conversion functions

Let's go

1 case of database name

First, three databases will be set up named

1 Test

Create table good (name varchar (20))

Insert into good (name) values ('Test')

2 test

Create table good (name varchar (20))

Insert into good (name) values ('test')

3 TEST

Create table good (name varchar (20))

Insert into good (name) values ('TEST')

Then in all three databases, a table, good, is created, and the data inserted in it will have the name of his own database.

Let's access the tables in these databases.

Through the above tests, it can be concluded that the database name is case-sensitive, so when building the database, we need to pay attention to the case problem.

Test of table case

We set up three tables, BIG,big and Big, in the test library.

You didn't report an error when you tried to create the first table, but when you created the second table, you started to report an error. We can see that there is only one table called big in the test database. Some people say, "what if I create the BIG table first?" (you can try. The result is the same. Postgresql can only run lowercase table names in a database.)

Of course, if you have to store different case table names, PostgreSQL is not impossible, that is, you need to add "" double quotation marks

Finally, let's try the question of field name and field value.

We still create a table in the test database, and we will create three fields in this table

The subtable is

Name

Name

NAME

And the value in each field is written in the same way as the field name. Let's take a look at the results.

When you do the following, you should see this error. In a table, the field names are lowercase by default. Of course, if you don't like it, you can still establish the case of the field name you want to create, and still add "double quotation marks" that way.

In that case, we can only continue to look for problems above the value of the field.

We insert different values in the name field of a table, TIM, tim, and Tim. Let's see what the results are, and will there be any problems in the query?

All right, the data has been generated. Let's see if the questions raised by some people in the group are true.

See the above results, verify the authenticity of the questions raised by someone in the group, it is true that different uppercase and lowercase values will be problematic in the query.

This led to complaints, and some of them said, "our front end doesn't care about case at all, how do we write SQL statements to check the data?"

Actually, So Easy, let's break the problem into pieces.

1 input data is not standard, there are both uppercase and lowercase, but when querying, the condition is lowercase. Can you check out the records of uppercase and uppercase?

You can:

This is all you need. See the following figure for details. In fact, I think this is where POSTGRESQL is stronger than other databases. It is very rigorous, but it is also supported if it needs to be loose.

In addition, I personally think that we cannot push everything to the backend, the verification, testing, and filtering that should be done at the front end, and we cannot transfer this work to the backend.

But the students said, there is no way, the company is like this, so what should we do?

There are ways, for example, to take the generated order number as an example, it must be unique, but if the customer enters some uppercase and some lowercase, then there will be two order numbers.

It is suggested that you can add the lower () upper () function to the back end of the input order number to change the input characters into a format.

Thank you for reading this article carefully. I hope the article "how to deal with library, table, field, value case in PostgreSQL" shared by the editor will be helpful to everyone. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.