Any difference on how the primary key is defined in Postgres?

Question

I wonder if they are all the same or is there any difference.

Method 1:

CREATE TABLE testtable ( id serial, title character varying, CONSTRAINT id PRIMARY KEY (id) ); 

Method: 2

 CREATE TABLE testtable ( id serial PRIMARY KEY, title character varying, ); 

Method 3:

 CREATE TABLE testtable ( id integer PRIMARY KEY, title character varying, ); CREATE SEQUENCE testtable_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER SEQUENCE testtable_id_seq OWNED BY testtable.id; 

Update: I found something on the Internet saying that using an unprocessed sequence, you can pre-allocate memory for primary keys, which will help if you plan to make several thousand inserts the next minute.

Answer 1

Try and see; delete the ending "," after "different" on the second and third so that they are executed, execute each of them, and then do:

 \d testtable 

after each, and you can see what happens. Then release the table and move on to the next. It will look like this:

  Column | Type | Modifiers --------+-------------------+-------------------------------------------------------- id | integer | not null default nextval('testtable_id_seq'::regclass) title | character varying | Indexes: "id" PRIMARY KEY, btree (id) Column | Type | Modifiers --------+-------------------+-------------------------------------------------------- id | integer | not null default nextval('testtable_id_seq'::regclass) title | character varying | Indexes: "testtable_pkey" PRIMARY KEY, btree (id) Column | Type | Modifiers --------+-------------------+----------- id | integer | not null title | character varying | Indexes: "testtable_pkey" PRIMARY KEY, btree (id) 

The first and second are almost identical, except that the generated primary key is named differently. In the third case, this sequence is no longer populated when inserted into the database. First you need to create a sequence, and then create a table as follows:

 CREATE TABLE testtable ( id integer PRIMARY KEY DEFAULT nextval('testtable_id_seq'), title character varying ); 

To get what looks the same as the second. The only drawback is that you can use the CACHE directive to pre-allocate a certain number of sequence numbers. This is possible in order to be a sufficiently large resource that needs to be reduced. But you will need to do several thousand inserts per second, not per minute, before this happens.

Answer 2

There is no semantic difference between method 1 and method 2.

Method 3 is also very similar - this is what happens implicitly when using serial. However, when using serial, postgres also records the dependency of the sequence on the table. That way, if you drop the table created in method 1 or 2, the sequence is also dropped.