Is a 'serial' column automatically a primary key in PostgreSQL?

Question

I've created the following table in Postgres...

create table printq (
   model varchar(20),
   session integer,
   timestamp timestamp DEFAULT now(),
   id serial);

It seems to do exactly what I need it to... it auto-increments the id column, when I clear the table using truncate "RESTART IDENTITY" it resets the sequence (which is why I rebuilt the table in the first place -- the id column used to not restart upon truncation)

Anyway, when I do a \d on the table, I don't see anything about a primary key.

Table "public.printq"
  Column   |            Type             |                      Modifiers                      
-----------+-----------------------------+-----------------------------------------------------
 model     | character varying(20)       | 
 session   | integer                     | 
 timestamp | timestamp without time zone | default now()
 id        | integer                     | not null default nextval('printq_id_seq'::regclass)

Three questions:

Is the ID column already a primary key since it auto-increments, or not?
If not, why would this table need a primary key, since it seems to be working fine? I know basically every table is supposed to have a primary key, but why exactly?
Finally, would the \d command tell me if this table had a primary key? If not, what would tell me?

Egor Rogov · Accepted Answer · 2016-05-21 22:35:31Z

16

No, use id serial primary key for that.
Well, a table doesn't actually "need" a primary key. It can live without PK, it can live without an autoincrementing field, it can contain duplicate rows all right. But in relational theory (on top of which SQL is built) each relation (i. e. table) is a set (in mathematical sense) of rows. So duplicate rows are not allowed, they are simply not possible in sets. And hence each relation has a field (or several fields) which has unique values for all the relation. Such field can be used to uniquely identify a row, and one of possible unique keys is called a primary key. Such a key is usually very useful for identifying the rows, that's why tables are supposed to have them. But technically they are not required.
Yes, it will.

answered May 21, 2016 at 22:35

Egor Rogov

5,48828 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is a 'serial' column automatically a primary key in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related