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

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