Postgresql three ways to create a primary key and set auto-increment

Source: Internet
Author: User
Tags modifiers postgresql

Postgresql has the following three ways to set the primary key increment, the following look at the same point and different points.

--Method One
CREATE TABLE Test_a
(
ID Serial,
Name character varying (128),
Constraint pk_test_a_id primary key (ID)
);

Notice:create TABLE would CREATE implicit sequence "Test_a_id_seq" for serial column "Test_a.id"
Notice:create table/primary KEY would CREATE implicit index "pk_test_a_id" for TABLE "Test_a"
CREATE TABLE


--Method Two
CREATE TABLE Test_b
(
ID serial PRIMARY KEY,
Name character varying (128)
);

Notice:create TABLE would CREATE implicit sequence "Test_b_id_seq" for serial column "Test_b.id"
Notice:create table/primary KEY would CREATE implicit index "Test_b_pkey" for TABLE "Test_b"
CREATE TABLE


--Method Three
CREATE TABLE Test_c
(
ID integer PRIMARY KEY,
Name character varying (128)
);
Notice:create table/primary KEY would CREATE implicit index "Test_c_pkey" for TABLE "Test_c"
CREATE TABLE

Method Three of the above is a small section of the tool generated, if the table is already built, as long as the following statement can be used to generate an automatic growth sequence

CREATE SEQUENCE Test_c_id_seq
START with 1
INCREMENT by 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER TABLE Test_c ALTER COLUMN ID set default Nextval (' Test_c_id_seq ');


As can be seen from the above, method one and method two are only different, essentially the primary key is implemented by using the serial type,
Using the serial type, PG automatically creates a sequence for the primary key, and if you do not specify an ID when inserting the table data, the ID will use the sequence's
Next value.

Method Three is to create a table, create a sequence, and then set the default value of the primary key ID of the table to the next value of this sequence. This notation
Seems to be more in line with people's thinking habits, but also easy to manage, if the system encountered sequence performance problems, easy to adjust the sequence properties;

--Compare the table structure of the three tables
Skytf=> \d Test_a
Table "Skytf.test_a"
Column |                      Type | Modifiers
--------+------------------------+-----------------------------------------------------
ID | Integer | NOT NULL default Nextval (' Test_a_id_seq ':: Regclass)
name | Character varying (128) |
Indexes:
"pk_test_a_id" PRIMARY KEY, Btree (ID)


Skytf=> \d Test_b
Table "Skytf.test_b"
Column |                      Type | Modifiers
--------+------------------------+-----------------------------------------------------
ID | Integer | NOT NULL default Nextval (' Test_b_id_seq ':: Regclass)
name | Character varying (128) |
Indexes:
"Test_b_pkey" PRIMARY KEY, Btree (ID)


Skytf=> \d Test_c
Table "Skytf.test_c"
Column |                      Type | Modifiers
--------+------------------------+-----------------------------------------------------
ID | Integer | NOT NULL default Nextval (' Test_c_id_seq ':: Regclass)
name | Character varying (128) |
Indexes:
"Test_c_pkey" PRIMARY KEY, Btree (ID)

As can be seen from the above, the three table structure is identical, three methods if you want to look for differences, there may be only the following point,
When the drop table is dropped, method one and method two automatically drop the sequence, and method three does not.

Postgresql three ways to create a primary key and set auto-increment

Related Article

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.