Three methods for creating a primary key and setting auto-increment for PostgreSQL

PostgreSQL has three ways to set the primary key incrementing mode. The following describes the similarities and differences.

-- Method 1
Create Table test_a
(
Id serial,
Name character varying (128 ),
Constraint pk_test_a_id primary key (ID)
);

Notice: Create Table will create implicit sequence "test_a_id_seq" for serial column "test_a.id"
Notice: Create Table/primary key will create implicit Index "pk_test_a_id" for table "test_a"
Create Table

-- Method 2
Create Table test_ B
(
Id serial primary key,
Name character varying (128)
);

Notice: Create Table will create implicit sequence "test_ B _id_seq" for serial column "test_ B .id"
Notice: Create Table/primary key will create implicit Index "test_ B _pkey" for table "test_ B"
Create Table

-- Method 3
Create Table test_c
(
Id integer primary key,
Name character varying (128)
);
Notice: Create Table/primary key will create implicit Index "test_c_pkey" for table "test_c"
Create Table

// A short section above method 3 is generated by a tool. If the table has been created, use the following statement 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 ');

Obviously, we can see from the above that method 1 and method 2 are different in writing, in essence, the primary key is implemented by using the serial type,
When the serial type is used, PG automatically creates a sequence for the primary key. If no ID is specified when the table data is inserted, the ID uses the sequence by default.
Next value.

Method 3: first create a table, then create a sequence, and then set the default value of the table primary key ID to the next value of the sequence. This writing method
It seems to be more in line with people's habits of thinking and easy to manage. If the system encounters sequence performance problems, it is easy to adjust the sequence attribute;

-- Compare the table structures of 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)

We can see from the above that the three tables have the same structure. If you want to find the difference between the three methods, you may only have one of the following,
When you drop a table, method 1 and method 2 automatically drop the sequence, but method 3 does not.