Those things in the project---trampled database pits

Source: Internet
Author: User
Tags postgresql

Today, I was in the pit of the PostgreSQL database that I stepped on when I was doing the project ....

Database: PostgreSQL

Tool: Navicat

Inducement: Set the primary key for the database table to grow automatically

The database was designed by someone else, and I only manipulate database tables. If you want the primary key (ID) to grow automatically when you create the data table in the Navicat tool today, set the ID type to Int2, because I see that the automatically growing field types in other people's tables are int2.

Unexpectedly, when inserting data, the primary key is empty (the primary key is generally non-null ), as shown below:

I wonder why people set up Int2 can grow automatically and I can't. So after some Baidu said PG automatic growth type of serial, so I will before the Int2

Modified to Serial2, save with the hint that the type does not exist, as shown in:

Note: When this prompt is present, it cannot be saved, so do not expect the type to be saved, but it will automatically be converted to an automatically growing int2.

So I set to serial4,serial8 all hints do not exist this type, so I fire, clearly in the drop-down list there is this type of ah, why there is such a hint?

I just want to jump out of the air ... (Of course it's impossible, haha ~ ~)

I sat, calm the mood, decided to re-build a table, so I built a new table when the type of ID to choose to Serial2, the result is saved successfully, there is no hint of the type does not exist, but I found that the ID type after saving has become

Int2, when inserting data into the table can be inserted successfully, and the ID will automatically grow. Although I am very happy, but also very sad, the original I saw other people's int2 is not the actual I need int2, it seems that all of us still can not be the appearance of the

Confused Ah, if blindly refer to other people's things, and even copy other people's things and do not know their own research, into the inside, the end of the pit or their own AH!!!!

Well, here's a summary of the pit:

1, use navicat set up the PostgreSQL database table field automatically grow. The field type should be selected as Serial2 when you create the table, but after you save it you will find that it becomes you int2

2. If you choose another type when you create it, you will get an error if you want to change it to serial2 after creation, causing the modification to fail.

3. The auto-growing field type cannot select Int2, although Int2 is displayed after creation, but this int2 is not int2.

Therefore, there are two kinds of int2 in PG, one is sequence type, it can grow automatically, the other is non-sequential, only the shaping data can not be automatically increased.

Those things in the project---trampled database pits

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.