Any difference in the way a primary key is defined in Postgres?

Question

I am wondering if all these are exactly the same or if there is some 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 web saying that by using a raw sequence you can pre-allocate memory for primary keys which helps if you plan on doing several thousand inserts in the next minute.

Answer 1

Try it and see; remove the trailing "," after "varying" on the second and third so they run, execute each of them, then do:

\d testtable

after each one and you can see what happens. Then drop the table and move onto the next one. 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)

First and second are almost identical, except the primary key created is named differently. In the third, the sequence is no longer filled in when you insert into the database. You need to create the sequence first, then create the table like this:

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

To get something that looks the same as the second one. The only upside to that is that you can use the CACHE directive to pre-allocate some number of sequence numbers. It's possible for that to be a big enough resource drain that you need to lower the contention. But you'd need to be doing several thousand inserts per second, not per minute, before that's likely to happen.

Answer 2

No semantic difference between method 1 and method 2.

Method 3 is quite similar, too - it's what happens implicitly, when using serial. However, when using serial, postgres also records a dependency of sequence on the table. So, if you drop the table created in method 1 or 2, the sequence gets dropped as well.